MySQL Docker

在Docker中安装使用MySQL 高可用之MGR（多主同时写入）

Posted on 2020-11-11，19 min read

一、创建3台MySQL环境
二、修改MySQL参数
三、重启MySQL环境
四、安装MGR插件（所有节点执行）
五、设置复制账号（所有节点执行）
六、启动MGR单主模式
6.1、启动MGR，在主库(172.72.0.15)上执行
6.2、其他节点加入MGR，在从库（172.72.0.16，172.72.0.17）上执行
七、多主和单主模式切换
7.1、查询当前模式
7.2、函数实现多主和单主切换
7.2.1、单主切多主模式
7.2.2、多主切单主模式
7.3、手动切换
7.3.1、单主切多主模式
7.3.2、多主切单主模式
八、测试同步
九、MGR新增节点
9.1、创建新MySQL节点
9.2、新节点安装MGR插件
9.3、新节点设置复制账号
9.4、在原3节点执行修改参数
9.5、新节点加入
9.6、查看所有节点
十、重置MGR配置

一、创建3台MySQL环境

 # 拉取镜像
 docker pull mysql:8.0.20
 # 创建专用网络
 docker network create --subnet=172.72.0.0/24 mysql-network
 
 # 创建目录存储数据
 mkdir -p /usr/local/mysql/lhrmgr15/conf.d
 mkdir -p /usr/local/mysql/lhrmgr15/data
 mkdir -p /usr/local/mysql/lhrmgr16/conf.d
 mkdir -p /usr/local/mysql/lhrmgr16/data
 mkdir -p /usr/local/mysql/lhrmgr17/conf.d
 mkdir -p /usr/local/mysql/lhrmgr17/data

# 创建3个节点的MySQL
docker run -d --name mysql8020mgr33065 \
   -h lhrmgr15 -p 33065:3306 --net=mysql-network --ip 172.72.0.15 \
   -v /usr/local/mysql/lhrmgr15/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr15/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.20

docker run -d --name mysql8020mgr33066 \
   -h lhrmgr16 -p 33066:3306 --net=mysql-network --ip 172.72.0.16 \
   -v /usr/local/mysql/lhrmgr16/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr16/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.20

docker run -d --name mysql8020mgr33067 \
   -h lhrmgr17 -p 33067:3306 --net=mysql-network --ip 172.72.0.17 \
   -v /usr/local/mysql/lhrmgr17/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr17/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
   mysql:8.0.20

二、修改MySQL参数

  cat > /usr/local/mysql/lhrmgr15/conf.d/my.cnf <<"EOF"
  [mysqld]
  user=mysql
  port=3306
  character_set_server=utf8mb4
  secure_file_priv=''
  server-id = 802033065
  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=lhrmgr15-relay-bin-ip15
 
 
  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.15:33061"
  loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
  loose-group_replication_bootstrap_group=OFF
  loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17"
  
  report_host=172.72.0.15
  report_port=3306
 
 EOF

  cat >  /usr/local/mysql/lhrmgr16/conf.d/my.cnf <<"EOF"
  [mysqld]
  user=mysql
  port=3306
  character_set_server=utf8mb4
  secure_file_priv=''
  server-id = 802033066
  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=lhrmgr16-relay-bin-ip16

  transaction_write_set_extraction=XXHASH64
  loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
  loose-group_replication_local_address= "172.72.0.16:33062"
  loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
  loose-group_replication_bootstrap_group=OFF
  loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17"
  
  report_host=172.72.0.16
  report_port=3306
  
  EOF
 
 
  cat > /usr/local/mysql/lhrmgr17/conf.d/my.cnf <<"EOF"
  [mysqld]
  user=mysql
  port=3306
  character_set_server=utf8mb4
  secure_file_priv=''
  server-id = 802033067
  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=lhrmgr16-relay-bin-ip16
 
 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.17:33063"
 loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"
 loose-group_replication_bootstrap_group=OFF
 loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17"
 
 report_host=172.72.0.17
 report_port=3306
 
 EOF

