MySQL two-way replication technology classic edition

The following articles mainly describe the MySQL two-way replication technology, including preparing for the server and setting the actual operation steps for the synchronization server. The following is an introduction to the MySQL two-way replication technology, I hope it will help you in this regard.

Setting up MySQL Data Synchronization (one-way and two-way) as a result of the company's business needs, we need to synchronize data between China Netcom and China Telecom, so we made a two-way MySQL synchronization and noted down the process, I will use it later and paste it 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 (my version is 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;  
 
 

Display (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.000028',MASTER_LOG_POS=313361;slave　start;  
 
 

Use show slave statusG; 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 MySQL two-way 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

Under the MySQL command of master server B

 
 

  
  
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! MySQL bidirectional synchronization is that simple!