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

 1# 拉取镜像 2docker pull mysql:8.0.20 3# 创建专用网络 4docker network create --subnet=172.72.0.0/24 mysql-network 5 6# 创建目录存储数据 7mkdir -p /usr/local/mysql/lhrmgr15/conf.d 8mkdir -p /usr/local/mysql/lhrmgr15/data 9mkdir -p /usr/local/mysql/lhrmgr16/conf.d10mkdir -p /usr/local/mysql/lhrmgr16/data11mkdir -p /usr/local/mysql/lhrmgr17/conf.d12mkdir -p /usr/local/mysql/lhrmgr17/data131415# 创建3个节点的MySQL16docker run -d --name mysql8020mgr33065 \17   -h lhrmgr15 -p 33065:3306 --net=mysql-network --ip 172.72.0.15 \18   -v /usr/local/mysql/lhrmgr15/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr15/data:/var/lib/mysql/ \19   -e MYSQL_ROOT_PASSWORD=lhr \20   -e TZ=Asia/Shanghai \21   mysql:8.0.202223docker run -d --name mysql8020mgr33066 \24   -h lhrmgr16 -p 33066:3306 --net=mysql-network --ip 172.72.0.16 \25   -v /usr/local/mysql/lhrmgr16/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr16/data:/var/lib/mysql/ \26   -e MYSQL_ROOT_PASSWORD=lhr \27   -e TZ=Asia/Shanghai \28   mysql:8.0.202930docker run -d --name mysql8020mgr33067 \31   -h lhrmgr17 -p 33067:3306 --net=mysql-network --ip 172.72.0.17 \32   -v /usr/local/mysql/lhrmgr17/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr17/data:/var/lib/mysql/ \33   -e MYSQL_ROOT_PASSWORD=lhr \34   -e TZ=Asia/Shanghai \35   mysql:8.0.20

二、修改MySQL参数

  1cat > /usr/local/mysql/lhrmgr15/conf.d/my.cnf <<"EOF"  2[mysqld]  3user=mysql  4port=3306  5character_set_server=utf8mb4  6secure_file_priv=''  7server-id = 802033065  8default-time-zone = '+8:00'  9log_timestamps = SYSTEM 10log-bin =  11binlog_format=row 12binlog_checksum=NONE 13log-slave-updates=1 14skip-name-resolve 15auto-increment-increment=2 16auto-increment-offset=1 17gtid-mode=ON 18enforce-gtid-consistency=on 19default_authentication_plugin=mysql_native_password 20max_allowed_packet = 500M 21 22master_info_repository=TABLE 23relay_log_info_repository=TABLE 24relay_log=lhrmgr15-relay-bin-ip15 25 26 27transaction_write_set_extraction=XXHASH64 28loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" 29loose-group_replication_start_on_boot=OFF 30loose-group_replication_local_address= "172.72.0.15:33061" 31loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063" 32loose-group_replication_bootstrap_group=OFF 33loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17" 34 35report_host=172.72.0.15 36report_port=3306 37 38EOF 39 40 41cat >  /usr/local/mysql/lhrmgr16/conf.d/my.cnf <<"EOF" 42[mysqld] 43user=mysql 44port=3306 45character_set_server=utf8mb4 46secure_file_priv='' 47server-id = 802033066 48default-time-zone = '+8:00' 49log_timestamps = SYSTEM 50log-bin =  51binlog_format=row 52binlog_checksum=NONE 53log-slave-updates=1 54gtid-mode=ON 55enforce-gtid-consistency=ON 56skip_name_resolve 57default_authentication_plugin=mysql_native_password 58max_allowed_packet = 500M 59 60master_info_repository=TABLE 61relay_log_info_repository=TABLE 62relay_log=lhrmgr16-relay-bin-ip16 63 64 65transaction_write_set_extraction=XXHASH64 66loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" 67loose-group_replication_start_on_boot=OFF 68loose-group_replication_local_address= "172.72.0.16:33062" 69loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063" 70loose-group_replication_bootstrap_group=OFF 71loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17" 72 73report_host=172.72.0.16 74report_port=3306 75 76EOF 77 78 79cat > /usr/local/mysql/lhrmgr17/conf.d/my.cnf <<"EOF" 80[mysqld] 81user=mysql 82port=3306 83character_set_server=utf8mb4 84secure_file_priv='' 85server-id = 802033067 86default-time-zone = '+8:00' 87log_timestamps = SYSTEM 88log-bin =  89binlog_format=row 90binlog_checksum=NONE 91log-slave-updates=1 92gtid-mode=ON 93enforce-gtid-consistency=ON 94skip_name_resolve 95default_authentication_plugin=mysql_native_password 96max_allowed_packet = 500M 97 98 99master_info_repository=TABLE100relay_log_info_repository=TABLE101relay_log=lhrmgr16-relay-bin-ip16102103104transaction_write_set_extraction=XXHASH64105loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"106loose-group_replication_start_on_boot=OFF107loose-group_replication_local_address= "172.72.0.17:33063"108loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063"109loose-group_replication_bootstrap_group=OFF110loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17"111112report_host=172.72.0.17113report_port=3306114115EOF

