MySQL master-slave replication configuration MySQL data automatic backup configuration

The benefits of using MySQL master-slave replication are:

1, the use of the master-slave server architecture, stability can be improved. If the primary server fails, we can use the server to provide services;
2, in the master and slave server separately processing user's request, may enhance the data processing efficiency;
3. Copy the data from the primary server to the server to protect the data from accidental loss;

MySQL server master-slave configuration, is a very simple thing, but not from scratch, always in others have been installed on the MySQL server, this will involve, MySQL version, startup files, and some other issues.

But never mind, ask two points first.

1, the MySQL configuration file my.cnf location

2, how to start, stop MySQL, find a good startup file

Assuming that there are two machines, MySQL has been installed (as far as possible version, and two machines the same network, you can ping)

A friend said: "From the server, can not be lower than the master server version", but I was below, there is no problem.

Host a:192.168.1.100

From Machine b:192.168.1.101

can have multiple machine

1, first login host A

The code is as follows	Copy Code
Mysql>grant REPLICATION SLAVE on *.* to ' backup ' @ ' 192.168.1.101 ' identified by ' 123456 ';

Given the permissions from the machine, there are more than one cluster machine, on the execution of multiple

2, open the my.cnf of host A, input

The code is as follows	Copy Code
Server-id = 1 #主机标示, integer 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, input

The code is as follows	Copy Code
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

There are a number of ways, I say the stupidest one, first mysqldump export host a data test is Test.sql

Then, build the database from machine B test,mysql import test.sql into the test library

5, first restart the host a MySQL, and then restart from machine b MySQL

6. Verify

In host A, mysql>show master status/g;

Mysql>show slave status/g in the form of a machine B;

Can see roughly these things

The code is as follows	Copy Code
file:mysql-bin.000001 position:1374 Binlog_do_db:test Binlog_ignore_db:mysql

You can do some insert, UPDATE, and DELETE operations in host A to see if the Host B has been modified