MySQL双向同步
MySql双向同步的配置整理。
1. Master
[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
user=mysql
# skip-bdb
# skip-innodb
# skip-networking
symbolic-links=0
#bind-address=127.0.0.1
#innodb_file_per_table=1
sync_binlog=1
server-id=4256076
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=blog
binlog_do_db=kids
binlog_do_db=radius
binlog_do_db=zenp
binlog_ignore_db=mysql
report-host=mail.myvm.net
relay-log=mysqld-relay-bin
master-host=176.34.57.108
master-user=mybackup
master-pass=aq1SW2de
master-port=3306
master-connect-retry=60
replicate-do-db=radius
replicate-do-db=blog
replicate-do-db=kids
replicate-do-db=zenp
replicate-ignore-db=mysql
slave-skip-errors=all
2. Slave
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1325430036
report-host=ec2.myvm.net
relay-log=mysqld-relay-bin-new
master-host=106.187.34.155
master-user=mybackup
master-pass=aq1SW2de
master-port=3306
master-connect-retry=60
replicate-do-db=radius
replicate-do-db=blog
replicate-do-db=kids
replicate-do-db=zenp
replicate-ignore-db=mysql
sync_binlog=1
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_do_db=blog
binlog_do_db=kids
binlog_do_db=radius
binlog_do_db=zenp
binlog_ignore_db=mysql
slave-skip-errors=all
3. Key Point
FLUSH TABLES WITH READ LOCK;
backup master da and restore in slave
UNLOCK TABLES;
4. Sample
# 查看 A 服务器主机状态（记录二进制开始文件，位置）
SHOW MASTER STATUS;
# B 服务器锁表（锁表状态下不能终止mysql进程，否则会失败）
FLUSH TABLES WITH READ LOCK;
# 修改 B 服务器配置
CHANGE MASTER TO MASTER_HOST=’192.168.1.100′,MASTER_USER=’backup’, MASTER_PASSWORD=’123′,MASTER_LOG_FILE=’binlog.000001′,MASTER_LOG_POS=107;
# 开启 B 服务器同步进程
START SLAVE;
# 查看 B 服务器同步状态是否正常
SHOW SLAVE STATUS;
# 查看 B 服务器主机（记录二进制开始文件，位置）
SHOW MASTER STATUS;
# 修改 A 服务器配置
CHANGE MASTER TO MASTER_HOST=’192.168.1.101′,MASTER_USER=’backup’,MASTER_PASSWORD=’123′,MASTER_LOG_FILE=’binlog.000001′,MASTER_LOG_POS=107;
# 开启 A 服务器同步进程
START SLAVE;
# 分别查看 A B 服务器同步状态，确定是否成功
SHOW SLAVE STATUS;SHOW MASTER STATUS;
# 解锁 A B 服务器
UNLOCK TABLES;
5. Reference:
http://database.51cto.com/art/201005/201636.htm
http://www.2cto.com/database/201108/99584.html
http://www.aixchina.net/club/viewthread.php?tid=26916
相关文章: