MySQL bidirectional Synchronization

1. Server Status
Server A: 192.168.1.1
Server B: 192.168.1.2

2. Create a synchronization user
Host Domain A: 192.168.1.2 User Name A: sync_a Password A: aaa
Host Domain B: 192.168.1.1 username B: sync_ B password B: bbb
At leastAssign the following permissions to grant replication slave

3. Execute flush privileges

4. Stop MySQL

5. Configure my. cnf (my. ini)

Server	Server B
User = mysql Log-bin = mysql-bin Server-id = 1 Binlog-do-db = test Binlog-ignore-db = mysql Replicate-do-db = test Replicate-ignore-db = mysql Log-slave-updates Slave-skip-errors = all Sync_binlog = 1	User = mysql Log-bin = mysql-bin Server-id = 2 Binlog-do-db = test Binlog-ignore-db = mysql Replicate-do-db = test Replicate-ignore-db = mysql Log-slave-updates Slave-skip-errors = all Sync_binlog = 1

Server-id requiredUnique
Binlog-do-db and replicate-do-dbYesSynchronized Database
Binlog-ignore-db and replicate-ignore-dbNoSynchronized Database
PleaseNoAdd the following command andNot solvedThe uid hop number is incorrect. On the contrary, the following two lines of commands cause the uid hop number.Culprit
Auto_increment_increment = 2
Auto_increment_offset = 1

6. Restart MySQL

7. log on to the MySQL console.

Server:
Show master status \ G
Flush tables with read lock;
Server B:
Show master status \ G
Flush tables with read lock;

Record the File and Position of the two servers at the same time. Assume that:

A: File: mysql-bin.000001
Position: 001
B: File: mysql-bin.000002
Position: 002

Server:
Change master
-> Master_host = '192. 168.1.2 ',
-> Master_user = 'sync _ B ',
-> Master_password = 'bbb ',
-> Master_log_file = 'mysql-bin.000002 ',
-> Master_log_pos = 002;
Server B:
Change master
-> Master_host = '2017. 168.1.1 ',
-> Master_user = 'sync _ ',
-> Master_password = 'aaa ',
-> Master_log_file = 'mysql-bin.000001 ',
-> Master_log_pos = 001;

The data entered here is complete.Opposite

8. Run show processlist \ G to check whether the synchronization is successful.