Using MySQL dual-machine hot standby under Windows

I. Preparatory work

1. Prepare two servers (computers), access the LAN, make each other ping each other

2. Two servers are installed mysql-server-5.1, you must ensure that the MySQL version is consistent

3. Assume that server a:192.168.0.2, server b:192.168.0.3

Two. Create and test sync users

1. To establish a connection account on the primary server for the slave server, the account must be granted replication slave permissions. Because Server A and Server B are primarily from each other, a synchronous user is created separately.

Server A:

 on * * to ' replicate ' @ ' 192.168.0.3 ' identified by ' 123456 '; MySQL> Flush privileges;

Server B:

 on * * to ' replicate ' @ ' 192.168.0.2 ' identified by ' 123456 '; MySQL> Flush privileges;

2. After creating a sync Connection account, we can use the replicate account on the slave server (slave) to access the primary server (master) database to see if the connection is successful.

Server A:

C:\users\administrator> mysql-h192.168.0.3-ureplicate-p123456

Server B:

C:\users\administrator> mysql-h192.168.0.2-ureplicate-p123456

If the "Welcome to the MySQL monitor" appears. "And so on, indicates the ability to log in successfully, indicating that the two servers can be dual-machine hot standby operation.

Three. Modify the configuration file My.ini

1. Open the "MySQL installation path \my.ini" file on pc A and add the following configuration at the end of the file:

Server A:

Server-id=1Log-bin=backuplogsync_binlog=1binlog_format== 2= 1  Max_binlog_size=512mexpire_logs_days=1binlog_do_db=oabinlog_ignore_db=  mysqlbinlog_ignore_db=information_schemareplicate_do_db=oareplicate_wild_ignore_table =oa. logslave_skip_errors=all

Server B:

server-id=2Log-bin=backuplogsync_binlog=1binlog_format== 2= 2  Max_binlog_size=512mexpire_logs_days=1binlog_do_db=oabinlog_ignore_db=  mysqlbinlog_ignore_db=information_schemareplicate_do_db=oareplicate_wild_ignore_table =oa. logslave_skip_errors=all

2. Restart the MySQL service on Server A and Server B, respectively, and if successful, there is no problem.

net stop MySQL; C: Net start MySQL;

　　

3. The Master command allows you to view the primary server status of server A and Server B, respectively

Mysql> Show Master Status\g;

Using MySQL dual-machine hot standby under Windows