Centos7搭建基于GTID的MySQL的M-M-S-S架构

简介

M-M-S-S架构即双主双从结点，具体架构为两台主结点互为对方的从节点，且两台从节点机器均有两个主结点，可用于当其中一台主结点服务发生故障时，有备用主结点可以对业务进行支撑
主从复制
主从复制(也称 AB 复制)是将来自一个MySQL数据库服务器(主服务器)中的数据复制到一个或多个MySQL数据库服务器(从服务器)中，部署方法可参考:https://blog.csdn.net/xixixilalalahaha/article/details/106434408
GTID
从MySQL 5.6.5 开始新增了一种基于 GTID 的主从复制方式，GTID (Global Transaction ID)是全局事务ID，通过 GTID可以保证每一个在主库中提交的事务在整个数据库集群中有一个唯一的ID，因此当在主库上提交事务或者被从库应用时，可以通过ID定位和追踪每一个事务，不用再通过手工去可以找偏移量的值，这种方式强化了数据库的主备一致性，故障恢复以及容错能力，部署方法可参考:https://blog.csdn.net/xixixilalalahaha/article/details/107127855

前期准备

准备四台centos7虚拟机，配置ip和hostname,关闭防火墙和selinux
在/etc/hosts中添加主机名和IP地址映射

hostname	ip
master1	192.168.29.132
master2	192.168.29.138
slave1	192.168.29.131
slave2	192.168.29.133

配置MySQL

两个master节点添加主从复制账号
MySQL5.7:

mysql>grant replication slave on *.* to 'repl'@'%' identified by 'your_password';
mysql>flush privileges;

MySQL8：

mysql>create user 'repl'@'%' identified by 'your_password';
mysql>grant replication slave on *.* to 'repl'@'%';
mysql>flush privileges;

master1主机初始化数据

mysql> create database mydb;
mysql> use mydb;
mysql> create table test(id int primary key);
mysql> insert into test values(1);
mysql> insert into test values(2);

修改两个master结点的配置文件

master1

[root@master1 ~]# vi /etc/my.cnf 
[mysqld]
#设置Master主机信息
server-id=1
log-bin=binlog
#基于事务的Replication
#可以实现基于库的多线程复制,减少主从复制的延迟
#主机配置
gtid_mode=ON
#强制执行GTID一致性
enforce_gtid_consistency=1
#设定把master主机信息保存在表中
#默认以文件形式保存
master-info-repository=table
relay-log-info-repository=table
#auto_increment字段为为奇数ID，避免同时写入ID冲突
auto_increment_offset = 1
auto_increment_increment = 2

master2

[root@master2 ~]# vi /etc/my.cnf 
[mysqld]
server-id=2
log-bin=binlog
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=table
relay-log-info-repository=table
#auto_increment字段为为偶数ID，避免同时写入ID冲突
auto_increment_offset = 2
auto_increment_increment = 2

配置两台slave主机的配置文件

slave1

[root@slave1 ~]# vi /etc/my.cnf
[mysqld]
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1 
master-info-repository=table
relay-log-info-repository=table

slave2

[root@slave2 ~]# vi /etc/my.cnf
[mysqld]
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1 
master-info-repository=table
relay-log-info-repository=table

备份master1主机的数据并导入到其余所有主机中

master1备份数据

[root@master1 ~]#  mysqldump -u root -p  -A > mydb.sql
[root@master1 ~]# scp mydb.sql root@master2:/tmp/
[root@master1 ~]# scp mydb.sql root@slave1:/tmp/
[root@master1 ~]# scp mydb.sql root@slave2:/tmp/

其余结点中导入数据

[root@master2 ~]# mysql -u root -p < /tmp/mydb.sql 
[root@slave1 ~]# mysql -u root -p < /tmp/mydb.sql 
[root@slave2 ~]# mysql -u root -p < /tmp/mydb.sql 

配置两台master结点互为主从

master1

mysql>stop slave;
mysql>change master to
	->master_host='192.168.29.138',
	->master_user='repl',
	->master_password='your_password',
	->master_auto_position=1
参数含义：
	master_host:主机IP地址
	master_user:主从复制远程主机用户
	master_password:主从复制远程主机用户的密码
	master_auto_position:自动寻找偏移量

master2

mysql>stop slave;
mysql>change master to
	->master_host='192.168.29.132',
	->master_user='repl',
	->master_password='your_password',
	->master_auto_position=1

查看两台主机中slave状态

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

配置两台slave结点为两个master的从结点

slave1

mysql>stop slave;
mysql>change master to
	->master_host='192.168.29.132',
	->master_user='repl',
	->master_password='your_password',
	->master_auto_position=1 for channel 'repl-master-1';
参数for channel为指定主结点在不同的频道

mysql>stop slave;
mysql>change master to
	->master_host='192.168.29.138',
	->master_user='repl',
	->master_password='your_password',
	->master_auto_position=1 for channel 'repl-master-2';

slave2

mysql>stop slave;
mysql>change master to
	->master_host='192.168.29.132',
	->master_user='repl',
	->master_password='your_password',
	->master_auto_position=1 for channel 'repl-master-1';

mysql>stop slave;
mysql>change master to
	->master_host='192.168.29.138',
	->master_user='repl',
	->master_password='your_password',
	->master_auto_position=1 for channel 'repl-master-2';

查看两台slave中的slave状态

mysql>start slave;
mysql>show slave status\G;
#可以看到有两份主机信息
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试验证

master1结点操作数据库

mysql> insert into test values(1111);

master2结点操作数据库

mysql> insert into test values(2222);

slave1和slave2结点查看数据

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
| 1111 |
| 2222 |
+------+
4 rows in set (0.00 sec)

模拟master1宕机

关闭master1的mysql服务

[root@master ~]# systemctl stop mysqld

查看其余结点的情况

 Last_IO_Error: error reconnecting to master 'repl@192.168.29.132:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.29.132' (111)

master2结点操作数据库

mysql> insert into test values(3);
mysql> insert into test values(4);

slave1和slave2结点查看数据

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
| 1111 |
| 2222 |
+------+
6 rows in set (0.00 sec)

重启master1的mysql服务

[root@master ~]# systemctl restart mysqld
#其余结点的从节点状态恢复正常

master1查询数据

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
| 1111 |
| 2222 |
+------+
6 rows in set (0.00 sec)

模拟同时写入

创建自增表

mysql> drop table test;
mysql> create table test(id int(4) primary key auto_increment);
mysql> desc test;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | int(4) | NO   | PRI | NULL    | auto_increment |
+-------+--------+------+-----+---------+----------------+

master1写入数据

mysql> insert into test values();
mysql> insert into test values();
mysql> insert into test values();
mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
+----+

master2写入数据

mysql> insert into test values();
mysql> insert into test values();
mysql> insert into test values();
mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
|  6 |
|  8 |
| 10 |
+----+

同时写入

#通过xshell同时操作master1和master2
#master1
mysql> insert into test values();
#master2
mysql> insert into test values();
#查看数据
mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
|  6 |
|  8 |
| 10 |
| 11 |
| 12 |
+----+

故障

配置过程中报错

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. To find the missing transactions, see the master's error log or the manual for GTID_SUBTRACT
原因是主结点数据库进行了过多数据操作或进行过重启等导致binlog产生了变化,从节点无法进行同步

解决方法

在master1中进行操作后在备份数据到其他从节点
mysql>reset master
mysql>reset slave