MySQL5.7-OCP-配置组复制之多主模式(Multi-Primary Mode)
Configuring Group Replication in Multi-Primary Mode
1.接上面单主模式的配置，将4台实例全部停止
[root@node232 group_Replication]#mysqld_multi --defaults-file=/server/shell_scripts/group_Replication/grouprep.cnf stop 1-4
或者：
[root@node232 group_Replication]# mysqladmin -uroot -p -h127.0.0.1 -P3311 shutdown
[root@node232 group_Replication]# mysqladmin -uroot -p -h127.0.0.1 -P3312 shutdown
[root@node232 group_Replication]# mysqladmin -uroot -p -h127.0.0.1 -P3313 shutdown
[root@node232 group_Replication]# mysqladmin -uroot -p -h127.0.0.1 -P3314 shutdown
2.所有服务器配置的文件当前配置在单主模式下，通过所以实例配置文件的参数：loose-groupreplication-single-primary-mode 为FALSE，来开启多主模式
cat /server/shell_scripts/group_Replication/grouprep.cnf
[mysql1]
loose-group-replication-single-primary-mode=FALSE
[mysql2]
loose-group-replication-single-primary-mode=FALSE
[mysql3]
loose-group-replication-single-primary-mode=FALSE
[mysql4]
loose-group-replication-single-primary-mode=FALSE
3.开启所有的4台服务器：
[root@node232 group_Replication]# mysqld_multi --defaults-file=/server/shell_scripts/group_Replication/grouprep.cnf start 1-4
4.进入第一台服务器并配置标示符：
[root@node232 group_Replication]# mysql -uroot -p -h127.0.0.1 -P3311
Enter password:
root@localhost[(none)]>prompt 1>;
PROMPT set to '1>'
1>
5.启动server1的组复制：
1>set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)
1>start group_replication;
Query OK, 0 rows affected (1.22 sec)
1>set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)
6.通过查询performance schema的replication_group_member表来验证server1是否是组成员：
1>select * from performance_schema.replication_group_members\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 9ffbf924-e1d8-11eb-a2d6-000c29cb49a4
MEMBER_HOST: localhost
MEMBER_PORT: 3311
MEMBER_STATE: ONLINE
1 row in set (0.00 sec)
ERROR:
No query specified
7.在server2,server3,server4开启组复制：
server2:
[root@node232 ~]# mysql -uroot -h127.0.0.1 -P3312 -p
Enter password:
root@localhost[(none)]>prompt 2>;
PROMPT set to '2>'
2>start group_replication;
Query OK, 0 rows affected (5.57 sec)
server3:
[root@node232 ~]# mysql -uroot -h127.0.0.1 -P3313 -p
root@localhost[(none)]>prompt 3>;
PROMPT set to '3>'
3>start group_replication;
Query OK, 0 rows affected (2.98 sec)
server4:
[root@node232 ~]# mysql -uroot -h127.0.0.1 -P3314 -p
root@localhost[(none)]>prompt 4>;
PROMPT set to '4>'
4>start group_replication;
Query OK, 0 rows affected (2.30 sec)
8.进入server1的客户端，通过performance schema的表replication_group_members来显示组成员信息：
4>select * from performance_schema.replication_group_members\g;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 9ffbf924-e1d8-11eb-a2d6-000c29cb49a4 | localhost | 3311 | ONLINE |
| group_replication_applier | a2365b2a-e1d8-11eb-a46f-000c29cb49a4 | localhost | 3312 | ONLINE |
| group_replication_applier | a48d962d-e1d8-11eb-a452-000c29cb49a4 | localhost | 3313 | ONLINE |
| group_replication_applier | a6dba605-e1d8-11eb-a5b1-000c29cb49a4 | localhost | 3314 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
4 rows in set (0.00 sec)
ERROR:
No query specified
9.在server1的mysql客户端,查看组复制的master的UUID:
1>select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
+----------------+
| variable_value |
+----------------+
| |
+----------------+
1 row in set (0.00 sec)
上面的值为空，表示组复制运行在多主模式下
10.在server4数据库下，插入一条新数据：
4>insert into testDB.tb1 values(2,'Multi-primary mode.');
Query OK, 1 row affected (0.06 sec)
11.在server1,server2,server3验证数据是否同步：
server1:
1>select * from testDB.tb1;
+----+---------------------+
| c1 | c2 |
+----+---------------------+
| 1 | Group replication. |
| 2 | Multi-primary mode. |
+----+---------------------+
2 rows in set (0.01 sec)
server2:
2>select * from testDB.tb1;
+----+---------------------+
| c1 | c2 |
+----+---------------------+
| 1 | Group replication. |
| 2 | Multi-primary mode. |
+----+---------------------+
2 rows in set (0.00 sec)
server3:
3>select * from testDB.tb1;
+----+---------------------+
| c1 | c2 |
+----+---------------------+
| 1 | Group replication. |
| 2 | Multi-primary mode. |
+----+---------------------+
2 rows in set (0.00 sec)
总结：
以上说明，4台服务器已配置成组复制的多组模式，可以在任何一台服务器实现DML
微信公众号视频：https://mp.weixin.qq.com/s/zZa8O8dunv_ymfQGOfEnDw
