MYSQL dual-master full-database synchronous replication and mysql Database Synchronization

MYSQL dual-master full-database synchronous replication and mysql Database Synchronization

Environment:

A, B two servers respectively install the mysql-5.7.18 server, configured as mutual master slave synchronization.

CentOS7 for linux

Server A ip Address: 192.168.1.7 Host Name: test1

Server B ip: 192.168.1.8 Host Name: test2

(Under the same LAN)

 

I. Preparation

1. Modify the Host Name

Command: hostnamectl set-hostname xxx

(Run the hostname command to view the host name)

2. Disable the Firewall

1) view the Firewall Status

Command: firewall-cmd -- state

Result: running

2) The firewall is in running state. Disable the Firewall Service first.

Command: systemctl mask firewalld

3) disable the Firewall

Command: systemctl stop firewalld

4) Check the Firewall Status

Command: firewall-cmd -- state

Result: not running.

3. Disable selinux policy

1) view the running status of selinux

Command: getenforce

Result: Enforcing

　　　　

2) permanently shut down (the computer needs to be restarted) and edit the selinux file vi/etc/sysconfig/selinux to modify SELINUX = disabled

　　　　

3) is disabled.

　　　　

2. Configure the master slave server (slave)

1. Open the my. cnf file vi/etc/my. cnf of server A, and add the following content under [mysqld] of the file:

Server-id = 1 # It must be different from other servers and must be 1 ~ Positive integer between 232

Log-bin = mysql-bin

Log-bin-index = mysql-bin

　　　

　　

Add content under [mysqld] In the my. cnf file of server B, and only change the server-id value.

Server-id = 2

Log-bin = mysql-bin

Log-bin-index = mysql-bin

2. Create the full-database backup file all. SQL In the mysql installation path (/var/lib/mysql in rpm installation path) of server.

CREATE command: touch/var/lib/mysql/all. SQL

Backup command: [root @ test1 mysql] # mysqldump-uroot-p123 -- all-databases>/var/lib/mysql/all. SQL

 

Copy the all. SQL file to a path on server B (take the/var/lib/mysql path as an example) for restoration.

Restore command: [root @ test2/] # mysql-uroot-p123 </var/lib/mysql/all. SQL

3. Create A user in mysql on server A and server B for synchronization.

Create a user: mysql> create user 'gbu' @ '%' identified by 'gbu ';

Authorization: mysql> grant all on *. * to 'gbu' @ '% ';

Delete A user: mysql> drop user 'username' @ '% ';

After server A and server B are created, test whether users can remotely log on to mysql.

[Root @ test1/] # mysql-utongbu-ptongbu-h192.168.1.8

[Root @ test2/] # mysql-utongbu-ptongbu-h192.168.1.7

　　

After the test is complete, restart the mysql service on server A and server B: [root @ test/] # service mysqld restart

4. Set A as the master server and B as the slave server.

1) view the binary log name and offset value in mysql of server.

Command: mysql> show master status \ G

For example

　　　　

2) configure the connection server in server B

Mysql> stop slave;

Mysql> change master

-> Master_host = '192. 168.1.7 ',

-> Master_user = 'gbu ',

-> Master_password = 'gbu ',

-> Master_log_file = 'mysql-bin.000024 ',

-> Master_log_pos = 154;

Mysql> start slave;

Mysql> show slave status \ G enter this command to check whether the values of Slave_IO_Running and Slave_ SQL _Running are Yes, as shown in figure

　　　　

5. Set B as the master server, and A as the slave server.

1) view the binary log name and offset in server B mysql

Command: mysql> show master status \ G

　　　　

2) configure the connection server in server.

Mysql> stop slave;

Mysql> change master

-> Master_host = '192. 168.1.8 ',

-> Master_user = 'gbu ',

-> Master_password = 'gbu ',

-> Master_log_file = 'mysql-bin.000027 ',

-> Master_log_pos = 154;

Mysql> start slave;

Mysql> show slave status \ G check whether the values of Slave_IO_Running and Slave_ SQL _Running are Yes

 

So far, the two mysql Databases have configured master-slave synchronization for each other.

 

Troubleshooting:

1. Slave_IO_Running status Error

When you check MySQL> show slave status \ G;, the Slave_IO_Running status is a ing error. There are three main causes:

1) network disconnection

2) incorrect account permission configuration, such as incorrect password, incorrect account, or incorrect address

3) the location of the binary file is incorrect.

2. Exceptions caused by master-slave data conflicts or differences

Primary key conflict, table existence, and other error codes, such as 1060, can be skipped in the mysql configuration file and continue the next SQL synchronization, in this way, many master-slave synchronization exceptions can be avoided. Open the my. cnf file under/etc/mysql and add the following code after [mysqld:

Slave-skip-errors = route 1060

3. Skip the exception and resume synchronization.

Mysql> slave stop;

Mysql> set global SQL _slave_skip_counter = 1;

Mysql> slave start;