文档介绍:MySQL复制实现互为主从双机热备
A B 为两台MySQL服务器,均开启二进制日志,数据库版本
一、服务器参数
[A 服务器]
log-bin=mysql-bin
server-id = 1
binlog-MySQL复制实现互为主从双机热备
A B 为两台MySQL服务器,均开启二进制日志,数据库版本
一、服务器参数
[A 服务器]
log-bin=mysql-bin
server-id = 1
binlog-do-db = test
binlog-ignore-db = mysql
replicate-do-db = test
replicate-ignore-db = mysql
master-host =
master-user = backup
master-password = 123
master-port = 3306
master-connect-retry = 10
#sync-binlog = 1
[B 服务器]
log-bin=mysql-bin
server-id = 2
binlog-do-db = test
binlog-ignore-db = mysql
replicate-do-db = test
replicate-ignore-db = mysql
master-host =
master-user = backup
master-password = 123
master-port = 3306
master-connect-retry = 10
#sync-binlog = 1
二、操作步骤
# A B 服务器停止同步
STOP SLAVE;
# A B 服务器清空Master日志
RESET MASTER;
# A 服务器授权同步账户
GRANT REPLICATION SLAVE ON *.* TO ‘backup’@'%’ IDENTIFIED BY ’123′;
FLUSH PRIVILEGES;
# A B 服务器锁表(锁表状态下不能终止mysql进程,否则会失败)
FLUSH TABLES WITH READ LOCK;
# 如果使用SSH,需要重新开启,复制数据库文件。
tar -cvf /tmp/mysql- /usr/local/mysql/data
tar -xvf /tmp/mysql- /usr/local/mysql/data
# 查看 A 服务器主机状态(记录二进制开始文件,位置)
SHOW MASTER STATUS;
# B 服务器锁表(锁表状态下不能终止mysql进程,否则会失败)
FLUSH TABLES WITH READ LOCK;
# 修改 B 服务器配置
CHANGE MASTER TO MASTER_HOST=’′,MASTER_USER=’backup’, MASTER_PASSWORD=’1