开源数据库MySQL DBA运维实战
主从复制案例
2.4 双主双从(MM-SS)
前言
前面的实验,主服务器单节点设置。假如主服务器故障会影响全局的写入事件。故设置双主。目前:已经设置master1为master2的主服务器,只需设置master2为master1的主服务器。
1 设置master2为master1的主服务器,在master2 上进行授权
mysql> grant replication slave, replication client on *.* to 'rep'@'192.168.2.%' identified by 'XuLei@123';
mysql> flush privileges;
2 master1
mysql> change master to
master_host='master2',
master_user='rep',
master_password='XuLei@123',
master_auto_position=1;
start slave;
show slave status\G;
测试
master1上插入数据,在master2上观察
master2上插入数据,在master1上观察
双方同步成功,双主设置完成。
3 同步现有数据库
master1备份数据
mysqldump -p'XuLei@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
scp -r 2017-1-1-mysql-all.sql slave1:/tmp
scp -r 2017-1-1-mysql-all.sql slave2:/tmp
slave1 slave2导入数据
[root@slave1 ~]# mysql -p'XuLei@123' < /tmp/2017-1-1-mysql-all.sql
[root@slave2 ~]# mysql -p'XuLei@123' < /tmp/2017-1-1-mysql-all.sql
4 启动从服务器ID,gtid
slave1上启动gtid
[root@slave1 ~]# vim /etc/my.cnf
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@slave1 ~]# systemctl restart mysqld
slave2上启动gtid
[root@slave2 ~]# vim /etc/my.cnf
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@slave1 ~]# systemctl restart mysqld
5 设置主服务器
slave1配置主服务器
mysql> change master to
master_host='master1',
master_user='rep',
master_password='XuLei@123',
master_auto_position=1 for channel 'master1';
mysql> change master to
master_host='master2',
master_user='rep',
master_password='XuLei@123',
master_auto_position=1 for channel 'master2';
start slave;
show slave status\G;
slave2配置主服务器
mysql> change master to
master_host='master1',
master_user='rep',
master_password='XuLei@123',
master_auto_position=1 for channel 'master1';
mysql> change master to
master_host='master2',
master_user='rep',
master_password='XuLei@123',
master_auto_position=1 for channel 'master2';
mysql> start slave;
mysql> show slave status\G;
6 测试
master1 插入数据
master2插入数据
停止master1,在master2上插入数据
停止slave1,在slave2上查看数据