MySQL Master-Slave backup

MySQL master-slave backup, listen to a noun is very tall, in fact, is the original implementation of MySQL, you only need a simple configuration can be achieved.

The first step: Keep the master-slave two database is synchronous, preferably manually synchronized ;

The second step: Stop two databases, change the configuration file respectively;

Below I use the following two addresses to illustrate the configuration process.

• Main database: 192.168.0.244
• From database: 192.168.0.8

master server database, add the following configuration:

Server-id = 244 #这个唯一就OK, usually take the IP address of the following several
Log-bin = E:\mysql\data\mysql-bin #日志所在目录
binlog-do-db = Test #这个是要同步的数据库

From the server database, add the following configuration:

Server-id = 8 #这个唯一就OK, usually take the IP address of the following several
replicate-do-db = Test #这个是要同步的数据库

The third step, restart the MySQL service of two servers respectively;

• net stop MySQL;
• net start MySQL;

Two server MySQL service to start up;

The fourth step, the master server authorized slave rights account

GRANT REPLICATION  on *. *  to ' BU '@ '%' by'123456';

Fifth step, log in to the master server, query the master State;

Show master status;

Notice the result, the result inside the thing, in the next will use.

Sixth step: Log in from the server, configure the slave from the server

 to Master_host='192.168.0.244', master_user='  Bu', Master_password='123456', Master_log_file  ='mysql-bin.000004', master_log_pos=516 ;

Master_log_pos is the above query position 516.

Seventh step, start the slave from the server

Start slave;

Eighth step, verify the Slave service status from the server

Show Slave Status\g

The following two states must be yes.

Slave_io_running:yes//This status must be Yes
Slave_sql_running:yes//This status must be Yes

If there is an item for no, it will not be synchronized successfully.

I've met slave_io_running for connecting. That could be the connection above change master to Master_host ... I wrote it wrong.

Nineth step, arbitrarily modify the data of the main database, from the server database immediately updated

MySQL Master-Slave backup