Shulou(Shulou.com)06/01 Report--

Master-slave synchronization

What is master-slave synchronization: have other database servers automatically synchronize data on the database server that is being served.

1 add authorized user

Mysql > grant replication slave on *. * to slaveuser@ "192.168.4.4" identified by '123456'

2 enable binlog Log

[root@4-3mysql ~] # vim / etc/my.cnf

[mysqld]

Server_id=3

Log-bin=master11

Binlog_format= "mixed"

Restart the database service

[root@4-3mysql ~] # systemctl restart mysqld

4 View log information

Mysql > show master status

Master11.000001

Verify the authorized users provided by the main library

[root@4-4mysql] # mysql-h 192.168.4.3-u slaveuser-p123456

Modify the configuration file

[root@4-4mysql ~] # vim / etc/my.cnf

[mysqld]

Server_id=4

Log-bin=db4

[root@4-4mysql ~] # systemctl restart mysqld

Log in using the data administrator of this machine to specify the main library information

Show slave status

Mysql > change master to master_host= "192.168.4.3", master_user= "slaveuser", master_password= "123456", master_log_file= "master11.000001", master_log_pos=154

Mysql > show slave status\ G

Master_Log_File: master11.000001

Read_Master_Log_Pos: 154

Slave_IO_Running: No

Slave_SQL_Running: No

Mysql > start slave

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave runs 2 threads

Slave_IO: copy the sql in the binlog log file of the master host to the local relay-log file

Slave_SQL: execute the SQL statement in the native relay-log file to reproduce the data operation of Master

There will be more files from the database directory

Master.info connection master server information

Relay-log.info Relay Log Information

Hostname-relay-bin.000000 relay log file

Hostname-relay-bin.index Relay Log Index

Test master-slave synchronization configuration

Add users who access data on the main library server

Mysql > grant all on bbsdb.* to yaya@ "%" identified by '123456'

Mysql > show master status

| | master11.000001 | 446 |

If an authorized user is used on the client to connect to the master library, the resulting data can also be found in the slave database.

[root@4-5test ~] # mysql-h 192.168.4.3-uyaya-p123456

MySQL [(none)] > create database bbsdb

At this time

[root@4-3mysql ~] # Master server

[root@4-4mysql ~] # slave server

The corresponding tables will be generated on the

Will change from server to stand-alone server, turn off synchronization

Mysql > stop slave

Rm-rf master.info relay-log.info

Rm-rf localhost-relay-bin*

Fault analysis and troubleshooting

Cause analysis

Unable to connect to the master database server

Solution.

Check physical connection ping check authorized user

Check if there are firewall rules

Close SElinux

Or the binlog log file specified incorrectly

Master-slave synchronous structure mode

One master and one slave

One master and many followers

Master and subordinate

Main main structure (mutual)

Common configuration parameters of master-slave synchronization

Written in vim / etc/my.cnf

[mysqld]

Option = valu

Master library configuration file (valid for all slaves)

Binlog_ignore_db= library name 1, library name 2 that are not allowed to synchronize

Only synchronized libraries binlog_do_db= library name 1 and library name 2 are allowed

[root@4-3mysql ~] # vim / etc/my.cnf

Binlog_do_db=dba

[root@4-3mysql ~] # systemctl restart mysqld

Slave library configuration file (valid for native only)

Only synchronized library replicate_do_db= library name 1, library name 2

Out of sync library replicate_ignore_db= library name 1, library name 2

Cascade replication log_slave_updates

Data reading and writing separation maxscale software + one master and one slave

What is read-write separation: send the query request select and write request insert when the client accesses the data to different database servers to process

Write (main service)

Read (from service)

one.

Pack a bag

[root@4-5test ~] # rpm-ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

Modify configuration file: specify database server

[root@4-5test ~] # vim / etc/maxcale.cnf

9 [maxscale]

10 threads=auto starts the thread according to cpu

18 [server1] # Database server name

19 type=server

20 address=192.168.4.3 # specify ip

21 port=3306

22 protocol=MySQLBackend

twenty-three

