Real-time MySQL synchronization-Dual-host mutual backup

MySQL real-time synchronization-master-slave mode.

Setting method:
Step 1 create a synchronization user on the master Service server and grant permissions
Grant replication slave on *. * to backup @ '%' identified by '123 ';
Grant the replication permission to any Connected Backup user
(For MySQL 5.0 and earlier than version 4.0.2, the following statement is not supported for replication.
Grant file on *. * to backup @ '%' identified by '2013 ';
)

Step 2 configure mysql. ini:
Master Server

# Master
Server-id = 1
Log-bin = "C: \ mysql \ logs \ mysql_binary_log"
BINLOG-do-DB = "test"

Explanation:
1) server-ID indicates that the serial number of the local machine is 1. The server-id of slave cannot be the same as that of the master.
2) log-bin indicates that BINLOG is enabled. This option can be used to write logs to slave relay-log through I/O, 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) BINLOG-do-DB = test indicates that the database to be backed up is test,
To back up multiple databases, write multiple rows as follows:
BINLOG-do-DB = backup1
BINLOG-do-DB = backup2
BINLOG-do-DB = backup3


Slave Server

# Replication slave
Server-id = 2
Master-host = "192.168.10.33"
Master-user = "backup"
Master-Password = "123456"
Master-Port = 3306
Master-connect-retry = 60
Replicate-do-DB = test

Explanation:
1) server-id = 2 indicates the serial number of the machine;
2) master-host = 192.168.10.33 indicates that the master is 192.168.10.33 when the local machine is doing slave;
3) master-user = username indicates a user with permissions open on the master, so that the user can connect to the master from the slave and perform replication;
4) master-Password = PASSWORD indicates the password of the authorized user;
5) master-Port = port 3306 of MySQL service listen3306 on the master;
6) master-connect-retry = 60 synchronization interval;
7) replicate-do-DB = backup indicates that the database is synchronized to the backup database. The database name is case sensitive and must be consistent with that of the master database;
Log-bin open the logbin option to write to the slave I/O thread;

Finally, restart the MySQL of the two machines.

Step 3 import the existing records on the master to slave.

------------------------------------------------
View status and debug

1. view the Master Status
Show Master status;
Position should not be 0
2. view the slave status
Show slave status;
Slave_io_running | the slave_ SQL _running fields must 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 \ data \ hostname. Err


Delete relay-log.info, master.info, xwb0703-relay-bin .*
Slave uses these two files to track the number of master binary logs processed.

Each time the relay-log.info is deleted, master.info will re-download the update from the master server.
You must delete the files in log-bin on the primary service.