mysql5.7-MGR集群搭建

妖狐艹你老母 2022-10-02 11:59 225阅读 0赞

一、 MGR介绍  
MySQL Group Replication（简称MGR）是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。MySQL组复制提供了高可用、高扩展、高可靠的MySQL集群服务。  
1）高一致性，基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；  
2）高容错性，只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制；  
3）高扩展性，节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；  
4）高灵活性，有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有server都可以同时处理更新操作。  
MGR是MySQL数据库未来发展的一个重要方向。

1、 MGR基础结构要求  
1）引擎必须为innodb,因为需事务支持在commit时对各节点进行冲突检查  
2）每个表必须有主键，在进行事务冲突检测时需要利用主键值对比  
3）必须开启binlog且为row格式  
4）开启GTID，且主从状态信息存于表中（–master-info-repository=TABLE 、–relay-log-info-repository=TABLE），–log-slave-updates打开  
5）一致性检测设置–transaction-write-set-extraction=XXHASH64

2、 MGR使用限制  
1）RP和普通复制binlog校验不能共存，需设置–binlog-checksum=none  
2）不支持gap lock(间隙锁)，隔离级别需设置为read\_committed  
3）不支持对表进行锁操作（lock /unlock table）,不会发送到其他节点执行 ,影响需要对表进行加锁操作的情况，列入mysqldump全表备份恢复操作  
4）不支持serializable(序列化)隔离级别  
5）DDL语句不支持原子性，不能检测冲突，执行后需自行校验是否一致  
6）不支持外键：多主不支持，单主模式不存在此问题  
7）最多支持9个节点：超过9台server无法加入组

二、 MGR环境准备  
1、 数据库服务器规划  
![在这里插入图片描述][20190618191026336.png]  
2、 在三台主机上设置hostname和IP映射  
\[root@xuegod140 ~\]\# vim /etc/hosts \#120/130/140都设置hosts文件  
192.168.2.120 [xuegod120.com][]  
192.168.2.130 [xuegod130.com][]  
192.168.2.140 [xuegod140.com][]  
注释：这里的hostname必须和hostname文件里写的一致

三、 创建复制环境  
1、 xuegod120复制环境配置  
1） 修改my.cnf文件  
server\_id = 100 \#服务ID  
gtid\_mode = ON \#全局事务  
enforce\_gtid\_consistency = ON \#强制GTID的一致性  
master\_info\_repository = TABLE \#将master.info元数据保存在系统表中  
relay\_log\_info\_repository = TABLE \#将relay.info元数据保存在系统表中  
binlog\_checksum = NONE \#禁用二进制日志事件校验  
log\_slave\_updates = ON \#级联复制  
log\_bin = binlog \#开启二进制日志记录  
binlog\_format = ROW \#以行的格式记录

transaction\_write\_set\_extraction = XXHASH64 \#使用哈希算法将其编码为散列  
loose-group\_replication\_group\_name = ‘ce9be252-2b71-11e6-b8f4-00212844f856’ \#加入的组名，可以自定义，格式是这种就行，三台主机需要一致  
loose-group\_replication\_start\_on\_boot = off \#不自动启用组复制集群  
loose-group\_replication\_local\_address = ‘[xuegod120.com:33061][xuegod120.com_33061]’ \#以本机端口33061接受来自组中成>员的传入连接，这里的名字需要些hostname文件里面的名字  
loose-group\_replication\_group\_seeds = ‘[xuegod120.com:33061][xuegod120.com_33061], [xuegod130.com:33062][xuegod130.com_33062], [xuegod140.com:33063][xuegod140.com_33063]’ \#组中成员访问表  
loose-group\_replication\_bootstrap\_group = off \#不启用引导组  
保存并重启服务  
注释：这里可以先不重启mysql服务，避免数据库会将操作记录到二进制日志文件，如果这里重启了mysqld服务，那么可以进入mysql关闭日志记录  
mysql> set sql\_log\_bin=0;或off

2） 建立复制账号  
\[root@xuegod120 ~\]\# mysql -uroot -p123456 -A  
mysql> set sql\_log\_bin=0; \#这里上面如果没有重启mysql服务，不需要这一步操作  
mysql> grant replication slave on *.* to repl@‘192.168.2.%’ identified by ‘123456’;  
Query OK, 0 rows affected, 1 warning (0.00 sec)  
mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

3） 重启mysql服务，并构建group replication集群  
\[root@xuegod120 ~\]\# systemctl restart mysqld  
\[root@xuegod120 ~\]\# mysql -uroot -p123456 -A  
mysql> change master to master\_user=‘repl’,master\_password=‘123456’ for channel ‘group\_replication\_reconvery’;  
Query OK, 0 rows affected, 2 warnings (0.01 sec)

4） 安装group replication插件  
mysql> install plugin group\_replication soname ‘group\_replication.so’;  
Query OK, 0 rows affected (0.27 sec)

