Mysql for two-way Database Synchronization under CentOS

Mysql for two-way Database Synchronization under CentOS

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:

[C-sharp] view plaincopy

1. # Log-bin =/var/log/mysql/updatelog
2. Server-id = 1
3. Binlog-do-db = discuz
4. Binlog-ignore-db = mysql

Restart MySQL and create a MySQL account for synchronization

[C-sharp] view plaincopy

1. #/Usr/local/mysql/bin/mysql-uroot-p
2. Mysql> GRANTREPLICATIONSLAVEON *. * TO [email =] 'back' @ '%' [/email] IDENTIFIEDBY 'back ';
3. If you want to have the permission to execute the "LOADTABLEFROMMASTER" or "LOADDATAFROMMASTER" Statement on Slave, you must grant the Global FILE and SELECT permissions:
4. Mysql> GRANTFILE, SELECT, REPLICATIONSLAVEON *. * TO [email =] 'back' @ '%' [/email] IDENTIFIEDBY 'back ';
5. Mysql> FLUSHPRIVILEGES;

2. Set synchronization Slave
Modify the my. cnf file and add

[C-sharp] view plaincopy

1. Server-id = 2
2. Master-host = 192.168.1.123
3. Master-user = back
4. Master-password = back
5. Master-port = 3306
6. Replicate-ignore-db = mysql
7. Replicate-do-db = discuz

Restart MySQL

3. Start Synchronization
Run the master MySQL command on the master server.

[C-sharp] view plaincopy

1. #/Usr/local/mysql/bin/mysql-uroot-p
2. Mysql> showmasterstatus;

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

[C-sharp] view plaincopy

1. #/Usr/local/mysql/bin/mysql-uroot-p
2. Mysql> slavestop;
3. Mysql> changemastertomaster_host = '192. 168.1.123 ', master_user = 'back', master_password = 'back', master_log_file = 'mysql-bin.000009', master_log_pos = 98;
4. Mysql> slavestart;

Use show slave status/G to check the synchronization status of the slave 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

[C-sharp] view plaincopy

1. Log-bin =/var/log/mysql/updatelog
2. Binlog-do-db = discuz
3. Binlog-ignore-db = mysql

Restart MySQL and create a MySQL account for synchronization

[C-sharp] view plaincopy

1. Mysql> GRANTREPLICATIONSLAVEON *. * TO [email =] 'back' @ '%' [/email] IDENTIFIEDBY 'back ';
2. Mysql> GRANTFILE, SELECT, REPLICATIONSLAVEON *. * TO [email =] 'back' @ '%' [/email] IDENTIFIEDBY 'back ';
3. Mysql> FLUSHPRIVILEGES;

Modify my. cnf of the master server and add

[C-sharp] view plaincopy

1. Master-host = 192.168.1.124
2. Master-user = back
3. Master-password = back
4. Master-port = 3306
5. Replicate-ignore-db = mysql
6. 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:

[C-sharp] view plaincopy

1. Mysql> slavestop;
2. Mysql> changemastertomaster_host = '192. 168.1.124 ', master_user = 'back', master_password = 'back', master_log_file = 'mysql-bin.000013', master_log_pos = 98;
3. Mysql> slavestart;

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