Detailed example of MYSQL dual-master full database synchronous replication

Environment:

The two servers A and B are installed with the mysql-5.7.18 server respectively and configured to synchronize each other's master and slave.

The linux system version is CentOS7

A server ip: 192.168.1.7 Host name: test1

B server ip: 192.168.1.8 Host name: test2

(Under the same LAN)

1. Preparation

1. Modify the host name

Command: hostnamectl set-hostname xxx

(View host name command ：hostname)

2. Turn off the firewall

1) Check the firewall status

Command: firewall-cmd --state

Result: running

　2) The firewall is running. First, shut down the firewall service. ##　4) Check the firewall status

　Command: firewall-cmd --state

　Result: not running

　3. Turn off selinux policy

　1) Check selinux running status

Command: getenforce

Result: Enforcing

　3) To the closed state

2. Configuration Master server (master) slave server (slave)

log-bin=mysql-bin
　　　log-bin-index=mysql-bin

server-id=2    
　　　log-bin=mysql-bin
　　　log-bin-index=mysql-bin

touch  /var/lib/mysql/all.sql

[root@test1 mysql]# mysqldump -uroot -p123 --all-databases > /var/lib/mysql/all.sql

Copy the all.sql file to a path in server B (use the /var/lib/mysql path (for example) to restore.

Restore command:

[root@test2 /]# mysql -uroot -p123 < /var/lib/mysql/all.sql

3. Create users in mysql of servers A and B for synchronization.

Create user:

mysql> create user 'tongbu'@'%' identified by 'tongbu';

Authorization:

mysql> grant all on *.* to 'tongbu'@'%';

Delete user:

mysql> drop user '用户名'@'%';

After the A and B server users are created, test whether the users can remotely access each other Log in to mysql.

[root@test1 /]# mysql -utongbu -ptongbu -h192.168.1.8
　　　[root@test2 /]# mysql -utongbu -ptongbu -h192.168.1.7

Restart the mysql service in servers A and B after testing without problems: [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 server A mysql

Command: mysql> show master status\G

As shown below

##　2) Configure the connection server in server B

mysql> stop slave;
　　　　mysql> change master to
　　　　　　-> master_host='192.168.1.7',
　　　　　　-> master_user='tongbu',
　　　　　　-> master_password='tongbu',
　　　　　　-> 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 below

　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 A.

mysql> stop slave;
　　　　mysql> change master to
　　　　　　-> master_host='192.168.1.8',
　　　　　　-> master_user='tongbu',
　　　　　　-> master_password='tongbu',
　　　　　　-> master_log_file='mysql-bin.000027',
　　　　　　-> master_log_pos=154;
　　　　mysql> start slave;
　　　　mysql> show slave status\G 查看Slave_IO_Running和Slave_SQL_Running的值是否为Yes

At this point, the master-slave synchronization configuration of the two mysql databases is completed.

Error troubleshooting:

　主键冲突、表已存在等错误代码如1062,1032,1060等，可以在mysql配置文件指定略过此类异常并继续下条sql同步，这样可以避免很多主从同步的异常中断。打开/etc/ mysql下的my.cnf文件，在[mysqld]后添加如下代码：

slave-skip-errors = 1062,1032,1060

3. 跳过异常恢复同步

mysql >slave stop;
　mysql >SET GLOBAL sql_slave_skip_counter = 1;
　mysql >slave start;

The above is the detailed content of Detailed example of MYSQL dual-master full database synchronous replication. For more information, please follow other related articles on the PHP Chinese website!