5） 查看mysql支持的group repliation插件  
mysql> show plugins;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70]

6） 设置group replication引导启动  
设置group\_replication\_bootstrap\_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。  
mysql> set global group\_replication\_bootstrap\_group=on;  
Query OK, 0 rows affected (0.00 sec)

7） 开启group replication  
作为首个MGR集群首个节点  
mysql> start group\_replication;  
Query OK, 0 rows affected (2.26 sec)

8） 关闭group replication引导  
mysql> set global group\_replication\_bootstrap\_group=off;  
Query OK, 0 rows affected (0.00 sec)

9） 查询MGR集群的信息  
我们通过查询表来获取信息，因为my.cnf配置文件里面定义了将信息写入到表中  
mysql> select \* from performance\_schema.replication\_group\_members;  
![在这里插入图片描述][2019061819113073.png]  
注释：状态显示online，表示在线。

10） 创建测试数据库  
mysql> create database test;  
Query OK, 1 row affected (0.00 sec)

mysql> use test;  
Database changed  
mysql> create table t1 (id int primary key,name varchar(20));  
Query OK, 0 rows affected (0.18 sec)

mysql> insert into t1 values (1,‘AA’);  
Query OK, 1 row affected (0.20 sec)

mysql> select \* from t1;  
±—±-----+  
| id | name |  
±—±-----+  
| 1 | AA |  
±—±-----+  
1 row in set (0.00 sec)

11） 查询二进制日志文件  
mysql> show binlog events;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 1]  
2、 xuegod130复制环境配置  
1） 修改my.cnf配置文件  
\#Group Replication  
server\_id = 101 \#注意服务ID不一样  
gtid\_mode = ON  
enforce\_gtid\_consistency = ON  
master\_info\_repository = TABLE  
relay\_log\_info\_repository = TABLE  
binlog\_checksum = NONE  
log\_slave\_updates = ON  
log\_bin = binlog  
binlog\_format= ROW

transaction\_write\_set\_extraction = XXHASH64  
loose-group\_replication\_group\_name = ‘ce9be252-2b71-11e6-b8f4-00212844f856’  
loose-group\_replication\_start\_on\_boot = off  
loose-group\_replication\_local\_address = ‘[xuegod130.com:33062][xuegod130.com_33062]’  
loose-group\_replication\_group\_seeds = ‘[xuegod120.com:33061][xuegod120.com_33061], [xuegod130.com:33062][xuegod130.com_33062], [xuegod140.com:33063][xuegod140.com_33063]’  
loose-group\_replication\_bootstrap\_group = off  
保存重启mysql服务  
\[root@xuegod130 ~\]\# systemctl restart mysqld

2） 创建复制账号，并构建group replication集群  
注意：这里记得关闭二进制日志记录，因为上面重启服务了  
mysql> set sql\_log\_bin=0;  
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repl@‘192.168.2.%’ identified by ‘123456’;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

mysql> set sql\_log\_bin=1;  
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master\_user=‘repl’,master\_password=‘123456’ for channel ‘group\_replication\_recovery’;  
Query OK, 0 rows affected, 2 warnings (0.11 sec)

3） 安装group replication插件  
mysql> install PLUGIN group\_replication SONAME ‘group\_replication.so’;  
Query OK, 0 rows affected (0.27 sec)  
4） 将实例添加到之前的复制组，并开启组复制  
mysql> set global group\_replication\_allow\_local\_disjoint\_gtids\_join=ON;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> start group\_replication;  
Query OK, 0 rows affected, 1 warning (5.90 sec)

5） 查看复制组的状态  
mysql> select \* from performance\_schema.replication\_group\_members;  
![在这里插入图片描述][20190618191207874.png]

mysql> use test  
Database changed  
mysql> select \* from t1;  
±—±-----+  
| id | name |  
±—±-----+  
| 1 | AA |  
±—±-----+  
1 row in set (0.00 sec)

3、 xuegod140复制组的配置  
1） 修改配置文件  
\#Group Replication  
server\_id = 102 \#注意服务id不一样  
gtid\_mode = ON  
enforce\_gtid\_consistency = ON  
master\_info\_repository = TABLE  
relay\_log\_info\_repository = TABLE  
binlog\_checksum = NONE  
log\_slave\_updates = ON  
log\_bin = binlog  
binlog\_format= ROW

transaction\_write\_set\_extraction = XXHASH64  
loose-group\_replication\_group\_name = ‘ce9be252-2b71-11e6-b8f4-00212844f856’  
loose-group\_replication\_start\_on\_boot = off  
loose-group\_replication\_local\_address = ‘[xuegod140.com:33063][xuegod140.com_33063]’  
loose-group\_replication\_group\_seeds =‘[xuegod120.com:33061][xuegod120.com_33061], [xuegod130.com:33062][xuegod130.com_33062], [xuegod140.com:33063][xuegod140.com_33063]’  
loose-group\_replication\_bootstrap\_group = off  
保存重启mysqld服务  
\[root@xuegod140 ~\]\# systemctl restart mysqld

