MySQL 高可用架构： MGR 搭建手册 -- 中国DBA社区

在上文我们了解了MGR 高可用架构的相关背景，如下：

MySQL 高可用架构： MGR 说明
https://www.cndba.cn/dave/article/108027

本篇我们来搭建一个三节点的MGR环境，并进行相关的测试。

1 安装MySQL 软件

MySQL 软件的安装参考之前的博客：

Redhat 7.7 平台 MySQL 5.7.33 安装手册（Tar包）
https://www.cndba.cn/dave/article/4509

这里要注意各个mysql节点的主机名不一致，并且在每个节点的/etc/hosts里面做主机名绑定，否则后续将节点加入group组会失败，报错RECOVERING。我们这里复用3台mongodb的测试环境进行测试。

[mysql@www.cndba.cn_1 ~]$ cat /etc/hosts
127.0.0.1   localhost
172.31.185.120 mongodb1
172.31.185.165 mongodb2
172.31.185.131 mongodb3
[mysql@www.cndba.cn_1 ~]$

2 配置MGR

2.1 创建配置文件

在三个节点都创建目录，我们这里使用3406 端口：

[mysql@www.cndba.cn_2 ~]$ mkdir -p /data/mysql/3406/{data,log}

分别给三个节点创建my.cnf 文件。我们这里将文件放到/data/mysql/3406/ 目录下。注意MGR的端口和DB 端口不同，一个是3406，一个是34061.

[mysql@www.cndba.cn_1 3406]$ cat my3406.cnf
[client]
default-character-set=utf8mb4

[mysqld]
user=mysql
datadir=/data/mysql/3406/data
basedir=/usr/local/mysql
port=3406
socket=/data/mysql/3406/mysql.sock
pid-file=/data/mysql/3406/mysql3406.pid
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
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
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="6789b6d7-de75-11ec-a59e-fa163e7035c7"
group_replication_start_on_boot=off
group_replication_local_address= "172.31.185.120:34061"
group_replication_group_seeds= "172.31.185.120:34061,172.31.185.165:34061,172.31.185.131:34061"
group_replication_bootstrap_group=off
log-error=/data/mysql/3406/log/error.log
[mysql@www.cndba.cn_1 3406]$


[mysql@www.cndba.cn_1 ~]$

[mysql@www.cndba.cn_2 3406]$ cat my3406.cnf
[client]
default-character-set=utf8mb4

[mysqld]
bind-address=0.0.0.0
user=mysql
datadir=/data/mysql/3406/data
basedir=/data/mysql/3406
port=3406
socket=/data/mysql/3406/mysql.sock
pid-file=/data/mysql/3406/mysql3406.pid
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
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
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="6789b6d7-de75-11ec-a59e-fa163e7035c7"
group_replication_start_on_boot=off
group_replication_local_address= "172.31.185.165:34061"
group_replication_group_seeds= "172.31.185.120:34061,172.31.185.165:34061,172.31.185.131:34061"
group_replication_bootstrap_group=off
log-error=/data/mysql/3406/log/error.log

[mysql@www.cndba.cn_2 3406]$

[mysql@www.cndba.cn_3 3406]$ cat my3406.cnf
[client]
default-character-set=utf8mb4

[mysqld]
bind-address=0.0.0.0
user=mysql
datadir=/data/mysql/3406/data
basedir=/data/mysql/3406
port=3406
socket=/data/mysql/3406/mysql.sock
pid-file=/data/mysql/3406/mysql3406.pid
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
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
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="6789b6d7-de75-11ec-a59e-fa163e7035c7"
group_replication_start_on_boot=off
group_replication_local_address= "172.31.185.131:34061"
group_replication_group_seeds= "172.31.185.120:34061,172.31.185.165:34061,172.31.185.131:34061"
group_replication_bootstrap_group=off
log-error=/data/mysql/3406/log/error.log

[mysql@www.cndba.cn_3 3406]$

3个节点除了server_id、loose-group_replication_local_address参数不一样外，其他保持一致。

group_replication_group_name 可以通过UUID 生成：

mysql> SELECT UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 6789b6d7-de75-11ec-a59e-fa163e7035c7 |
+--------------------------------------+
1 row in set (0.02 sec)

mysql>

详细的参数解释可以参考官方手册：

https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html#group-replication-configure-plugin

