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

2) Permanently shut down (need to restart the computer), edit selinux File vi /etc/sysconfig/selinux modify SELINUX=disabled

　　

　3) To the closed state

　　

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

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

server-id=1 #Must be different from other servers, and must be a positive integer value between 1 and 232

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

　

　[In the B server my.cnf file mysqld], add the content in the above picture, and only change the server-id value.

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

　2. Create the full library backup file all.sql in the mysql installation path in server A (the rpm installation path is /var/lib/mysql).

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 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:

1.Slave_IO_Running status error

When checking MySQL> show slave status\G;, the Slave_IO_Running status is Connecting error. The reasons are mainly divided into three kind, you need to check the configuration:

　1) The network is blocked

　2) The account permissions are configured incorrectly, such as incorrect password, wrong account, incorrect address

　3) The location of the binary file is incorrect

2. Abnormalities caused by conflicts or differences between primary and secondary data

　主键冲突、表已存在等错误代码如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!