文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >专栏 >在Docker中安装使用MySQL 高可用之MGR（多主同时写入）

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

作者头像
jwangkun
发布于 2021-12-23 17:32:00
发布于 2021-12-23 17:32:00
3.3K0
举报
文章被收录于专栏：John Wong's BlogJohn Wong's Blog

一、创建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环境

代码语言：javascript
复制
 # 拉取镜像
 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参数

代码语言：javascript
复制
  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环境

代码语言：javascript
复制
  # 重启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"

结果：

代码语言：javascript
复制
[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插件（所有节点执行）

代码语言：javascript
复制
 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)

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

代码语言：javascript
复制
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';

执行过程：

代码语言：javascript
复制
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)上执行

代码语言：javascript
复制
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;

执行过程：

代码语言：javascript
复制
 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）上执行

代码语言：javascript
复制
START GROUP_REPLICATION;
-- 查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;

执行结果：

代码语言：javascript
复制
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、查询当前模式

代码语言：javascript
复制
 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模式。

代码语言：javascript
复制
-- 单主切多主
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、单主切多主模式

代码语言：javascript
复制
 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、多主切单主模式

代码语言：javascript
复制
 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、停止组复制(所有节点执行)：

代码语言：javascript
复制
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

2、随便选择某个节点执行

代码语言：javascript
复制
SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION; 
SET GLOBAL group_replication_bootstrap_group=OFF;

3、其他节点执行

代码语言：javascript
复制
START GROUP_REPLICATION;

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

代码语言：javascript
复制
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、所有节点执行

代码语言：javascript
复制
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）执行

代码语言：javascript
复制
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）执行

代码语言：javascript
复制
START GROUP_REPLICATION;

4、查看MGR组信息

代码语言：javascript
复制
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)

八、测试同步

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

代码语言：javascript
复制
 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节点

代码语言：javascript
复制
 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插件

代码语言：javascript
复制
-- 安装MGR插件（新增节点执行）
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;

9.3、新节点设置复制账号

代码语言：javascript
复制
 -- 设置复制账号（新增节点执行）
 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节点执行修改参数

代码语言：javascript
复制
 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、新节点加入

代码语言：javascript
复制
  -- 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、查看所有节点

代码语言：javascript
复制
 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配置

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

代码语言：javascript
复制
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 解除死锁状态→

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020-11-11，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
云数据库 SQL Server
sql
访问管理

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

数据库
云数据库 SQL Server
sql
访问管理
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
目录
  • 一、创建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配置
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2026 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论