MySQL bidirectional synchronous replication in RedHat Linux 9

Set two-way MySQL Data Synchronization

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: master (IP: 192.168.1.123), Slave host name is: slave (IP: 192.168.1.124), and the two MySQL basedir directories 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 = discuz
Binlog-ignore-db = mysql
Restart MySQL and create a MySQL account for synchronization
#/Usr/local/mysql/bin/mysql-u root-p
Mysql> grant replication slave on *. * TO [email =] 'back' @ '%' [/email] identified by 'back ';
If you want to have the permission to execute the "load table from master" or "load data from master" Statement on Slave, you must grant the Global FILE and SELECT permissions:
Mysql> grant file, SELECT, replication slave on *. * TO [email =] 'back' @ '%' [/email] identified by 'back ';
Mysql> flush privileges;

2. Set synchronization Slave
Modify the my. cnf file and add
Server-id = 2
Master-host = 192.168.1.123
Master-user = back
Master-password = back
Master-port = 3306
Replicate-ignore-db = mysql
Replicate-do-db = discuz

Restart MySQL

3. Start Synchronization
Under the master MySQL command:
#/Usr/local/mysql/bin/mysql-u root-p
Mysql> 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 |
+ ------------------ + ---------- + ------------------- + ------------------ +
| Mysql-bin.000009 | 98 | discuz | mysql |
+ ------------------ + ---------- + ------------------- + ------------------ +

Under the slave server master MySQL command:
#/Usr/local/mysql/bin/mysql-u root-p
Mysql> slave stop;
Mysql> change master to master_host = '192. 168.1.123 ', master_user = 'back', master_password = 'back', master_log_file = 'mysql-bin.000009', master_log_pos = 98;
Mysql> 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 the slave server and add
Log-bin =/var/log/mysql/updatelog
Binlog-do-db = discuz
Binlog-ignore-db = mysql

Restart MySQL and create a MySQL account for synchronization
Mysql> grant replication slave on *. * TO [email =] 'back' @ '%' [/email] identified by 'back ';
Mysql> grant file, SELECT, replication slave on *. * TO [email =] 'back' @ '%' [/email] identified by 'back ';
Mysql> flush privileges;

Modify my. cnf of the master server and add
Master-host = 192.168.1.124
Master-user = back
Master-password = back
Master-port = 3306
Replicate-ignore-db = mysql
Replicate-do-db = discuz

Restart MySQL

Under the master server slave MySQL command:
Show master status;
+ ------------------ + ---------- + ------------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + ------------------- + ------------------ +
| Mysql-bin.000013 | 98 | discuz | mysql |
+ ------------------ + ---------- + ------------------- + ------------------ +

Run the MySQL Command on server:
Mysql> slave stop;
Mysql> change master to master_host = '192. 168.1.124 ', master_user = 'back', master_password = 'back', master_log_file = 'mysql-bin.000013', master_log_pos = 98;
Mysql> slave start;

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

Tip: If you have modified the configuration of the master server, remember to delete the master.info file on the slave server. Otherwise, the old configurations used by the slave server may cause errors.
Bytes -----------------------------------------------------------------------------------
Note: When you want to copy multiple databases, the binlog-do-db and replicate-do-db options are set. Many people on the Internet say that they are separated by commas (,). After testing, this statement is incorrect. The official MySQL documentation also clearly states that if you want to back up multiple databases, you only need to set the corresponding options again.
For example:
Binlog-do-db =
Binlog-do-db = B
Replicate-do-db =
Replicate-do-db = B