Dual Master synchronous replication configuration for MySQL engine

This configuration scheme from CSDN predecessor blog, but can not find the source, Embrace!!!

1. mysql Synchronization mechanism overview

MySQL supports one-way, asynchronous replication, one server acting as the primary server during replication, one or more servers acting as slave servers, and dual-master synchronization requires two servers to be primary.

The MySQL synchronization mechanism records all updates to the database in the binary log based on the primary server and maintains the log file index to track the logs. Reads the last update from the server in the log and receives all updates that have occurred since then, and then blocks and waits for the next update notification from the primary server. Therefore, when the synchronization mechanism is enabled, the primary server must enable the binary log, each accepting an update from the server that is logged from the binary log on the primary server, and the SQL copy that is logged from the primary server log is actually executed from the server. Therefore, when synchronization is enabled, the binary log files of the master and slave servers are required to be consistent or the synchronization fails.

The MySQL sync feature is implemented by 3 threads (1 on master server, 2 from server slave). After the start slave statement is executed, slave creates an I/O thread. The I/O line thread attached to master and requests that master send the statements in the binary log. Master creates a thread that sends the contents of the log to the slave. The Binlog Dump thread in the result of this thread executing the show Processlist statement on Master. The I/O thread on the slave reads the statements sent by the master's Binlog dump thread and copies them to the trunk log (relay logs) in its data directory. The third is the SQL thread, which salve uses to read the trunk logs and then executes them to update the data.

As mentioned above, there are 3 threads on each mster/slave. There are multiple threads on each master, which creates a thread for each slave connection, and each slave has only I/O and SQL threads. Executing the show Processlist statement in MySQL tells us what happens on the master and slave that we care about.

2. Configuring the Environment

Operating system: Linux

Environment: MySQL

A, b two servers

a-ip:192.168.204.128

b-ip:192.168.204.129

Requires both A and B servers to communicate

Shutting down two server firewalls

3 Synchronization Configuration 3.1 Create a sync account on the master and slave server

On a server to create a B server can login account, into the MySQL terminal to create a synchronization account: Rootnew, the password is: 123456;

Mysql>grant all privileges on * * to ' rootnew ' @ ' 192.168.204.129 ' identified by ' 123456 ';

Mysql>flush privileges;

Create an account on Server B that can log on to a server:

Mysql>grant Privileges On * * to ' newroot ' @ ' 192.168.204.128 ' identified bu ' 123456 ';

Mysql>flush privileges;

There is one newroot user in the MySQL database on the A and B servers at this time:

3.2 Primary Server database backup (ignore this step for the newly installed database) 3.3 Modify the master-slave server configuration file

Create an empty ZYYT_ANTM database on a, B server, open the my.cnf file of Server A, and add the following in the file [Mysqld]: (The my.cnf file location of each operating system is different, mine is under/etc/mysql/)

Skip-name-resolve

Server-id=1 #必须与其他服务器不同 and must be a positive integer value between 1 and 232–1

#i am Master

Log-bin=mysql-bin #二进制文件

Binlog-do-db=zyyt_antm #要同步的数据库, here I do the test directly with the Unit project

Binlog-ignore-db=information_schema #不同步的数据库

Binlog-ignore-db=db_im

Binlog-ignore-db=db_mdr

Binlog-ignore-db=db_mrs

Binlog-ignore-db=mysql

Binlog-ignore-db=performance_schema

Binlog-ignore-db=test

slave-skip-errors=1032,1062

To restart the a server's MySQL service:

~#/etc/inti.d/mysql Restart

Successful restart indicates a server configuration was successful.

Open the my.cnf file for the B server and add the same content under the [MYSQLD] node:

Skip-name-resolve

server-id=2 #必须与其他服务器不同 and must be a positive integer value between 1 and 232–1

#i am Master

Log-bin=mysql-bin #二进制文件

Binlog-do-db=zyyt_antm #要同步的数据库

Binlog-ignore-db=information_schema #不同步的数据库

Binlog-ignore-db=db_im

Binlog-ignore-db=db_mdr

Binlog-ignore-db=db_mrs

Binlog-ignore-db=mysql

Binlog-ignore-db=performance_schema

Binlog-ignore-db=test

slave-skip-errors=1032,1062

Restart the B server's MySQL service.

3.4 Specifying master-Slave server synchronization information

1. View the current binary log file and offset values on a server:

Mysql>show Master Status\g;

Record the file and position values in the MySQL binaries;

2. Specify the update point on the B server (modify the Master_log_file value as the file value in the sample result, based on the example above, and modify the Master_log_pos value as the position value in the sample result).

Mysql>stop slave;

Mysql>change Master to master_host= ' 192.168.204.128 ', master_port=3306,master_user= ' rootnew ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000012 ', master_log_pos=154;

Mysql>start slave;

3. View the current binary log name and offset on the B server:

Mysql>show Master status;

Records file and position values in MySQL binaries

4. Specify the update point on the a server (modify the Master_log_file value as a file value in the sample result based on the sample above, and modify the Master_log_pos value as the position value in the sample result).

Mysql>stop slave;

Mysql>change Master to master_host= ' 192.168.204.129 ', master_port=3306,master_user= ' rootnew ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000010 ', master_log_pos=154;

Mysql>start slave;

5. Restart the MySQL service for a and B servers.

3.5 Checking the database synchronization status

1. View a, B server database synchronization status, the main view slave_io_running and slave_sql_running These two fields are displayed as Yes.

Mysql>show slave status\g;

2. View the synchronization thread status for a and B servers:

Mysql>show processlist\g;

If the three threads described in the overview appear after entering a command, the configuration is successful.

Dual Master synchronous replication configuration for MySQL engine