MySQL real-time dual standby

Setup method:

Step one set

A service suit (192.168.1.43) user is backup, 123456, synchronized database is test;

B Service Suit (192.168.1.23) user is root, 123456, synchronized database is test;

Step Two configuration Mysql.ini:

A server

#Replication Master

Server-id = 10

log-bin= "E:\MySQL\logs\mysql_binary_log"//must first create this file

Binlog-do-db=test

# 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

b Server

#Replication Master

Server-id = 2

Log-bin= "C:\mysql5\logs\mysql_binary_log"

Binlog-do-db=test

# 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

=============================================================

Explain:

3) binlog-do-db=test indicates that the database that needs to be backed up is the test database,

If you need to back up multiple databases, you should write more than one line, as follows:

Binlog-do-db=backup1

Binlog-do-db=backup2

Binlog-do-db=backup3

Explain:

1) server-id=2 indicates that the serial number of the machine, a, B Server-id cannot be the same;

2) Log-bin means open binlog, open this option can write to slave relay-log through I/O, also can be replication premise;

Where Mysql_binary_log is the name of the log file, MySQL will establish several log files with different extension names and files named Mysql_binary_log.

3) master-host= "192.168.1.23" indicates that a does slave when the master is 192.168.1.23;

4) Master-user=root here represents a privileged user that is open on master, allowing it to connect to master and replicate from slave;

5) master-password=123456 represents the password of the authorized user;

6) master-port=3306 Master on MySQL service Listen3306 port;

7) master-connect-retry=60 synchronization interval time;

8) Replicate-do-db=test means synchronous backup database;

Finally restart the MySQL for both machines.

------------------------------------------------

View status and Debug

1. View the status of master

SHOW MASTER STATUS;

Position should not be 0

2. View the status of the slave

show slave status;

slave_io_running | Slave_sql_running these two fields should be yes| YES.

Show Processlist;

There will be two records in sync with the state for have 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

4,change MASTER to

If the Slave of a is not started, slave_io_running is no.

May be the information of the master of B has changed,

View B show MASTER STATUS;

Record the File,position field. Assumed to be ' mysql_binary_log.000004 ', 98;

Execute under a:

Stop Slave;

Change MASTER to

Master_log_file = ' mysql_binary_log.000004 ',

Master_log_pos = 98;

Start Slave;

5,set GLOBAL sql_slave_skip_counter = n;

If the slave_sql_running of a is no.

The Err file records:

Slave:error ' Duplicate entry ' 1 ' for key 1 ' on query ....

It is possible that master did not synchronize successfully to slave, but there is already a record in slave. caused by the conflict.

Can be executed on a

SET GLOBAL sql_slave_skip_counter = n;

Skip a few steps. Again

Restart salve;

You can do it.

MySQL real-time dual standby