三、重启MySQL环境

 1# 重启MySQL 2docker restart mysql8020mgr33065 mysql8020mgr33066 mysql8020mgr33067 3docker ps 4 5# 进入MySQL 6docker exec -it mysql8020mgr33065 bash 7docker exec -it mysql8020mgr33065 mysql -uroot -plhr 8 9#远程连接MySQL10mysql -uroot -plhr -h192.168.1.35 -P33065 11mysql -uroot -plhr -h192.168.1.35 -P33066 12mysql -uroot -plhr -h192.168.1.35 -P33067 1314# 查看MySQL日志15docker logs -f --tail 10 mysql8020mgr3306516docker logs -f --tail 10 mysql8020mgr3306617docker logs -f --tail 10 mysql8020mgr330671819# 查看MySQL的主机名、server_id和server_uuid20mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"21mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"22mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"

结果：

 1[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid" 2mysql: [Warning] Using a password on the command line interface can be insecure. 3+------------+-------------+--------------------------------------+ 4| @@hostname | @@server_id | @@server_uuid                        | 5+------------+-------------+--------------------------------------+ 6| lhrmgr15   |   802033065 | 611717fe-d785-11ea-9342-0242ac48000f | 7+------------+-------------+--------------------------------------+ 8[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid" 9mysql: [Warning] Using a password on the command line interface can be insecure.10+------------+-------------+--------------------------------------+11| @@hostname | @@server_id | @@server_uuid                        |12+------------+-------------+--------------------------------------+13| lhrmgr16   |   802033066 | 67090f47-d785-11ea-b76c-0242ac480010 |14+------------+-------------+--------------------------------------+15[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"16mysql: [Warning] Using a password on the command line interface can be insecure.17+------------+-------------+--------------------------------------+18| @@hostname | @@server_id | @@server_uuid                        |19+------------+-------------+--------------------------------------+20| lhrmgr17   |   802033067 | 678cf064-d785-11ea-b8ce-0242ac480011 |21+------------+-------------+--------------------------------------+22[root@docker35 ~]#

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

 1MySQL [(none)]> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 2Query OK, 0 rows affected (0.23 sec) 3 4MySQL [(none)]> show plugins; 5+---------------------------------+----------+--------------------+----------------------+---------+ 6| Name                            | Status   | Type               | Library              | License | 7+---------------------------------+----------+--------------------+----------------------+---------+ 8| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     | 9+---------------------------------+----------+--------------------+----------------------+---------+1045 rows in set (0.00 sec)

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

1SET SQL_LOG_BIN=0;2CREATE USER repl@'%' IDENTIFIED BY 'lhr';3GRANT REPLICATION SLAVE ON *.* TO repl@'%';4FLUSH PRIVILEGES;5SET SQL_LOG_BIN=1;6CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';

执行过程：

 1MySQL [(none)]> SET SQL_LOG_BIN=0; 2Query OK, 0 rows affected (0.00 sec) 3 4MySQL [(none)]> CREATE USER repl@'%' IDENTIFIED BY 'lhr'; 5Query OK, 0 rows affected (0.01 sec) 6 7MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; 8Query OK, 0 rows affected (0.00 sec) 910MySQL [(none)]> FLUSH PRIVILEGES;11Query OK, 0 rows affected (0.00 sec)1213MySQL [(none)]> SET SQL_LOG_BIN=1;14Query OK, 0 rows affected (0.00 sec)1516MySQL [(none)]> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';17Query OK, 0 rows affected, 1 warning (0.04 sec)

六、启动MGR单主模式

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

1SET GLOBAL group_replication_bootstrap_group=ON;2START GROUP_REPLICATION;3SET GLOBAL group_replication_bootstrap_group=OFF;45-- 查看MGR组信息 6SELECT * FROM performance_schema.replication_group_members;

执行过程：

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

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

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

执行结果：

1MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;2+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+3| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |4+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+5| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |6| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |7| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |8+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+93 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%'; 2+---------------------------------------+-------+ 3| Variable_name                         | Value | 4+---------------------------------------+-------+ 5| group_replication_single_primary_mode | ON    | 6+---------------------------------------+-------+ 71 row in set (0.01 sec) 8 9MySQL [(none)]> SELECT @@group_replication_single_primary_mode;10+-----------------------------------------+11| @@group_replication_single_primary_mode |12+-----------------------------------------+13|                                       1 |14+-----------------------------------------+151 row in set (0.00 sec)

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

7.2、函数实现多主和单主切换

函数切换：从MySQL 8.0.13开始，可以使用函数进行在线修改MGR模式。

1-- 单主切多主2select group_replication_switch_to_multi_primary_mode(); 3-- 多主切单主，入参需要传入主库的server_uuid4select group_replication_switch_to_single_primary_mode('@@server_uuid') ;56-- 查看组信息7SELECT * FROM performance_schema.replication_group_members;

7.2.1、单主切多主模式

 1MySQL [(none)]> SELECT @@group_replication_single_primary_mode; 2+-----------------------------------------+ 3| @@group_replication_single_primary_mode | 4+-----------------------------------------+ 5|                                       1 | 6+-----------------------------------------+ 71 row in set (0.00 sec) 8 9MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;10+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+11| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |12+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+13| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |14| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |15| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |16+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+173 rows in set (0.00 sec)1819MySQL [(none)]> select group_replication_switch_to_multi_primary_mode();20+--------------------------------------------------+21| group_replication_switch_to_multi_primary_mode() |22+--------------------------------------------------+23| Mode switched to multi-primary successfully.     |24+--------------------------------------------------+251 row in set (1.01 sec)2627MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;28+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+29| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |30+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+31| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |32| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |33| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |34+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+353 rows in set (0.00 sec)36MySQL [(none)]> SELECT @@group_replication_single_primary_mode;37+-----------------------------------------+38| @@group_replication_single_primary_mode |39+-----------------------------------------+40|                                       0 |41+-----------------------------------------+

7.2.2、多主切单主模式

 1MySQL [(none)]> SELECT @@group_replication_single_primary_mode; 2+-----------------------------------------+ 3| @@group_replication_single_primary_mode | 4+-----------------------------------------+ 5|                                       0 | 6+-----------------------------------------+ 71 row in set (0.00 sec) 8 9MySQL [(none)]> select group_replication_switch_to_single_primary_mode('67090f47-d785-11ea-b76c-0242ac480010') ;10+-----------------------------------------------------------------------------------------+11| group_replication_switch_to_single_primary_mode('67090f47-d785-11ea-b76c-0242ac480010') |12+-----------------------------------------------------------------------------------------+13| Mode switched to single-primary successfully.                                           |14+-----------------------------------------------------------------------------------------+151 row in set (1.02 sec)1617MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;18+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+19| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |20+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+21| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |22| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | PRIMARY     | 8.0.20         |23| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         |24+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+253 rows in set (0.00 sec)2627MySQL [(none)]> SELECT @@group_replication_single_primary_mode;28+-----------------------------------------+29| @@group_replication_single_primary_mode |30+-----------------------------------------+31|                                       1 |32+-----------------------------------------+331 row in set (0.00 sec)

7.3、手动切换

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

7.3.1、单主切多主模式

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

1stop group_replication;2set global group_replication_single_primary_mode=OFF;3set global group_replication_enforce_update_everywhere_checks=ON;

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

1SET GLOBAL group_replication_bootstrap_group=ON; 2START GROUP_REPLICATION; 3SET GLOBAL group_replication_bootstrap_group=OFF;

3、其他节点执行

1START GROUP_REPLICATION;

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

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

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

7.3.2、多主切单主模式

1、所有节点执行

1stop group_replication;2set global group_replication_enforce_update_everywhere_checks=OFF;3set global group_replication_single_primary_mode=ON;

2、主节点（172.72.0.16）执行

1SET GLOBAL group_replication_bootstrap_group=ON; 2START GROUP_REPLICATION; 3SET GLOBAL group_replication_bootstrap_group=OFF;

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

1START GROUP_REPLICATION;

4、查看MGR组信息

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

八、测试同步

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

 1create database lhrdb; 2CREATE TABLE lhrdb.`tb1` ( 3 `id` int(11) NOT NULL AUTO_INCREMENT, 4 `hostname` varchar(100) DEFAULT NULL, 5 `server_id` varchar(100) DEFAULT NULL, 6 PRIMARY KEY (`id`) 7) ENGINE=InnoDB DEFAULT CHARSET=latin1; 8 910insert into lhrdb.tb1(hostname,server_id) select @@hostname,@@server_id;11select * from lhrdb.tb1;1213-- 3个节点查询出来的值一样14MySQL [(none)]> select * from lhrdb.tb1;15+----+----------+-----------+16| id | hostname | server_id |17+----+----------+-----------+18|  1 | lhrmgr16 | 802033066 |19+----+----------+-----------+201 row in set (0.02 sec)

九、MGR新增节点

9.1、创建新MySQL节点

 1mkdir -p /usr/local/mysql/lhrmgr18/conf.d 2mkdir -p /usr/local/mysql/lhrmgr18/data 3 4docker run -d --name mysql8020mgr33068 \ 5  -h lhrmgr18 -p 33068:3306 --net=mysql-network --ip 172.72.0.18 \ 6  -v /usr/local/mysql/lhrmgr18/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr18/data:/var/lib/mysql/ \ 7  -e MYSQL_ROOT_PASSWORD=lhr \ 8  -e TZ=Asia/Shanghai \ 9  mysql:8.0.2010111213cat > /usr/local/mysql/lhrmgr18/conf.d/my.cnf <<"EOF"14[mysqld]15user=mysql16port=330617character_set_server=utf8mb418secure_file_priv=''19server-id = 80203306820log-bin = 21binlog_format=row22binlog_checksum=NONE23log-slave-updates=124skip-name-resolve25auto-increment-increment=226auto-increment-offset=127gtid-mode=ON28enforce-gtid-consistency=on29default_authentication_plugin=mysql_native_password30max_allowed_packet = 500M31log_slave_updates=on3233master_info_repository=TABLE34relay_log_info_repository=TABLE35relay_log=lhrmgr18-relay-bin-ip183637transaction_write_set_extraction=XXHASH6438loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"39loose-group_replication_start_on_boot=OFF40loose-group_replication_local_address= "172.72.0.18:33064"41loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063,172.72.0.18:33064"42loose-group_replication_bootstrap_group=OFF43loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18"44report_host=172.72.0.1845report_port=33064647EOF48495051docker restart mysql8020mgr33068525354docker ps55mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"56mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"57mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"58mysql -uroot -plhr -h192.168.1.35 -P33068 -e "select @@hostname,@@server_id,@@server_uuid"59mysql -uroot -plhr -h192.168.1.35 -P33065 60mysql -uroot -plhr -h192.168.1.35 -P33066 61mysql -uroot -plhr -h192.168.1.35 -P33067 62mysql -uroot -plhr -h192.168.1.35 -P33068 63docker logs -f --tail 10 mysql8020mgr3306564docker logs -f --tail 10 mysql8020mgr3306665docker logs -f --tail 10 mysql8020mgr3306766docker logs -f --tail 10 mysql8020mgr33068

9.2、新节点安装MGR插件

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

9.3、新节点设置复制账号

1-- 设置复制账号（新增节点执行）2SET SQL_LOG_BIN=0;3CREATE USER repl@'%' IDENTIFIED BY 'lhr';4GRANT REPLICATION SLAVE ON *.* TO repl@'%';5FLUSH PRIVILEGES;6SET SQL_LOG_BIN=1;7CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';

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

1set 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';2stop group_replication;3set global group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18";4start group_replication;

9.5、新节点加入

 1-- 4个节点需要保证以下2个参数的值一致 2MySQL [(none)]> select @@group_replication_enforce_update_everywhere_checks,@@group_replication_single_primary_mode; 3+------------------------------------------------------+-----------------------------------------+ 4| @@group_replication_enforce_update_everywhere_checks | @@group_replication_single_primary_mode | 5+------------------------------------------------------+-----------------------------------------+ 6|                                                    0 |                                       1 | 7+------------------------------------------------------+-----------------------------------------+ 8 9-- 如果不一致，那么需要修改10set global group_replication_single_primary_mode=ON;11set global group_replication_enforce_update_everywhere_checks=OFF;12CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';1314-- 新节点加入15start group_replication;

9.6、查看所有节点

 1MySQL [lhrdb]> SELECT * FROM performance_schema.replication_group_members; 2+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 4+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 5| group_replication_applier | 611717fe-d785-11ea-9342-0242ac48000f | 172.72.0.15 |        3306 | ONLINE       | PRIMARY     | 8.0.20         | 6| group_replication_applier | 67090f47-d785-11ea-b76c-0242ac480010 | 172.72.0.16 |        3306 | ONLINE       | SECONDARY   | 8.0.20         | 7| group_replication_applier | 678cf064-d785-11ea-b8ce-0242ac480011 | 172.72.0.17 |        3306 | ONLINE       | SECONDARY   | 8.0.20         | 8| group_replication_applier | e4d6bf4b-d78d-11ea-b1b6-0242ac480012 | 172.72.0.18 |        3306 | ONLINE       | SECONDARY   | 8.0.20         | 9+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+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;

【DB宝18】在Docker中安装使用MySQL高可用之MGR

目录