Configure master-slave backup for MySQL

For data security, data in the database is generally backed up. MySQL comes with master-slave backup, which is very convenient to use and has powerful functions and can provide approximate synchronization performance.

When configuring master-slave backup, you only need to let slave know the master'sAddressAndPort, And a permission to back up replication slaveAccountThe master does not need to know slave-related information.

MASTER: 192.168.1.123: 3306

First, configure the master. For Linux, the MySQL configuration file is generally/Etc/MySQL/My. CNFFor WindowsInstallMy. ini under the Directory

Add the following in the configuration file:Code(In some cases, this content may exist in the configuration file. You only need to remove the comment ):

Server-id = 1

Log-bin =/var/log/MySQL/mysql-bin.log

Binlog_do_db = XXXXX # database to be backed up

The configuration in slave is as follows:

Server-id = 2 # As long as it is larger than 1, 1 stands for Master

Master-host = 192.168.1.123

Master-Port = 3306

Master-user = iuser

Master-Password = useri

Master-connect-retry = 60 # If the connection fails, try it 60 seconds.

Replicate-do-DB = XXXX # the database to be backed up. The structure must be the same as that in the master.

Note:

Grant replication slave permission to iuser in master

Master>Grant replication slave on *. * To iuser @ '%' identified by 'useri ';

Replace iuser with the corresponding user name and useri with the corresponding password.

After the configuration is complete, first restart the Master, then restart the slave, and execute

Slave>Start slave;

In the master, you can view the status through show Master status, and in the slave, you can view the status through show slave status.

Test:

After inserting a record in the master table, you will soon be able to see the same data in the corresponding slave table. If there is no synchronization, you can view the error log to find the specific cause,

The error log in Windows is in the data directory of the installation directory, which is generally the *. Err file;

In Linux, the error log is usually stored in/var/log/MySQL/error. log.