In-depth introduction to MySQL two-way replication technology

Set MySQL Data Synchronization in one-way and two-way) due to the company's business needs, we need to synchronize data between China Netcom and China Telecom, so we have done a two-way MySQL synchronization, write down the process, and use it later to get it out, it is also posted for your reference.

I. Prepare servers

Because the binlog formats of different MySQL versions (Binary logs) may be different, the best combination is that the MySQL version of the Master is the same or lower than the Slave version, the Master version must not be higher than the Slave version.

More... | less... | In this article, we assume that the Master server and Slave server (Slave) versions are 5.0.27, and the operating system is RedHat Linux 9.

Assume that the Host Name of the synchronization Master is A (IP: 192.168.0.1), the Slave host name is B (IP: 192.168.0.2), and the two basedir directories of MySQL are/usr/local/mysql, datadir is both:/var/lib/mysql.

Ii. Set synchronization server

1. Set synchronization Master

Modify the my. cnf file in

# Replication Master Server (default) # binary logging is required for replication

Add the following content:

log-bin=/var/log/mysql/updatelog server-id = 1 binlog-do-db=test binlog-ignore-db=mysql

Restart MySQL and create a MySQL account for synchronization

Grant replication slave, RELOAD, SUPER, ON *. * TO back@192.168.0.2 identified by 'back '; Flush privileges; 2. Set synchronization Slave Modify the my. cnf file and add Server-id = 2 Master-host = 192.168.0.1 Master-user = back Master-password = back Master-port = 3306 Replicate-ignore-db = mysql I use Ver 14.14 Distrib 5.1.22-rc. This parameter does not seem to be available) Replicate-do-db = test

Restart MySQL

3. Start Synchronization

Run the MySQL command on the master server:

show master status;

Of course, this is my machine. You cannot be the same as me, just an example ):

+------------------+----------+-------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+-------------------+------------------+ | updatelog.000028 | 313361 | test | mysql | +------------------+----------+-------------------+------------------+

Under the slave server A MySQL command:

Slave stop; Change master to MASTER_LOG_FILE = 'updatelog. 100', MASTER_LOG_POS = 000028; Slave start; Use show slave status \ G; to check synchronization from the server Slave_IO_Running: Yes Slave_ SQL _Running: Yes

If all are yes, it indicates that the synchronization is in progress.

Write Data to the table and test whether the synchronization is successful. If the synchronization fails, it is definitely not your RP problem. Then check the operation steps!

4. Set bidirectional Synchronization

Modify my. cnf of server B and add

log-bin=/var/log/mysql/updatelog binlog-do-db=test binlog-ignore-db=mysql

Restart MySQL and create a MySQL account for synchronization

GRANT REPLICATION SLAVE,RELOAD,SUPER, ON *.* TO back@192.168.0.1 IDENTIFIED BY 'back' ; FLUSH PRIVILEGES ;

Modify my. cnf of server A and add

master-host = 192.168.0.2 master-user = back master-password = back master-port = 3306 replicate-ignore-db=mysql replicate-do-db=test

Restart MySQL

Run the following command on master server B MySQL:

show master status; +------------------+----------+-------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+-------------------+------------------+ | updatelog.000028 | 13753 | test | mysql | +------------------+----------+-------------------+------------------+

Run the MySQL Command on server:

slave stop； CHANGE MASTER TO MASTER_LOG_FILE='updatelog.000028',MASTER_LOG_POS=13753; slave start;

In fact, this is the reverse operation of A-> B one-way synchronization! Bidirectional synchronization is that simple!

1. How MySQL offers millions of High-concurrency websites
2. Several little-known special techniques of MySQL
3. MySQL database UPDATE statement