目录
一、创建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.d
10mkdir -p /usr/local/mysql/lhrmgr16/data
11mkdir -p /usr/local/mysql/lhrmgr17/conf.d
12mkdir -p /usr/local/mysql/lhrmgr17/data
13
14
15# 创建3个节点的MySQL
16docker 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.20
22
23docker 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.20
29
30docker 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=TABLE
100relay_log_info_repository=TABLE
101relay_log=lhrmgr16-relay-bin-ip16
102
103
104transaction_write_set_extraction=XXHASH64
105loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
106loose-group_replication_start_on_boot=OFF
107loose-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=OFF
110loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17"
111
112report_host=172.72.0.17
113report_port=3306
114
115EOF
三、重启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#远程连接MySQL
10mysql -uroot -plhr -h192.168.1.35 -P33065
11mysql -uroot -plhr -h192.168.1.35 -P33066
12mysql -uroot -plhr -h192.168.1.35 -P33067
13
14# 查看MySQL日志
15docker logs -f --tail 10 mysql8020mgr33065
16docker logs -f --tail 10 mysql8020mgr33066
17docker logs -f --tail 10 mysql8020mgr33067
18
19# 查看MySQL的主机名、server_id和server_uuid
20mysql -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)
9
10MySQL [(none)]> FLUSH PRIVILEGES;
11Query OK, 0 rows affected (0.00 sec)
12
13MySQL [(none)]> SET SQL_LOG_BIN=1;
14Query OK, 0 rows affected (0.00 sec)
15
16MySQL [(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;
4
5-- 查看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)
9
10MySQL [(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_uuid
4select group_replication_switch_to_single_primary_mode('@@server_uuid') ;
5
6-- 查看组信息
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)
18
19MySQL [(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)
26
27MySQL [(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)
16
17MySQL [(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)
26
27MySQL [(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
9
10insert into lhrdb.tb1(hostname,server_id) select @@hostname,@@server_id;
11select * from lhrdb.tb1;
12
13-- 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.20
10
11
12
13cat > /usr/local/mysql/lhrmgr18/conf.d/my.cnf <<"EOF"
14[mysqld]
15user=mysql
16port=3306
17character_set_server=utf8mb4
18secure_file_priv=''
19server-id = 802033068
20log-bin =
21binlog_format=row
22binlog_checksum=NONE
23log-slave-updates=1
24skip-name-resolve
25auto-increment-increment=2
26auto-increment-offset=1
27gtid-mode=ON
28enforce-gtid-consistency=on
29default_authentication_plugin=mysql_native_password
30max_allowed_packet = 500M
31log_slave_updates=on
32
33master_info_repository=TABLE
34relay_log_info_repository=TABLE
35relay_log=lhrmgr18-relay-bin-ip18
36
37transaction_write_set_extraction=XXHASH64
38loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
39loose-group_replication_start_on_boot=OFF
40loose-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=OFF
43loose-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.18
45report_port=3306
46
47EOF
48
49
50
51docker restart mysql8020mgr33068
52
53
54docker ps
55mysql -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 mysql8020mgr33065
64docker logs -f --tail 10 mysql8020mgr33066
65docker logs -f --tail 10 mysql8020mgr33067
66docker 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';
13
14-- 新节点加入
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宝，作者：小麦苗
• 作者博客地址：http://blog.itpub.net/26736162/
• 作者微信：db_bao• 作者QQ：646634621，QQ群：230161599、618766405
• 提供Oracle OCP、OCM、高可用（rac+dg+ogg）和MySQL DBA培训
• 版权所有，欢迎分享本文，转载请保留出处• 若有侵权请联系小麦苗删除
★DB宝分享的IT资料：https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag★DB宝笔试面试详解：https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
长按下图识别二维码，关注小麦苗的微信公众号：DB宝，学习最实用的数据库技术。
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
