mysql mm 架构_运维架构师-开源数据库MySQL运维实战-019

开源数据库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上查看数据