Linux 开源数据库Mysql-10-mysql集群双主双从

Linux 开源数据库Mysql-10-mysql集群双主双从

实验环境4台机器
192.168.159.130 master1
192.168.159.131 master2
192.168.159.132 slave1
192.168.159.133 slave2

主master1

准备数据（验证主从同步使用）主从未建立之前得数据不会同步到备库
备份数据

 mysqldump -p'qwer1234QWER!@#$' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql.sql

开启二进制日志

vim /etc/my.cnf
添加以下内容
[mysqld]
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1

创建复制用户

grant replication slave,replication client on *.* to 'rep'@'192.168.159.%' identified by 'qwer1234QWER!@#$';

设置主服务器

change master to master_host='master2',master_user='rep',master_password='qwer1234QWER!@#$',master_auto_position=1;
start slave;
show slave status\G;

查看状态是否都正常

主master2

1.还原数据备份文件
登陆数据库还原数据

set sql_log_bin=0;
souurce /root/2022-08-04-mysql.sql

2.启动服务器序号

vim /etc/my.cnf
注意serverid要与master1不同
[mysqld]
log-bin=mysql-bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1

创建复制用户并授权

grant replication slave,replication client on *.* to 'rep'@'192.168.159.%' identified by 'qwer1234QWER!@#$';
flush privileges;

设置主服务器

change master to master_host='master1',master_user='rep',master_password='qwer1234QWER!@#$',master_auto_position=1;
start slave;
show slave status\G;

以上双主状态完成，双方会进行同步

slave1

vim /etc/my.cnf
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

systemctl restart mysqld

change master to master_host='master1',master_user='rep',master_password='qwer1234QWER!@#$',master_auto_position=1 for channel 'master1';

change master to master_host='master2',master_user='rep',master_password='qwer1234QWER!@#$',master_auto_position=1 for channel 'master2';
start slave;
show slave status\G

salve2

vim /etc/my.cnf
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

systemctl restart mysqld`

change master to master_host='master1',master_user='rep',master_password='qwer1234QWER!@#$',master_auto_position=1 for channel 'master1';

change master to master_host='master2',master_user='rep',master_password='qwer1234QWER!@#$',master_auto_position=1 for channel 'master2';

start slave;
show slave status\G;