24 [server2] # Database server name

25 type=server

26 address=192.168.4.4 # specify ip

27 port=3306

28 protocol=MySQLBackend

36 [MySQL Monitor]

37 type=monitor

38 module=mysqlmon

39 servers=server1,server2 # listening on two database servers

40 user=scalemon # authorized user name

41 passwd=123456 # authorized user password

42 monitor_interval=10000 # milliseconds to monitor every ten seconds

52 # [Read-Only Service] # Read-Only full comment

53 # type=service

54 # router=readconnroute

55 # servers=server1

56 # user=myuser

57 # passwd=mypwd

58 # router_options=slave

63 [Read-Write Service]

64 type=service

65 router=readwritesplit

66 servers=server1,server2 # listening on two database servers

67 user=maxscale # authorized user name

68 passwd=123456 # authorized user password

69 max_slave_connections=100%

75 [MaxAdmin Service] # define software management services without modification

76 type=service

77 router=cli # usage Command Line

85 # [Read-Only Listener] # Read-Only full comment

86 # type=listener

87 # service=Read-Only Service

88 # protocol=MySQLClient

89 # port=4008

91 [Read-Write Listener]

92 type=listener

93 service=Read-Write Service # Service name

94 protocol=MySQLClient

95 port=4006 # read-write separation listener port number (default)

97 [MaxAdmin Listener] # Management Service snooping

98 type=listener

99 service=MaxAdmin Service

100 protocol=maxscaled

101 socket=default

102 port=4009 # designated management port

Add the appropriate authorized user to the database according to the profile settings

Scalemon 123456 users who monitor database service status (database service status and master-slave synchronization status)

Mysql > grant replication slave,replication client on *. * to scalemon@'%' identified by '123456'

Mysql > grant select on mysql.* to maxscale@'%' identified by '123456'

Maxscale 123456 checks whether the username and password used when the client connects exists on the monitored database server

Test on the agent host

Mysql-h292.168.4.3-uscalemon-p123456

Mysql-h292.168.4.3-umaxscale-p123456

Mysql-h292.168.4.4-umaxscale-p123456

Mysql-h292.168.4.4-uscalemon-p123456

Start the service

[root@4-5test ~] # maxscale-f / etc/maxscale.cnf

[root@4-5test ~] # netstat-pantu | grep maxscale

Tcp 0 0 192.168.4.5:42703 192.168.4.3:3306 ESTABLISHED 4877/maxscale

Tcp 0 0 192.168.4.5:44918 192.168.4.4:3306 ESTABLISHED 4877/maxscale

Tcp6 00: 4009: * LISTEN 4877/maxscale

Tcp6 00: 4006: * LISTEN 4877/maxscale

two.

Test configuration

Access the management service locally on the proxy server

[root@4-5test ~] # maxadmin-uadmin-pmariadb-P4009

MaxScale > list servers

Servers.

Server | Address | Port | Connections | Status

Server1 | 192.168.4.3 | 3306 | 0 | Master, Running

Server2 | 192.168.4.4 | 3306 | 0 | Slave, Running

-- + -

Storing or querying data on the client access proxy server

4-3

Mysql > create database bbsdb

Query OK, 1 row affected (0.00 sec)

Mysql > create table bbsdb.t1 (id int)

Query OK, 0 rows affected (0.26 sec)

MySQL [(none)] > show grants

+-+

| | Grants for yaya@% |

+-+

| | GRANT USAGE ON *. * TO 'yaya'@'%' |

| | GRANT ALL PRIVILEGES ON `bbsdb`. * TO 'yaya'@'%' |

+-+

2 rows in set (0.00 sec)

Display as slave service when viewing

MySQL [(none)] > select @ @ hostname

+-+

| | @ @ hostname |

+-+

| | 4-4mysql |

+-+

1 row in set (0.00 sec)

MySQL [(none)] > insert into bbsdb.t1 values

Switch to primary service after adding

MySQL [(none)] > select @ @ hostname

+-+

| | @ @ hostname |

+-+

| | 4-3mysql |

+-+

1 row in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.