【MySQL】—基于Docker安装部署MySQL高可用之MGR集群
架构原理
MGR(MySQL Group Replication)是MySQL官方在MySQL 5.7.17版本中以插件形式推出的主从复制高可用技术，它基于原生的主从复制，将各节点归入到一个组中，通过组内节点的通信协商(组通信协议基于Paxos算法)，实现数据的强一致性、故障探测、冲突检测、节点加组、节点离组等等功能。
image.png
这3个节点互相通信，每当有事件发生，都会向其他节点传播该事件，然后协商，如果大多数节点都同意这次的事件，那么该事件将通过，否则该事件将失败或回滚。
这些节点可以是单主模型的(single-primary)，也可以是多主模型的(multi-primary)。单主模型只有一个主节点可以接受写操作，主节点故障时可以自动选举主节点。多主模型下，所有节点都可以接受写操作，所以没有master-slave的概念。
优点：
强一致性、高容错性、高扩展性、高灵活性
局限：
仅支持InnoDB表且每张表一定要有一个主键(用于做写入集的冲突检测)、必须打开GTID特性、日志格式为ROW、不支持大事务（大小最好不超过143MB）、一个MGP集群最多支持9个节点(节点过多性能负载大)、不支持外键约束、二进制日志不支持Binlog Event Checksum
局限性总结：
1、不适合大事务场景。
2、要求网络环境好，如果异地，最好使用专线。
3、最多支持9个节点，节点过多性能负载大。
4、不支持外键约束。
5、每张表一定要有一个主键，用于做写入集的冲突检测。
适用场景：
金融交易、重要数据存储、对主从一致性要求高的场景
核心数据总量未过亿
读多写少的应用场景，如互联网电商
一、环境准备
1、查看宿主机的环境
操作系统
[root@kubernetes ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
Docker版本：
[root@kubernetes ~]# docker --version
Docker version 20.10.24, build 297e128
root@mysql-mgr-master:/# mysql -V
mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)
2、准备环境
拉取镜像
[root@kubernetes ~]# docker pull mysql:8.0.20
创建容器网络
docker network create --subnet=172.72.0.0/24 mysql-network
创建数据目录，mysql的数据目录。
mkdir -p /data/mysql-mgr/mysql-mgr-master/conf.d
mkdir -p /data/mysql-mgr/mysql-mgr-master/data
mkdir -p /data/mysql-mgr/mysql-mgr-slave01/conf.d
mkdir -p /data/mysql-mgr/mysql-mgr-slave01/data
mkdir -p /data/mysql-mgr/mysql-mgr-slave02/conf.d
mkdir -p /data/mysql-mgr/mysql-mgr-slave02/data
拉起容器
docker run -d --name mysql-mgr-master -h mysql-mgr-master \
-p 13066:3306 -p 33011:33011 \
--net=mysql-network --ip 172.72.0.21 \
-v /data/mysql-mgr/mysql-mgr-master/conf.d:/etc/mysql/conf.d -v /data/mysql-mgr/mysql-mgr-master/data:/var/lib/mysql/ \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai \
--privileged=true mysql:8.0.20
docker run -d --name mysql-mgr-slave01 -h mysql-mgr-slave01 \
-p 23066:3306 -p 33012:33012 \
--net=mysql-network --ip 172.72.0.22 \
-v /data/mysql-mgr/mysql-mgr-slave01/conf.d:/etc/mysql/conf.d -v /data/mysql-mgr/mysql-mgr-slave01/data:/var/lib/mysql/ \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai \
--privileged=true mysql:8.0.20
docker run -d --name mysql-mgr-slave02 -h mysql-mgr-slave02 \
-p 33066:3306 -p 33013:33013 \
--net=mysql-network --ip 172.72.0.23 \
-v /data/mysql-mgr/mysql-mgr-slave02/conf.d:/etc/mysql/conf.d -v /data/mysql-mgr/mysql-mgr-slave02/data:/var/lib/mysql/ \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai \
--privileged=true mysql:8.0.20
查看集群容器
[root@kubernetes ~]# docker ps -a|grep mysql-mgr
cfd43a333add mysql:8.0.20 "docker-entrypoint.s…" 25 seconds ago Up 24 seconds 0.0.0.0:33013->33013/tcp, :::33013->33013/tcp, 33060/tcp, 0.0.0.0:33066->3306/tcp, :::33066->3306/tcp mysql-mgr-slave02
18c94b5202b7 mysql:8.0.20 "docker-entrypoint.s…" 31 seconds ago Up 31 seconds 0.0.0.0:33012->33012/tcp, :::33012->33012/tcp, 33060/tcp, 0.0.0.0:23066->3306/tcp, :::23066->3306/tcp mysql-mgr-slave01
e0bbcd47f46f mysql:8.0.20 "docker-entrypoint.s…" 2 minutes ago Up 2 minutes 0.0.0.0:33011->33011/tcp, :::33011->33011/tcp, 33060/tcp, 0.0.0.0:13066->3306/tcp, :::13066->3306/tcp mysql-mgr-master
[root@kubernetes ~]#
3、修改配置mysql参数配置文件
rm -rf /data/mysql-mgr/mysql-mgr-master/conf.d/my.cnf
rm -rf /data/mysql-mgr/mysql-mgr-slave01/conf.d/my.cnf
rm -rf /data/mysql-mgr/mysql-mgr-slave02/conf.d/my.cnf
mysql-mgr集群master配置文件
cat > /data/mysql-mgr/mysql-mgr-master/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033060
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin =
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
skip-name-resolve
auto-increment-increment=2
auto-increment-offset=1
gtid-mode=ON
enforce-gtid-consistency=on
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mysql-mgr-master-relay-bin-ip21
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.21:33011"
loose-group_replication_group_seeds= "172.72.0.21:33011,172.72.0.22:33012,172.72.0.23:33013"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.21,172.72.0.22,172.72.0.23"
report_host=172.72.0.21
report_port=3306
EOF
mysql-mgr集群slave01配置文件
cat > /data/mysql-mgr/mysql-mgr-slave01/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033061
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin =
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mysql-mgr-slave-relay-bin-ip22
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.22:33012"
loose-group_replication_group_seeds= "172.72.0.21:33011,172.72.0.22:33012,172.72.0.23:33013"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.21,172.72.0.22,172.72.0.23"
report_host=172.72.0.22
report_port=3306
EOF
mysql-mgr集群slave02配置文件
cat > /data/mysql-mgr/mysql-mgr-slave02/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033062
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin =
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mysql-mgr-slave-relay-bin-ip23
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.23:33013"
loose-group_replication_group_seeds= "172.72.0.21:33011,172.72.0.22:33012,172.72.0.23:33013"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.21,172.72.0.22,172.72.0.23"
report_host=172.72.0.23
report_port=3306
EOF
mysql的配置文件解析
[client]
# 默认字符集
default-character-set=utf8mb4
[mysqld]
# 字符集
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default_authentication_plugin=mysql_native_password
# 最大连接数
max_connections=4096
# 日志到期自动删除
binlog_expire_logs_seconds=604800
# 对于组复制，数据必须存储在 InnoDB 事务性存储引擎，因此禁用以下引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
# server-id必须是唯一的
server-id=11
# 开启GTID,必须开启
gtid_mode=on
# 强制GTID的一致性
enforce_gtid_consistency=on
# binlog校验规则,8.0.21以后不需要设置
binlog_checksum=NONE
# binlog格式,MGR要求必须是ROW
binlog_format=row
# 生成各 Binlog 文件的前缀
log_bin=mysql-bin
# 因为集群会在故障恢复时互相检查binlog的数据, 所以需要记录下集群内其他服务器发过来已经执行过的binlog,按GTID来区分是否执行过.
log_slave_updates=on
# 基于安全的考虑,MGR集群要求复制模式要改成slave记录记录到表中,不然就报错
master_info_repository=TABLE
# 基于安全的考虑,MGR集群要求复制模式要改成slave记录记录到表中,不然就报错
relay_log_info_repository=TABLE
# 记录事务的算法,官网建议设置该参数使用 XXHASH64 算法
transaction_write_set_extraction=XXHASH64
# MGR使用乐观锁,所以官网建议隔离级别是RC,减少锁粒度
transaction_isolation=READ-COMMITTED
# 预装插件
plugin_load_add='group_replication.so'
# 相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,
# 主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID
loose-group_replication_group_name='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
# 是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况
loose-group_replication_start_on_boot=off
# 本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address='172.19.0.11:33011'
# 需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
loose-group_replication_group_seeds='172.19.0.11:33011,172.19.0.12:33012,172.19.0.13:33013'
# 开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启,
loose-group_replication_bootstrap_group=OFF
# 是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
loose-group_replication_single_primary_mode=ON
# 多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭
loose-group_replication_enforce_update_everywhere_checks=OFF
report_host=172.19.0.11
report_port=3306
4、做好mysql的配置文件后，重启容器。
[root@kubernetes ~]# docker restart `docker ps -a |grep mysql-mgr |awk -F " " '{print $1}'`
cfd43a333add
18c94b5202b7
e0bbcd47f46f
[root@kubernetes ~]#
5、进入容器内部
[root@kubernetes ~]# docker exec -it mysql-mgr-master bash
[root@kubernetes ~]# docker exec -it mysql-mgr-slave01 bash
[root@kubernetes ~]# docker exec -it mysql-mgr-slave02 bash
6、查看容器日志
[root@kubernetes ~]# docker logs -f --tail 10 mysql-mgr-master
[root@kubernetes ~]# docker logs -f --tail 10 mysql-mgr-slave01
[root@kubernetes ~]# docker logs -f --tail 10 mysql-mgr-slave02
7、查看MySQL的主机名、server_id和server_uuid
连接登陆mysql
root@mysql-mgr-master:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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.
查看hostname，server_id和server_uuid
mysql>
mysql> select @@hostname,@@server_id,@@server_uuid;
+------------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid |
+------------------+-------------+--------------------------------------+
| mysql-mgr-master | 802033060 | 348ecc41-194c-11ee-9c6e-0242ac480014 |
+------------------+-------------+--------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select @@hostname,@@server_id,@@server_uuid;
+-------------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid |
+-------------------+-------------+--------------------------------------+
| mysql-mgr-slave01 | 802033061 | 7bb32a93-194c-11ee-9c2e-0242ac480016 |
+-------------------+-------------+--------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select @@hostname,@@server_id,@@server_uuid;
+-------------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid |
+-------------------+-------------+--------------------------------------+
| mysql-mgr-slave02 | 802033062 | e1aaa1ff-196b-11ee-9b44-0242ac480017 |
+-------------------+-------------+--------------------------------------+
1 row in set (0.00 sec)
mysql>
二、部署MGR集群
单主多从MGR集群
1、安装MGR插件（所有节点执行）
安装插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> show plugins;
执行过程：
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
查看已安装的数据库插件
mysql> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)
mysql>
2、设置复制账号（所有节点执行）
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
执行过程：
mysql>
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER repl@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (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.03 sec)
mysql>
3、启动MGR单主模式
启动MGR，在主库上执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
-- 查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;
执行过程：
-- 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | | | NULL | OFFLINE | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql>
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 (3.33 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 | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | ONLINE | PRIMARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)
mysql>
4、其他节点加入MGR，在从库上执行
START GROUP_REPLICATION;
-- 查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;
执行过程：
从节点01：
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.34 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 7bb32a93-194c-11ee-9c2e-0242ac480016 | 172.72.0.22 | 3306 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
mysql>
从节点02：
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.47 sec)
mysql> -- MGR
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 7bb32a93-194c-11ee-9c2e-0242ac480016 | 172.72.0.22 | 3306 | ONLINE | SECONDARY | 8.0.20 |
| group_replication_applier | e1aaa1ff-196b-11ee-9b44-0242ac480017 | 172.72.0.23 | 3306 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
mysql>
可以看到，3个节点状态为online，并且主节点为172.72.0.21，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功。
三、多主和单主模式切换-自动在线切换
1、查询当前模式
mysql> show variables like '%group_replication_single_primary_mode%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON |
+---------------------------------------+-------+
1 row in set (0.00 sec)
mysql> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql>
参数group_replication_single_primary_mode为ON，表示单主模式。
2、函数实现多主和单主切换
函数切换：从MySQL 8.0.13开始，可以使用函数进行在线修改MGR模式。
-- 单主切多主
select group_replication_switch_to_multi_primary_mode();
-- 多主切单主，入参需要传入主库的server_uuid
select group_replication_switch_to_single_primary_mode('@@server_uuid') ;
-- 查看组信息
SELECT * FROM performance_schema.replication_group_members;
单主切多主模式
mysql>
mysql> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.00 sec)
查看为单主
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 7bb32a93-194c-11ee-9c2e-0242ac480016 | 172.72.0.22 | 3306 | ONLINE | SECONDARY | 8.0.20 |
| group_replication_applier | e1aaa1ff-196b-11ee-9b44-0242ac480017 | 172.72.0.23 | 3306 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)
单主切换为多主
mysql> select group_replication_switch_to_multi_primary_mode();
+--------------------------------------------------+
| group_replication_switch_to_multi_primary_mode() |
+--------------------------------------------------+
| Mode switched to multi-primary successfully. |
+--------------------------------------------------+
1 row in set (1.01 sec)
切换后，查看为多主状态
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 7bb32a93-194c-11ee-9c2e-0242ac480016 | 172.72.0.22 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | e1aaa1ff-196b-11ee-9b44-0242ac480017 | 172.72.0.23 | 3306 | ONLINE | PRIMARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql>
多主切单主模式
mysql> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (0.00 sec)
注意：切换为单主时，需要指定那个为单主，指定主的MEMBER_ID 。
mysql> select group_replication_switch_to_single_primary_mode('7bb32a93-194c-11ee-9c2e-0242ac480016') ;
+-----------------------------------------------------------------------------------------+
| group_replication_switch_to_single_primary_mode('7bb32a93-194c-11ee-9c2e-0242ac480016') |
+-----------------------------------------------------------------------------------------+
| Mode switched to single-primary successfully. |
+-----------------------------------------------------------------------------------------+
1 row in set (1.01 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | ONLINE | SECONDARY | 8.0.20 |
| group_replication_applier | 7bb32a93-194c-11ee-9c2e-0242ac480016 | 172.72.0.22 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | e1aaa1ff-196b-11ee-9b44-0242ac480017 | 172.72.0.23 | 3306 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql>
3、手动切换方式
MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。
单主切多主模式
1、停止组复制(所有节点执行)：
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
2、随便选择某个节点执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
3、其他节点执行
START GROUP_REPLICATION;
4、查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | ONLINE| PRIMARY | 8.0.20 |
| group_replication_applier | 7bb32a93-194c-11ee-9c2e-0242ac480016 | 172.72.0.22 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | e1aaa1ff-196b-11ee-9b44-0242ac480017 | 172.72.0.23 | 3306 | ONLINE| PRIMARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
mysql>
可以看到所有节点状态都是online，角色都是PRIMARY，MGR多主模式切换成功。
多主切单主模式
1、所有节点执行
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;
执行过程：
mysql> stop group_replication;
Query OK, 0 rows affected (5.78 sec)
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> set global group_replication_single_primary_mode=ON;
Query OK, 0 rows affected (0.00 sec)
mysql>
2、主节点执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
主节点执行：
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 (3.16 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql>
3、从节点执行
START GROUP_REPLICATION;
执行过程：
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.90 sec)
4、查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;
执行过程：
mysql>
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 7bb32a93-194c-11ee-9c2e-0242ac480016 | 172.72.0.22 | 3306 | ONLINE | SECONDARY | 8.0.20 |
| group_replication_applier | e1aaa1ff-196b-11ee-9b44-0242ac480017 | 172.72.0.23 | 3306 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
mysql>
四、测试同步
在主节点上执行以下命令，然后在其它节点查询：
创建数据和表：
create database lhrdb;
CREATE TABLE lhrdb.`tb1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hostname` varchar(100) DEFAULT NULL,
`server_id` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
插入数据：
insert into lhrdb.tb1(hostname,server_id) select @@hostname,@@server_id;
select * from lhrdb.tb1;
查看数据：
-- 3个节点查询出来的值一样
mysql> select * from lhrdb.tb1;
+----+------------------+-----------+
| id | hostname | server_id |
+----+------------------+-----------+
| 1 | mysql-mgr-master | 802033060 |
+----+------------------+-----------+
1 row in set (0.00 sec)
五、添加新节点
1、创建新MySQL节点
创建存储目录
mkdir -p /data/mysql-mgr/mysql-mgr-slave03/conf.d
mkdir -p /data/mysql-mgr/mysql-mgr-slave03/data
启动容器：
docker run -d --name mysql-mgr-slave03 -h mysql-mgr-slave03 \
-p 43066:3306 -p 33014:33014 \
--net=mysql-network --ip 172.72.0.24 \
-v /data/mysql-mgr/mysql-mgr-slave03/conf.d:/etc/mysql/conf.d -v /data/mysql-mgr/mysql-mgr-slave03/data:/var/lib/mysql/ \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai \
--privileged=true mysql:8.0.20
查看容器
[root@kubernetes mysql-mgr-slave02]# docker ps -a|grep mysql-mgr-slave03
26973dbbc2b4 mysql:8.0.20 "docker-entrypoint.s…" 42 seconds ago Up 41 seconds 0.0.0.0:33014->33014/tcp, :::33014->33014/tcp, 33060/tcp, 0.0.0.0:43066->3306/tcp, :::43066->3306/tcp mysql-mgr-slave03
[root@kubernetes mysql-mgr-slave02]#
集群的配置文件如下：
mysql-mgr集群slave03配置文件
cat > /data/mysql-mgr/mysql-mgr-slave03/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033063
default-time-zone = '+8:00'
log_timestamps = SYSTEM
log-bin =
binlog_format=row
binlog_checksum=NONE
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
default_authentication_plugin=mysql_native_password
max_allowed_packet = 500M
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=mysql-mgr-slave-relay-bin-ip24
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "172.72.0.24:33014"
loose-group_replication_group_seeds= "172.72.0.21:33011,172.72.0.22:33012,172.72.0.23:33013,172.72.0.24:33014"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.21,172.72.0.22,172.72.0.23,172.72.0.24"
report_host=172.72.0.24
report_port=3306
EOF
重启容器
[root@kubernetes mysql-mgr-slave02]# docker restart mysql-mgr-slave03
进入容器内部
[root@kubernetes ~]# docker exec -it mysql-mgr-slave03 bash
root@mysql-mgr-slave03:/#
查看mysql的hostname，server_id和server_uuid。
mysql> select @@hostname,@@server_id,@@server_uuid;
+-------------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid |
+-------------------+-------------+--------------------------------------+
| mysql-mgr-slave03 | 802033063 | 32905643-197f-11ee-a60e-0242ac480018 |
+-------------------+-------------+--------------------------------------+
1 row in set (0.00 sec)
mysql>
查看容器的日志
docker logs -f --tail 10 mysql-mgr-slave03
2、新节点安装MGR插件
-- 安装MGR插件（新增节点执行）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;
3、新节点设置复制账号
-- 设置复制账号（新增节点执行）
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'lhr';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';
执行过程：
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER repl@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (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.08 sec)
4、在原3节点执行修改参数
set global group_replication_group_seeds== "172.72.0.21:33011,172.72.0.22:33012,172.72.0.23:33013,172.72.0.24:33014"
set global group_replication_group_seeds=="172.72.0.21,172.72.0.22,172.72.0.23,172.72.0.24"
注意：设置后，执行如下操作后，该节点会退出集群，并且无法加入，需要重启节点。
stop group_replication;
start group_replication;
5、查看master的状态
mysql> show master status;
+------------------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| mysql-mgr-slave01-bin.000006 | 4944 | | | 348ecc41-194c-11ee-9c6e-0242ac480014:1-3,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-25 |
+------------------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> show binlog events in 'mysql-mgr-slave01-bin.000006' limit 5;
+------------------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
| mysql-mgr-slave01-bin.000006 | 4 | Format_desc | 802033061 | 125 | Server ver: 8.0.20, Binlog ver: 4 |
| mysql-mgr-slave01-bin.000006 | 125 | Previous_gtids | 802033061 | 232 | 348ecc41-194c-11ee-9c6e-0242ac480014:1-3,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-13 |
| mysql-mgr-slave01-bin.000006 | 232 | Gtid | 802033060 | 314 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:14' |
| mysql-mgr-slave01-bin.000006 | 314 | Query | 802033060 | 381 | BEGIN |
| mysql-mgr-slave01-bin.000006 | 381 | View_change | 802033060 | 560 | view_id=16883758001737077:1 |
+------------------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
6、新节点加入
- 4个节点需要保证以下2个参数的值一致
mysql> select @@group_replication_enforce_update_everywhere_checks,@@group_replication_single_primary_mode;
+------------------------------------------------------+-----------------------------------------+
| @@group_replication_enforce_update_everywhere_checks | @@group_replication_single_primary_mode |
+------------------------------------------------------+-----------------------------------------+
| 0 | 1 |
+------------------------------------------------------+-----------------------------------------+
1 row in set (0.00 sec)
mysql>
-- 如果不一致，那么需要修改
set global group_replication_single_primary_mode=ON;
set global group_replication_enforce_update_everywhere_checks=OFF;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
-- 新节点加入
start group_replication;
报错提示：
mysql> start group_replication;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
mysql>
处理过程：
STOP GROUP_REPLICATION;
reset master;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
start GROUP_REPLICATION;
7、查看所有节点
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 32905643-197f-11ee-a60e-0242ac480018 | 172.72.0.24 | 3306 | RECOVERING | SECONDARY | 8.0.20 |
| group_replication_applier | 348ecc41-194c-11ee-9c6e-0242ac480014 | 172.72.0.21 | 3306 | RECOVERING | SECONDARY | 8.0.20 |
| group_replication_applier | 7bb32a93-194c-11ee-9c2e-0242ac480016 | 172.72.0.22 | 3306 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | e1aaa1ff-196b-11ee-9b44-0242ac480017 | 172.72.0.23 | 3306 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
4 rows in set (0.00 sec)
mysql>
六、重置MGR配置
如果需要重置，那么需要执行如下命令：
STOP GROUP_REPLICATION;
reset master;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
start GROUP_REPLICATION;
执行过程：
mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (4.69 sec)
mysql> reset master;
Query OK, 0 rows affected (0.03 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.03 sec)
mysql> start GROUP_REPLICATION;
Query OK, 0 rows affected (8.55 sec)
mysql>
七、MGR集群命令汇总：
1、查看MGR集群成员组
SELECT * FROM performance_schema.replication_group_members;
2、停止集群组和启动集群组
STOP GROUP_REPLICATION;
start GROUP_REPLICATION; #可以加入新节点
3、安装MGR插件（新增节点执行）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;
4、查看是否为多主，0表示多主，1表示单主。
SELECT @@group_replication_single_primary_mode;
5、多主和单主切换
-- 单主切多主
select group_replication_switch_to_multi_primary_mode();
-- 多主切单主，入参需要传入主库的server_uuid
select group_replication_switch_to_single_primary_mode('@@server_uuid') ;
-- 查看组信息
SELECT * FROM performance_schema.replication_group_members;
6、查看master的状态
show master status;
show binlog events in 'master.000006' limit 5;
