MySQL configuration master-slave backup

First, describe the cluster structure:

Master : 10.1.111.46

Slave : 10.1.111.47

First, the Master Operation:

1 , to Root User Login Master , create Slave Replicated Users:

Mysql> grantreplication SLAVE on * * to ' backup ' @ ' 10.1.111.47 ' identified by ' 123456 ';

2 , create a binary log file, and modify the user group for the log file

# mkdir-p/var/log/mysql# touch/var/log/mysql/mysql-bin.log# chown-r Mysql.mysql/var/log/mysql

3 , modify MySQL configuration file /etc/my.cfg

Server-id =1 #主机标示, integer log_bin =/var/log/mysql/mysql-bin.log #确保此文件可写read-only =0 #主机, Read and write can be binlog-do-db =test1 #需要备份数据, more than write multiple lines binlog-ignore-db =mysql #不需要备份的数据库, more than write multiple lines

4 , restart MySQL

# Service Mysqld Restart

Second, the Slave Operation:

1 , create a binary log file, and modify the user group for the log file

# mkdir-p/var/log/mysql# touch/var/log/mysql/mysql-bin.log# chown-r Mysql.mysql/var/log/mysql

2 mysql /etc/my.cfg

Server-id =2log_bin =/var/log/mysql/mysql-bin.logmaster-host =10.1.111.46master-user =backupmaster-pass =123456master-port =3306master-connect-retry=60# If the primary server is found to be disconnected from the server, the time difference (in seconds) to reconnect replicate-do -db =test1 #只复制某个库replicate-ignore-db =mysql #不复制某个库

3 , restart MySQL

# Service Mysqld Restart

4 , landing MySQL View Status

Mysql> Show slavestatus\g;mysql> ... relay_master_log_file:mysql-bin.000001 Slave_io_running:yes Slave_sql_running:yes Replic Ate_do_db:test1 Replicate_ignore_db:mysql ...

The configuration is successful if the above status is shown.

MySQL configuration master-slave backup