MYSQL database master/master synchronization _ MySQL

MYSQL supports unidirectional and asynchronous replication. one server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to the binary log file and maintains an index of the log file to track log loops. When a slave server connects to MYSQL supports unidirectional and asynchronous replication, one server acts as the master server during the replication process, and one or more other servers act as the slave server. The master server writes updates to the binary log file and maintains an index of the log file to track log loops. When an slave server is connected to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of the next update.

In the actual project, two hosts distributed in different regions are installed with MYSQL databases, and the two servers are both active and Standby. the customer requires that when one of the hosts fails, another application that can take over the server requires the data of the two databases to be consistent in real time. here, the MYSQL synchronization function is used to achieve synchronous replication between two machines.

The following are examples:

1. database synchronization settings

Host operating system: RedHat Enterprise Linux 5

Database version: mysql Ver 14.12 Distrib 5.0.22

Prerequisites: the MYSQL database is started normally.

Assume that the addresses of the two hosts are:

ServA: 10.240.136.9

ServB: 10.240.136.149

1.1 Configure synchronization account

Add an account on ServA that can be logged on to ServB:

Mysql> GRANT all privileges ON *. * TO tongbu @ '10. 240.136.149 'identified BY '123 ';

Add an account that can be logged on to ServB:

Mysql> GRANT all privileges ON *. * TO tongbu @ '10. 240.136.9 'identified by '123 ';

1.2 configure database parameters

1. log on to ServA as the root user and modify the my. cnf file of ServA.

Vi/etc/my. cnf

Add the following configuration to the configuration item of [mysqld:

1 default-character-set = utf8 2 3 log-bin = mysql-bin 4 5 relay-log = relay-bin 6 7 relay-log-index = relay-bin-index 8 9 server-id = 1 10 11 master-host = 10.240.136.149 12 13 master-user = tongbu 14 15 master-password = 123456 16 17 master-port = 3306 18 19 master-connect-retry = 30 20 21 binlog-do-db = umsdb 22 23 replicate-do-db = umsdb 24 25 replicate-ignore-table = umsdb. boco_tb_menu 26 27 replicate-ignore-table = umsdb. boco_tb_connect_log 28 29 replicate-ignore-table = umsdb. boco_tb_data_stat 30 31 replicate-ignore-table = umsdb. boco_tb_log_record 32 33 replicate-ignore-table = umsdb. boco_tb_workorder_record