首先,说一下集群结构:
Master:10.1.111.46
Slave:10.1.111.47
一、对Master操作:
1、以root用户登录Master,创建Slave复制的用户:mysql> GRANTREPLICATION SLAVE ON *.* TO 'backup'@'10.1.111.47' IDENTIFIED BY '123456';
2、创建二进制日志文件并修改日志文件的用户组# mkdir -p/var/log/mysql
# touch /var/log/mysql/mysql-bin.log
# chown -R mysql.mysql /var/log/mysql
3、修改mysql配置文件/etc/my.cfgserver-id =1 #主机标示,整数
log_bin =/var/log/mysql/mysql-bin.log #确保此文件可写
read-only =0 #主机,读写都可以
binlog-do-db =test1 #需要备份数据,多个写多行
binlog-ignore-db =mysql #不需要备份的数据库,多个写多行
4、重启mysql# service mysqld restart
二、对Slave操作:
1、创建二进制日志文件并修改日志文件的用户组# mkdir -p/var/log/mysql
# touch /var/log/mysql/mysql-bin.log
# chown -R mysql.mysql /var/log/mysql
2、修改mysql配置文件/etc/my.cfgserver-id =2
log_bin =/var/log/mysql/mysql-bin.log
master-host =10.1.111.46
master-user =backup
master-pass =123456
master-port =3306
master-connect-retry=60#如果从服务器发现主服务器断掉,重新连接的时间差(秒)
replicate-do-db =test1 #只复制某个库
replicate-ignore-db =mysql #不复制某个库
3、重启mysql# service mysqld restart
4、登陆mysql查看状态mysql> show slavestatus\G;
mysql>
...
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB: test1
Replicate_Ignore_DB: mysql
...
如出现以上状态说明配置成功。