【备注】

master1 xx.104.242.89
master2 xx.182.126.76

同步的数据库 ultrax
同步的数据库 blog

在master1上面配置

vi /etc/my.cnf

主从配置master1 xx.104.242.89

user = mysql
log_bin = mysql-bin
relay_log=relay-bin
binlog_format=mixed
server_id = 1
binlog-do-db=blog
binlog-do-db=ultrax
binlog-checksum = none
replicate-do-db=blog
replicate-do-db=ultrax
log-slave-updates=1
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1 
expire-logs-days = 7
skip-slave-start

重启master1的mysql服务

service mysqld restart

在master2上面配置

vi /etc/my.cnf

主从配置master2 xx.182.126.76

user = mysql
log-bin = mysql-bin
relay-log=relay-bin
binlog_format=mixed
server-id = 2
binlog-do-db=blog
binlog-do-db=ultrax
binlog-checksum = none
replicate-do-db=blog
replicate-do-db=ultrax
log-slave-updates=1
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
expire-logs-days = 7
skip-slave-start

重启master2的mysql服务

service mysqld restart

是上面两处配置只有两个参数不同：

server_id
auto_increment_offset

首先锁定master1写操作，避免在备份同步期间造成数据差异

mysql> FLUSH TABLES WITH READ LOCK;

master1导出需要同步的数据库

mysqldump -uroot -p123456 blog > /root/bak/blog.sql;
mysqldump -uroot -p123456 ultrax > /root/bak/ultrax.sql;

master2还原数据库

mysql -uroot -p123456 blog < /root/bak/blog.sql;
mysql -uroot -p123456 ultrax < /root/bak/ultrax.sql;

查看当前master1上的binlog文件及位置

mysql> show master status;

将master1(xx.104.242.89)设为master2的主服务器(master2上执行)

mysql> change master to master_host = 'xx.104.242.89', master_user = 'www', master_password = 'pwd123', master_log_file = 'mysql-bin.000004', master_log_pos = 150;

启动

mysql> start slave;

查看状态

mysql> show slave status\G;

查看当前master2上的binlog文件及位置

mysql> show master status;

将master2(xx.182.126.76)设为master1的主服务器(master1上执行)

mysql> change master to master_host = 'xx.182.126.76', master_user = 'www', master_password = 'WwwPwd_db', master_log_file = 'mysql-bin.000004', master_log_pos = 8573;

启动

mysql> start slave;

查看状态

mysql> show slave status\G;

取消master1上面的写入锁定

mysql>UNLOCK TABLES;