MySQL dual-master Bidirectional Synchronization

The MySQL Dual-master replication implementation method described in this article may not be the most perfect and powerful. But in my application environment can be very good to meet the needs.

This article is based on the case that we only use two MySQL servers, but you will find that the methods described in this article can be easily applied to multiple server environments. Likewise, let's assume that the database you will be using for replication has already been built on one of the MySQL servers. Finally, before we start all the work, we have to adjust the firewall policies of all the servers to ensure that we can access each other's 3306 ports.

Environment:

Operating system: CentOS 5.4 x86

MySQL version: 5.0.77

Primary server ip:10.10.1.1

From server ip:10.10.1.2

Achieve the goal: Master and slave two machines MySQL data bidirectional synchronization

Database Preparation WorkPerform a lock table operation on two servers: shell>mysql-u [user]-p-e "FLUSH TABLES with READ lock" will need to configure the synchronized database dump out: shell>mysqldump-u [user ]-p-c [database name] >/tmp/dumpeddb.sql to copy the dump file to another server, the method is arbitrary. I use Scpshell>scp-p 22000/tmp/dumpeddb.sql [e-mail protected]:/tmp If the server has already built a library, then can direct data: shell>mysql-u [user]-P -d [Database name] </tmp/dumpeddb.sql If the library is not yet in use, we need to build the library before the data is directed. shell>mysql-u [user]-p-e ' Create DATABASE ' databasename ' Finally we need to create an account on both servers to synchronize the data. Shell>mysql-u root-pmysql>grant REPLICATION SLAVE, REPLICATION CLIENT, SUPER on databasename.* to replicate Identi Fied by ' Replpassword '; mysql>flush privileges; Modify MY.CNFFirst, we need to modify the bind-address option. Ensure that the MySQL listener is on a network that can be accessed by the other side, and of course, if you listen to the 0.0.0.0, there will be no problem, but doing so usually leads to some security issues. I would normally let it listen to the intranet address.bind-address = 10.10.1.1 (master server)bind-address = 10.10.1.2 (from server) Add the following to the master server's my.cnf

1. Server-id = 1
2. Auto-increment-increment = 2
3. Auto-increment-offset = 1
4. Log-bin =/var/log/mysql/log-bin.log
5. Binlog-do-db = DatabaseName
6. binlog-ignore-db = mysql
7. binlog-ignore-db = Test

Server-Id= 1Auto-Increment-Increment= 2Auto-Increment-Offset= 1 Log-Bin= /var/Log/Mysql/Log-Bin.LogBinlog-Do-Db=databasename Binlog-Ignore-Db=MySQL Binlog-Ignore-Db=Test

Server-id Server ID, we have to assign two machines different server-id.log-bin, turn on MySQL binlog (binary log). When on, MySQL logs all the modifications to the Binlog. Can be viewed with the Mysqlbinlog tool, which is a one-piece SQL statement. Slave I/O reads the host's Binlog and then executes the read content on the slave, which is the basic principle of MySQL master-slave synchronization. BINLOG-DO-DB, set which databases write Binlog. BINLOG-INGORE-DB, set which databases do not write Binlog. With these two options we can set which libraries synchronize which libraries are out of sync. The two options of Auto-increment-incrment and Auto-increment-offset are set to prevent collisions between 2 servers that produce critical fields. If they use different cheap, such as one according to 1,3,5,7 increase and the other one according to that 2,4,6,8 increase. auto-incrment-increment=2 automatically increments the field each step is 2,auto-increment-offset=1 the initial value of the Automatically incremented field is 1 Add the following to the slave server

1. Server-id = 2
2. Auto-increment-increment = 2
3. Auto-increment-offset = 2
4. Log-bin =/var/log/mysql/log-bin.log
5. Binlog-do-db = DatabaseName
6. binlog-ignore-db = mysql
7. binlog-ignore-db = Test

Server-Id= 2Auto-Increment-Increment= 2Auto-Increment-Offset= 2 Log-Bin= Log-Bin.LogBinlog-Do-Db=demo1 Binlog-Ignore-Db=MySQL Binlog-Ignore-Db=Testbinlog-Ignore-Db=Demobinlog-Ignore-Db=Wang

Note that the value of the Auto_increment_offset option here is different from the primary server. Restart MySQL service separatelyShell>/etc/init.d/mysql Restart or shell>mysql-u root-p-E "RESET MASTER" Configuring host to slave synchronizationRecords the master status of the host mysql> show Master status\g*************************** 1. Row *************************** file:log-bin.log.000001 position:98 Binlog_do_db:databasenamebinlo G_ignore_db:mysql,test1 row in Set (0.00 sec) Configures the master parameter Mysql>change master to, Master_host= ' 10 based on the host display results .10.1.1 ', master_user= ' replicate ',--master_password= ' Replpassword ', master_log_file= ' log-bin.log.000001 ', master_log_pos=98;

Change MASTER to master_host= ' 192.168.1.108 ', master_user= ' replicate ', master_password= ' 123456 ', master_log_file= ' Log-bin.000001 ', master_log_pos=120;

Where Master_host, Master_user, Master_password These parameters can be in the my.cnf. But I personally prefer to configure in MySQL, on the one hand, the security point, on the other hand, Master_log_file and master_log_pos These parameters are still in the MySQL configuration, rather than a piece in the MYQL to configure the convenience. Configure slave-to-host synchronization(The steps in the previous section are performed in turn). Record the master status from the machine mysql> show Master status\g*************************** 1. Row *************************** file:log-bin.log.000001 position:98 Binlog_do_db:databasenamebinlo G_ignore_db:mysql,test1 row in Set (0.00 sec) Configures the master parameter Mysql>change master to, master_host= ' 1 on the host based on the results displayed by the slave 0.10.1.2 ', master_user= ' replicate ', master_password= ' Replpassword ', master_log_file = ' log-bin.log.000001 ', master_log_pos=98 change MASTER to master_host= ' 192.168.1.109 ', master_user= ' replicate ' , master_password= ' 123456 ', master_log_file= ' log-bin.000001 ', master_log_pos=120; Open slave thread mysql>start slave respectively; Unlock mysql>unlock tables;

MySQL dual-master Bidirectional Synchronization