2） 创建复制账号，并构建组复制集群  
\[root@xuegod140 ~\]\# mysql -uroot -p123456 -A  
mysql> set SQL\_LOG\_BIN=0;  
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repl@‘192.168.2.%’ identified by ‘123456’;  
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

mysql> set SQL\_LOG\_BIN=1;  
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master\_user=‘repl’,master\_password=‘123456’ for channel ‘group\_replication\_recovery’;  
Query OK, 0 rows affected, 2 warnings (0.16 sec)

3） 安装组复制集群插件  
mysql> install PLUGIN group\_replication SONAME ‘group\_replication.so’;  
Query OK, 0 rows affected (0.23 sec)

4） 添加实例到复制组，并启动组复制集群  
mysql> set global group\_replication\_allow\_local\_disjoint\_gtids\_join=ON;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> start group\_replication;  
Query OK, 0 rows affected, 1 warning (3.31 sec)

5） 查看组复制集群状态  
mysql> select \* from performance\_schema.replication\_group\_members;  
![在这里插入图片描述][20190618191221351.png]  
到此，单主节点的集群就搭建完毕！

4） 我们可以进行测试  
在xuegod120上插入数据（发现可以插入数据）  
mysql> use test;  
Database changed  
mysql> insert into t1 values (2,‘BB’);  
Query OK, 1 row affected (0.01 sec)

mysql> select \* from t1;  
±—±-----+  
| id | name |  
±—±-----+  
| 1 | AA |  
| 2 | BB |  
±—±-----+  
2 rows in set (0.00 sec)

在xuegod130和140上插入数据（发现报错，无法插入）  
mysql> use test;  
Database changed  
mysql> insert into t1 values (3,‘CC’);  
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannotexecute this statement

总结：MGR集群搭建完毕后，只能有一个写入的服务器，其他都是读取的服务器

总结：当master服务器挂掉后，其他两台slave服务器会通过选举，自动接管作为主服务器，代理员master服务器的业务，进行读写

6、 启动模拟故障的master服务器，查看能否自动加入集群  
1） 查看组复制的状态  
这里发现是offline状态（离线状态）  
mysql> select \* from performance\_schema.replication\_group\_members;  
±--------------------------±----------±------------±------------±-------------+  
| CHANNEL\_NAME | MEMBER\_ID | MEMBER\_HOST | MEMBER\_PORT | MEMBER\_STATE |  
±--------------------------±----------±------------±------------±-------------+  
| group\_replication\_applier | | | NULL | OFFLINE |  
±--------------------------±----------±------------±------------±-------------+  
1 row in set (0.00 sec)

2） 开启组复制集群，再次查看状态  
mysql> start group\_replication;  
Query OK, 0 rows affected (3.24 sec)

mysql> select \* from performance\_schema.replication\_group\_members;  
![在这里插入图片描述][20190618191243467.png]  
注释：这里虽然看到已经加入到组复制集群，但是状态显示recovering

4） 一段时间后，再次查看组复制集群状态，发现变更为ERROR  
![在这里插入图片描述][20190618191248953.png]

5） 查看mysql日志  
![在这里插入图片描述][20190618191253380.png]

6） 故障解决办法  
重新构建组复制集群，重启组复制集群即可  
mysql> change master to master\_user=‘repl’,master\_password=‘123456’ for channel ‘group\_replication\_recovery’;  
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> stop group\_replication;  
Query OK, 0 rows affected (1.00 sec)

mysql> start group\_replication;  
Query OK, 0 rows affected (2.92 sec)

mysql> select \* from performance\_schema.replication\_group\_members;

![在这里插入图片描述][20190618191259364.png]

四、 多主模式（multi-primary模式）  
1、该模式启用需设置两个参数  
group\_replication\_single\_primary\_mode=0 \#这个参数很好理解，就是关闭单master模式  
group\_replication\_enforce\_update\_everywhere\_checks=1 \#这个参数设置多主模式下各个节点严格一致性检查  
2、 默认启动的都是单master模式，其他节点都设置了read\_only、super\_read\_only这两个参数，需要修改这两个配置  
3、 完成上面的配置后就可以执行多点写入了，多点写入会存在冲突检查，这耗损性能挺大的，官方建议采用网络分区功能，在程序端把相同的业务定位到同一节点，尽量减少冲突发生几率。

4、单主模式改多主模式方法  
在原来单主模式的主节点执行操作如下  
我们这里因为刚刚做了故障模拟，所以现在的主节点是xuegod130服务器  
1） 在单主模式执行如下操作  
mysql> stop group\_replication;  
Query OK, 0 rows affected (9.30 sec)

