In-depth introduction to MySQL two-way replication technology _ MySQL

In-depth introduction to MySQL two-way replication technology to set up MySQL data synchronization (one-way and two-way) due to the company's business needs, data synchronization between China Netcom and China Telecom is required, and a MySQL two-way synchronization is implemented, write down the process and use it later 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 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 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 version is Ver 14.14 Distrib 5.1.22-rc. this parameter does not seem to be available)

Replicate-do-db = test

Restart MySQL