MySQL bidirectional replication technology

Set up MySQL data synchronization (unidirectional & bidirectional) due to the company's business needs, the need for Netcom and telecommunications data synchronization, it has done a two-way synchronization of MySQL, write down the process, later used to get back out, also posted out for everyone to reference.

First, prepare the server

Because the Binlog format may be different between versions of MySQL (binary log), the best combination is that the MySQL version of Master and the slave version are the same or lower, and the master version must not be higher than the slave version.

More.. | Less.. | In this article, we assume that the primary server (hereinafter referred to as Master) and the version from the server (hereinafter referred to as slave) are 5.0.27 and that 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 2 MySQL basedir directories are/usr/local/mysql,datadir:/ Var/lib/mysql.

Second, set up the synchronization server

1. Set Sync Master

Modify the My.cnf file to

# 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, create a MySQL account for synchronization dedicated

GRANT REPLICATION Slave,reload,super, on * * to [e-mail protected] identified by ' back ';
FLUSH privileges;
2. Set the sync slave
Modify the My.cnf file to add
Server-id = 2
Master-host = 192.168.0.1
Master-user = back
Master-password = back
Master-port = 3306
Replicate-ignore-db=mysql (My is ver 14.14 distrib 5.1.22-RC version, this parameter does not seem to be used)
Replicate-do-db=test

Restart MySQL

3. Start the synchronization

Under the master server a MySQL command:

Show master status;

Show (of course this is the case of my machine, you can not be the same as I ha, just an example):

+------------------+----------+-------------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+-------------------+------------------+
| updatelog.000028 | 313361 | Test | MySQL |
+------------------+----------+-------------------+------------------+

Under MySQL command from Server A:

Slave stop;
Change MASTER to master_log_file= ' updatelog.000028 ', master_log_pos=313361;
Slave start;
With show slave status\g; take a look at the synchronization from the server
Slave_io_running:yes
Slave_sql_running:yes

If yes, that means it's already in sync.

In the table to write some data test to see if the synchronization success, if not successful, definitely not your RP problem, and then check the operation steps!

4. Set up bidirectional synchronization

Modify the my.cnf of Server B, add

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

Restart MySQL, create a MySQL account for synchronization dedicated

GRANT REPLICATION Slave,reload,super, on * * to [e-mail protected] identified by ' back ';
FLUSH privileges;

Modify the my.cnf of a server to 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 master server B mysql command:

Show master status;
+------------------+----------+-------------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+-------------------+------------------+
| updatelog.000028 | 13753 | Test | MySQL |
+------------------+----------+-------------------+------------------+

Under Server a MySQL command:

Slave stop;
Change MASTER to master_log_file= ' updatelog.000028 ', master_log_pos=13753;
Slave start;

In fact, a->b one-way synchronous reverse operation! Two-way synchronization, it's so simple!

MySQL bidirectional replication technology