mysql> set global group\_replication\_single\_primary\_mode=off;  
Query OK, 0 rows affected (0.00 sec)

mysql> set global group\_replication\_enforce\_update\_everywhere\_checks=ON;  
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL group\_replication\_bootstrap\_group=ON;  
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP\_REPLICATION;  
Query OK, 0 rows affected, 1 warning (2.02 sec)

mysql> SET GLOBAL group\_replication\_bootstrap\_group=OFF;  
Query OK, 0 rows affected (0.00 sec)

2） 在其他节点执行如下操作  
xuegod120上  
mysql> stop GROUP\_REPLICATION;  
Query OK, 0 rows affected (9.33 sec)

mysql> set global group\_replication\_allow\_local\_disjoint\_gtids\_join=ON;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set global group\_replication\_single\_primary\_mode=off;  
Query OK, 0 rows affected (0.00 sec)

mysql> set global group\_replication\_enforce\_update\_everywhere\_checks=ON;  
Query OK, 0 rows affected (0.00 sec)

mysql> start group\_replication;  
Query OK, 0 rows affected, 1 warning (5.77 sec)

xuegod140上  
mysql> stop GROUP\_REPLICATION;  
Query OK, 0 rows affected (9.33 sec)

mysql> set global group\_replication\_allow\_local\_disjoint\_gtids\_join=ON;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set global group\_replication\_single\_primary\_mode=off;  
Query OK, 0 rows affected (0.00 sec)

mysql> set global group\_replication\_enforce\_update\_everywhere\_checks=ON;  
Query OK, 0 rows affected (0.00 sec)

mysql> start group\_replication;  
Query OK, 0 rows affected, 1 warning (5.77 sec)

3） 查看各个节点的组复制集群状态  
都是正常在线状态  
![在这里插入图片描述][20190618191311873.png]

5） 在各个节点上，插入数据，进行测试  
xuegod120：  
mysql> use test  
Database changed  
mysql> insert into t1 values (3,‘CC’);  
Query OK, 1 row affected (0.20 sec)

mysql> select \* from t1;  
±—±-----+  
| id | name |  
±—±-----+  
| 1 | AA |  
| 2 | BB |  
| 3 | CC |  
±—±-----+  
3 rows in set (0.00 sec)

xuegod130：  
mysql> use test  
Database changed  
mysql> insert into t1 values (4,‘DD’);  
Query OK, 1 row affected (0.01 sec)

mysql> select \* from t1;  
±—±-----+  
| id | name |  
±—±-----+  
| 1 | AA |  
| 2 | BB |  
| 3 | CC | \#复制也OK  
| 4 | DD |  
±—±-----+  
4 rows in set (0.00 sec)

xuegod140：  
mysql> use test;  
Database changed  
mysql> insert into t1 values (5,‘EE’);  
Query OK, 1 row affected (0.01 sec)

mysql> select \* from t1;  
±—±-----+  
| id | name |  
±—±-----+  
| 1 | AA |  
| 2 | BB |  
| 3 | CC |  
| 4 | DD |  
| 5 | EE |  
±—±-----+  
5 rows in set (0.00 sec)

[20190618191026336.png]: /images/20220112/dac35434b9d14a978f2c80bb9d35eb9f.png
[xuegod120.com]: http://xuegod120.com
[xuegod130.com]: http://xuegod130.com
[xuegod140.com]: http://xuegod140.com
[xuegod120.com_33061]: http://xuegod120.com:33061
[xuegod130.com_33062]: http://xuegod130.com:33062
[xuegod140.com_33063]: http://xuegod140.com:33063
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70]: /images/20220112/2d0ab2ebc881434a8545041028c0004a.png
[2019061819113073.png]: /images/20220112/8a2d4e3627284166b89663d66a206c3d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTg0MzY5OQ_size_16_color_FFFFFF_t_70 1]: /images/20220112/68690ddf2d2441ed93896b65bcd98755.png
[20190618191207874.png]: /images/20220112/55da01ada10c41b5a979c16547c23be0.png
[20190618191221351.png]: /images/20220112/966a36dfb8624b3cbd0162bf4d9b2b7f.png
[20190618191231265.png]: /images/20220112/beeee03c001f42a7a2edeb4c20464a15.png
[20190618191243467.png]: /images/20220112/0550e4e0039940ad8d07fc36338cbe18.png
[20190618191248953.png]: /images/20220112/9d4c3554dd1b4dd3b8e7802a6edae376.png
[20190618191253380.png]: /images/20220112/628ae8a976b04964a35c8605243af3db.png
[20190618191259364.png]: /images/20220112/a8f73d08aea84360aab542fa6cc3595c.png
[20190618191311873.png]: /images/20220112/fa7943edd73f4b1e938e58f385486369.png