三、重启MySQL环境

  # 重启MySQL
  docker restart mysql8020mgr33065 mysql8020mgr33066 mysql8020mgr33067
  docker ps
  
  # 进入MySQL
  docker exec -it mysql8020mgr33065 bash
  docker exec -it mysql8020mgr33065 mysql -uroot -plhr
 
  #远程连接MySQL
 mysql -uroot -plhr -h192.168.1.35 -P33065 
 mysql -uroot -plhr -h192.168.1.35 -P33066 
 mysql -uroot -plhr -h192.168.1.35 -P33067 
 
 # 查看MySQL日志
 docker logs -f --tail 10 mysql8020mgr33065
 docker logs -f --tail 10 mysql8020mgr33066
 docker logs -f --tail 10 mysql8020mgr33067
  
 # 查看MySQL的主机名、server_id和server_uuid
 mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
 mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
 mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"

结果：

[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
 mysql: [Warning] Using a password on the command line interface can be insecure.
 +------------+-------------+--------------------------------------+
 | @@hostname | @@server_id | @@server_uuid                        |
 +------------+-------------+--------------------------------------+
 | lhrmgr15   |   802033065 | 611717fe-d785-11ea-9342-0242ac48000f |
 +------------+-------------+--------------------------------------+
 [root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
 mysql: [Warning] Using a password on the command line interface can be insecure.
 +------------+-------------+--------------------------------------+
 | @@hostname | @@server_id | @@server_uuid                        |
 +------------+-------------+--------------------------------------+
| lhrmgr16   |   802033066 | 67090f47-d785-11ea-b76c-0242ac480010 |
+------------+-------------+--------------------------------------+
[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+--------------------------------------+
| @@hostname | @@server_id | @@server_uuid                        |
+------------+-------------+--------------------------------------+
| lhrmgr17   |   802033067 | 678cf064-d785-11ea-b8ce-0242ac480011 |
+------------+-------------+--------------------------------------+
[root@docker35 ~]#

四、安装MGR插件（所有节点执行）

 MySQL [(none)]> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
 Query OK, 0 rows affected (0.23 sec)
 
 MySQL [(none)]> show plugins;
 +---------------------------------+----------+--------------------+----------------------+---------+
 | Name                            | Status   | Type               | Library              | License |
 +---------------------------------+----------+--------------------+----------------------+---------+
 | group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
 +---------------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)

五、设置复制账号（所有节点执行）

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 [(none)]> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
 
MySQL [(none)]> CREATE USER repl@'%' IDENTIFIED BY 'lhr';
Query OK, 0 rows affected (0.01 sec)
 
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
 
MySQL [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 1 warning (0.04 sec)

六、启动MGR单主模式

6.1、启动MGR，在主库(172.72.0.15)上执行

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;

执行过程：

 MySQL [(none)]> SET GLOBAL group_replication_bootstrap_group=ON;
 Query OK, 0 rows affected (0.00 sec)
 
 MySQL [(none)]> START GROUP_REPLICATION;
 Query OK, 0 rows affected (3.49 sec)
 
 MySQL [(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;
 Query OK, 0 rows affected (0.00 sec)
 
 MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

6.2、其他节点加入MGR，在从库（172.72.0.16，172.72.0.17）上执行

START GROUP_REPLICATION;
-- 查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;

执行结果：

MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)

可以看到，3个节点状态为online，并且主节点为172.72.0.15，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功。

七、多主和单主模式切换

7.1、查询当前模式

 1MySQL [(none)]>  show variables like '%group_replication_single_primary_mode%';
 | Variable_name                         | Value |
 +---------------------------------------+-------+
 | group_replication_single_primary_mode | ON    |
 +---------------------------------------+-------+
 1 row in set (0.01 sec)
 
 MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
 +-----------------------------------------+
 | @@group_replication_single_primary_mode |
 +-----------------------------------------+
 |                                       1 |
 +-----------------------------------------+
 1 row in set (0.00 sec)

参数group_replication_single_primary_mode为ON，表示单主模式。

7.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;

7.2.1、单主切多主模式

 MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
 +-----------------------------------------+
 | @@group_replication_single_primary_mode |
 +-----------------------------------------+
 |                                       1 |
 +-----------------------------------------+
 1 row in set (0.00 sec)
 
 MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

MySQL [(none)]> 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 [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+

7.2.2、多主切单主模式

 MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
 +-----------------------------------------+
 | @@group_replication_single_primary_mode |
 +-----------------------------------------+
 |                                       0 |
 +-----------------------------------------+
 1 row in set (0.00 sec)
 
 MySQL [(none)]> select group_replication_switch_to_single_primary_mode('67090f47-d785-11ea-b76c-0242ac480010') ;
 +-----------------------------------------------------------------------------------------+
 | group_replication_switch_to_single_primary_mode('67090f47-d785-11ea-b76c-0242ac480010') |
 +-----------------------------------------------------------------------------------------+
 | Mode switched to single-primary successfully.                                           |
 +-----------------------------------------------------------------------------------------+
 1 row in set (1.02 sec)
 
 MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
 | group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
 | group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 3 rows in set (0.00 sec)
 
 MySQL [(none)]> SELECT @@group_replication_single_primary_mode;
 +-----------------------------------------+
 | @@group_replication_single_primary_mode |
 +-----------------------------------------+
 |                                       1 |
 +-----------------------------------------+
 1 row in set (0.00 sec)

7.3、手动切换

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

7.3.1、单主切多主模式

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 [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

可以看到所有节点状态都是online，角色都是PRIMARY，MGR多主模式切换成功。

7.3.2、多主切单主模式

1、所有节点执行

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

2、主节点（172.72.0.16）执行

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

3、从节点（172.72.0.15、172.72.0.17）执行

START GROUP_REPLICATION;

4、查看MGR组信息

MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

八、测试同步

在主节点上执行以下命令，然后在其它节点查询：

 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 [(none)]> select * from lhrdb.tb1;
+----+----------+-----------+
| id | hostname | server_id |
+----+----------+-----------+
|  1 | lhrmgr16 | 802033066 |
+----+----------+-----------+
201 row in set (0.02 sec)

九、MGR新增节点

9.1、创建新MySQL节点

 mkdir -p /usr/local/mysql/lhrmgr18/conf.d
 mkdir -p /usr/local/mysql/lhrmgr18/data
 
 docker run -d --name mysql8020mgr33068 \
   -h lhrmgr18 -p 33068:3306 --net=mysql-network --ip 172.72.0.18 \
   -v /usr/local/mysql/lhrmgr18/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr18/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   -e TZ=Asia/Shanghai \
    mysql:8.0.20

cat > /usr/local/mysql/lhrmgr18/conf.d/my.cnf <<"EOF"
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 802033068
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
log_slave_updates=on

master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=lhrmgr18-relay-bin-ip18

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.18:33064"
loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063,172.72.0.18:33064"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18"
report_host=172.72.0.18
report_port=3306

EOF

docker restart mysql8020mgr33068

docker ps
mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33068 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h192.168.1.35 -P33065 
mysql -uroot -plhr -h192.168.1.35 -P33066 
mysql -uroot -plhr -h192.168.1.35 -P33067 
mysql -uroot -plhr -h192.168.1.35 -P33068 
docker logs -f --tail 10 mysql8020mgr33065
docker logs -f --tail 10 mysql8020mgr33066
docker logs -f --tail 10 mysql8020mgr33067
docker logs -f --tail 10 mysql8020mgr33068

9.2、新节点安装MGR插件

-- 安装MGR插件（新增节点执行）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;

9.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';

9.4、在原3节点执行修改参数

 set global group_replication_group_seeds='172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063,172.72.0.18:33064';
 stop group_replication;
 set global group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18";
 start group_replication;

9.5、新节点加入

  -- 4个节点需要保证以下2个参数的值一致
 2MySQL [(none)]> 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 |
  +------------------------------------------------------+-----------------------------------------+
 
  -- 如果不一致，那么需要修改
 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='lhr' FOR CHANNEL 'group_replication_recovery';

-- 新节点加入
 start group_replication;

9.6、查看所有节点

 MySQL [lhrdb]> SELECT * FROM performance_schema.replication_group_members;
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 | group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
 | group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
 | group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
 | group_replication_applier | e4d6bf4b-d78d-11ea-b1b6-0242ac480012 | 172.72.0.18 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
 +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
104 rows in set (0.31 sec)

十、重置MGR配置

如果需要重置，那么需要执行如下命令：

1STOP GROUP_REPLICATION;
2reset master;
3SET SQL_LOG_BIN=1;
4CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';
5start GROUP_REPLICATION;

下一篇： MySQL 解除死锁状态→

▲