MySQL server master-slave database synchronization configuration

First of all need in the same LAN two machines (of course, can also use a machine virtual two machines out), are installed on the MySQL service.

Host a:192.168.1.100

From Machine b:192.168.1.101

Can have more than one from the machine.

1, first login to host A, execute the following commands given from the machine permissions, if there are more than one cluster machine, on the execution of several times:

Mysql>grant REPLICATION SLAVE on *.* to ' backup ' @ ' 192.168.1.101 ' identified by ' 123456 ';

2, open the my.cnf of host A, enter the following configuration parameters:

Server-id = 1 #主机标示, integer www.111cn.net
Log_bin =/var/log/mysql/mysql-bin.log #确保此文件可写
Read-only =0 #主机, read and write can be
Binlog-do-db =test #需要备份数据, multiple write multiple lines
Binlog-ignore-db =mysql #不需要备份的数据库, multiple write multiple lines

3, open the MY.CNF from the Machine B, enter the following configuration parameters:

Server-id = 2
Log_bin =/var/log/mysql/mysql-bin.log
Master-host =192.168.1.100
Master-user =backup
Master-pass =123456
Master-port =3306
Master-connect-retry=60 #如果从服务器发现主服务器断掉, Reconnection time difference (sec)
Replicate-do-db =test #只复制某个库
Replicate-ignore-db=mysql #不复制某个库

4, synchronization database

After the above configuration, restart the host a and from the machine B, can automatically achieve synchronization.

5. Verify

In host A, mysql>show master STATUSG;

Mysql>show slave statusg in the form of a machine B;

Can see roughly these things

file:mysql-bin.000001
position:1374
Binlog_do_db:test
Binlog_ignore_db:mysql

Alternatively, in host A, insert, UPDATE, and DELETE operations can be done to see if the Host B has been modified.