Mysql real-time synchronization-Dual-host mutual backup (dual master)

Mysql real-time synchronization-Dual-host mutual standby (dual master) Setting Method: Step 1 sets the user on server A (192.168.1.43) as backup, 123456, And the synchronized database as test; B Service Server (192.168.1.23) the user is root, 123456, And the synchronized database is test. Step 2 configure mysql. ini: server A # Replication master server-id = 10 log-bin = "E: /MySQL/logs/mysql_binary_log "binlog-do-db = test binlog-Ignore-db = information_schema # For unidirectional backup, only Part A needs to add server- id # Replication slave master-host = "192.168.1.23" master-user = root master-password = "123456" master-port = 3306 master-connect-retry = 60 replicate-do-db = test replicate-Ignore-db = information_schema B server # Replication master server-id = 2 log-bin = "c: /mysql5/logs/mysql_binary_log "binlog-do-db = test binlog-Ignore-db = information_schema # Replication slave master-host =" 192.168.1.43 "master-user = backup master-password = 123456 master-port = 3306 master-connect-retry = 60 replicate-do-db = test replicate-Ignore-db = information_schema ============ ========================================================== ======== explanation: 3) binlog-do-db = test indicates that the database to be backed up is the test database. If you need to back up multiple databases, write multiple lines as follows: binlog-do-db = backup1 binlog-do-db = backup2 binlog-do-db = backup3 explanation: 1) server-id = 2 indicates the serial number of the current machine., b's server-id cannot be the same; 2) log-bin indicates that binlog is enabled, and this option can be enabled before I/O can be written to Slave's relay-log, which is also a prerequisite for replication; mysql_binary_log is the name of the log file. mysql creates several log files with Different Extensions named mysql_binary_log. 3) master-host = "192.168.1.23" indicates that the master of Server Load balancer A is 192.168.1.23; 4) master-user = root indicates A user with permissions on the master, enable the Server Load balancer to connect to the master node and copy it. 5) master-password = 123456 indicates the password of the authorized user; 6) master-port = port 6 of MySQL service Listen3306 on the 3306 master node; 7) master-connect-retry = 60 synchronization interval; 8) replicate-do-db = test indicates synchronizing the backup database; and then restart the mysql of the two machines. view STATUS and debug 1. Check the master status. The Position should not be 0 2. Check the slave status show slave status. The Slave_IO_Running | Slave_ SQL _Running fields should be YES | YES. show processlist; there will be two records related to synchronization state: Has read all relay log; waiting for the slave I/O thread to update it and s Waiting for master to send event. 3. Error Log: MySQL installation directory dataHostname. related commands on the err master server: show master status show slave hosts show {master | binary} logs show binlog events purge {master | binary} logs to 'Log _ name' purge {master | binary} logs before 'date' reset master (earlier version of flush master) set SQL _log_bin = {0 | 1} slave related commands on the server: slave start slave stop slave stop IO_THREAD // This thread writes the log of the master segment to the local SLAVE start IO_THREAD slave stop SQL _THREAD // This thread applies the local log to the database SLAVE start SQL _THREAD reset slave set global SQL _SLAVE_SKIP_COUNTER load data from master show slave status (SUPER, replication client) change master to MASTER_HOST =, MASTER_PORT =, MASTER_USER =, MASTER_PASSWORD = // dynamically CHANGE master information purge master [before 'date'] delete logs that have been synchronized on the master end