MySQL two-way replication technology Classic Edition _ MySQL

The following articles in the classic version of MySQL two-way replication technology mainly refer to the MySQL two-way replication technology, including preparing for the server and setting the actual operation steps of the synchronization server, the following is an introduction to MySQL's two-way replication technology, hoping to 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 RedHatLinux9.

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 master 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 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)

+ Region + ---------- + Region + ---------------- + | File | Position | Binlog_Do_DB | region | + ------------------ + ---------- + Region + | updatelog.000028 | 313361 | test | mysql | + Region + ---------- + ------------------- + ------------------ +

Under the slave server A MySQL command:

Slave stop; change master to MASTER_LOG_FILE = 'updatelog. 100', MASTER_LOG_POS = 000028; 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; + Region + ---------- + Region + ---------------- + | File | Position | Binlog_Do_DB | region | + ------------------ + ---------- + Region + | updatelog.000028 | 13753 | test | mysql | + Region + ---------- + ------------------- + ------------------ +

Run the MySQL command on server:

Slave stop; change master to MASTER_LOG_FILE = 'updatelog. 100', MASTER_LOG_POS = 000028; slave start;

In fact, this is the reverse operation of A-> B One-way synchronization! MySQL bidirectional synchronization is that simple!