2.2 初始化数据库并且启动

分别在3个节点进行数据库初始化并启动。这里只记录节点1的操作。

[mysql@www.cndba.cn_1 ~]$ mysqld --initialize --datadir=/data/mysql/3406/data 
[mysql@www.cndba.cn_1 ~]$ mysqld_safe --defaults-file=/data/mysql/3406/my3406.cnf  &

这里有个小插曲：

MySQL 初始化没回日志返回异常处理
https://www.cndba.cn/dave/article/108029

2.3 登录数据库，加载MGR插件，创建同步账号

这一步在所有节点上执行。

先连上数据库，把root用户密码改了：

[mysql@www.cndba.cn_1 data]$ mysql -uroot -p -S /data/mysql/3406/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 2
Server version: 5.7.33-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql>
mysql>  alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.01 sec)

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

安装MGR插件，如果my.cnf 参数中配置了plugin_load_add则可以忽略这一步：

mysql>INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

设置复制账号

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

3 单主模式（主库写，从库只读）

3.1 启动MGR

这里将mongodb1 节点作为主节点。现在该节点启动MGR。

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 (2.05 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1.novalocal |        3406 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
1 row in set (0.00 sec)

mysql>

其他节点加入MGR，在从库（mongodb2,mongodb3）上执行

mysql> START GROUP_REPLICATION;



mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2           |        3406 | RECOVERING   |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3           |        3406 | RECOVERING   |
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1.novalocal |        3406 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
3 rows in set (0.05 sec)

mysql> select * from performance_schema.replication_connection_status;
+----------------------------+--------------------------------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+----------------------------------------------------------------------------------------+----------------------+
| CHANNEL_NAME               | GROUP_NAME                           | SOURCE_UUID                          | THREAD_ID | SERVICE_STATE | COUNT_RECEIVED_HEARTBEATS | LAST_HEARTBEAT_TIMESTAMP | RECEIVED_TRANSACTION_SET | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE                                                                     | LAST_ERROR_TIMESTAMP |
+----------------------------+--------------------------------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+----------------------------------------------------------------------------------------+----------------------+
| group_replication_recovery |                                      |                                      |      NULL | OFF           |                         0 | 0000-00-00 00:00:00      |                          |              2005 | error connecting to master 'repl@www.cndba.cn_1.novalocal:3406' - retry-time: 60  retries: 1 | 2022-05-28 23:26:12  |
| group_replication_applier  | 6789b6d7-de75-11ec-a59e-fa163e7035c7 | 6789b6d7-de75-11ec-a59e-fa163e7035c7 |      NULL | ON            |                         0 | 0000-00-00 00:00:00      |                          |                 0 |                                                                                        | 0000-00-00 00:00:00  |
+----------------------------+--------------------------------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+----------------------------------------------------------------------------------------+----------------------+
2 rows in set (0.03 sec)

mysql>

虽然我前面配置过了/etc/hosts文件，但是这里主库的主机还是不对：mongodb1.novalocal

所以又查看了一下主机名，果然不对，其他2个节点是正常的:

[root@www.cndba.cn_1 ~]# hostname
mongodb1.novalocal

[mysql@www.cndba.cn_3 log]$ hostname
mongodb3
[mysql@www.cndba.cn_3 log]$

修改主机名：

[root@www.cndba.cn_1 ~]# hostnamectl --static set-hostname mongodb1
[root@www.cndba.cn_1 ~]# hostname mongodb1
[root@www.cndba.cn_1 ~]# hostname
mongodb1
[root@www.cndba.cn_1 ~]#

重启所有DB后，在启动MGR，恢复正常：

mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2    |        3406 | ONLINE       |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3    |        3406 | ONLINE       |
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1    |        3406 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

可以看到，3个节点状态为online，MGR单主模式搭建成功。

3.2 验证

在MGR 单主模式中，只有主节点可以写入，其他节点只读。

3.2.1 验证主从库模式

主库mongodb1：

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

mysql>

从库：mongodb2，mongodb3：

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

mysql>

3.2.2 验证数据同步

主库：

mysql> create database cndba;
Query OK, 1 row affected (0.01 sec)

mysql> use cndba;
Database changed
mysql>  create table cndba(id int,url varchar(50));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into cndba value(1,"http://www.cndba.cn/dave");
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
mysql> alter table cndba add primary key(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into cndba value(1,"http://www.cndba.cn/dave");
Query OK, 1 row affected (0.01 sec)

mysql>

从库mongodb2查询：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cndba              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use cndba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from cndba;
+----+--------------------------+
| id | url                      |
+----+--------------------------+
|  1 | http://www.cndba.cn/dave |
+----+--------------------------+
1 row in set (0.00 sec)

mysql>

从库mongodb3查询：

mysql> use cndba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from cndba;
+----+--------------------------+
| id | url                      |
+----+--------------------------+
|  1 | http://www.cndba.cn/dave |
+----+--------------------------+
1 row in set (0.00 sec)

mysql>

3.2.3 验证节点故障转移

我们这里将主库节点关闭。然后查看剩下的2个从节点的状态。

主库：

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

从库mongodb2：
mysql>  show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

很明显，这里已经从只读变成了读写。

在日志里也记录了这个过程：

2022-05-28T15:45:26.336251Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
2022-05-28T15:45:31.739358Z 0 [Note] Plugin group_replication reported: 'Members joined the group: mongodb3:3406'
2022-05-28T15:45:31.739519Z 0 [Note] Plugin group_replication reported: 'Group membership changed to mongodb2:3406, mongodb3:3406, mongodb1:3406 on view 16537520712021032:3.'
2022-05-28T15:45:31.820110Z 18 [Note] Start binlog_dump to master_thread_id(18) slave_server(3), pos(, 4)
2022-05-28T15:45:31.983150Z 0 [Note] Plugin group_replication reported: 'The member with address mongodb3:3406 was declared online within the replication group'
2022-05-28T15:46:31.820398Z 18 [Note]
2022-05-28T15:57:20.399249Z 4 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection'
2022-05-28T16:09:10.317212Z 0 [Warning] Plugin group_replication reported: 'Members removed from the group: mongodb1:3406'
2022-05-28T16:09:10.317259Z 0 [Note] Plugin group_replication reported: 'Primary server with address mongodb1:3406 left the group. Electing new Primary.'
2022-05-28T16:09:10.317352Z 0 [Note] Plugin group_replication reported: 'A new primary with address mongodb2:3406 was elected, enabling conflict detection until the new primary applies all relay logs.'
2022-05-28T16:09:10.317414Z 21 [Note] Plugin group_replication reported: 'This server is working as primary member.'
2022-05-28T16:09:10.317443Z 0 [Note] Plugin group_replication reported: 'Group membership changed to mongodb2:3406, mongodb3:3406 on view 16537520712021032:4.'
[mysql@www.cndba.cn_2 log]$

从库mongodb3，依旧是只读状态：

mysql>  show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.03 sec)

4 多主模式部署

多主模式和单主部署方式差不多，只在加入集群时多执行：

set global group_replication_single_primary_mode=off;

我们因为这里已经部署了单主模式，所以这里直接从单主切换到多主模式。

MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。

停止组复制(所有节点执行)：

mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;

随便选择某个节点执行

mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
mysql> START GROUP_REPLICATION; 
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

其他节点执行

mysql> START GROUP_REPLICATION;

查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2    |        3406 | ONLINE       |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3    |        3406 | ONLINE       |
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1    |        3406 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
可以看到所有节点状态都是online，角色都是PRIMARY，MGR多主模式搭建成功。

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

5 多主模式切换回单主模式

所有节点执行

mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;

主节点（mongodb1）执行

SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;

从节点（mongodb2，mongodb3）执行

START GROUP_REPLICATION;

查看MGR组信息

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2    |        3406 | ONLINE       |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3    |        3406 | ONLINE       |
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1    |        3406 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

查看当前主节点
mysql> show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | eadc7c4e-de90-11ec-a79a-fa163e7035c7 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

6 小结

本篇我们演示的是MGR 单主模式和多主模式的搭建。通过测试，我们也能看著来，MGR 实际上只是一种增强的同步模式。

MGR的可靠性 > 半同步复制 > 主从复制

但是，MGR的不足也很明显，虽然可以完成主从库的切换，但没有VIP，所以当发生切换时，就会对业务产生影响。所以一般还需要结合ProxySQL 等中间件来组合使用，才能发挥最佳的效果。这个我们下篇再说。

签到成功

CNDBA社区