MySQL组复制的几个常见问题以及解决办法

MySQL组复制提供的功能很强大，但是有时会出现一些问题，或者使用上存在一些限制主要包括： 一.组复制的限制: (a)存储引擎只能是InnoDB。 (b)二进制日志格式只支持ROW格式。 (c)只支持使用GTID模式。 (d)每个分组最多只支持9个成员节点。   二.数据表必须有主键。 mysql> create table test (name varchar(100)); Query OK, 0 rows affected (0.05 sec)   mysql> insert into test values( now()),(now()); ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.   查看日志： 2017-08-19T06:23:22.253181Z 13 [ERROR] Plugin group_replication reported: 'Table test does not have any PRIMARY KEY. This is not compatible with Group Replication' 2017-08-19T06:24:18.493848Z 13 [ERROR] Plugin group_replication reported: 'Table test does not have any PRIMARY KEY. This is not compatible with Group Replication'   解决办法： 创建有主键的数据表。 mysql> create table test ( name varchar(100) primary key); Query OK, 0 rows affected (0.02 sec)   mysql> insert into test (name) values ('001'); Query OK, 1 row affected (0.02 sec)   mysql> insert into test (name) values ('002'); Query OK, 1 row affected (0.01 sec)   mysql> insert into test (name) values ('003'); Query OK, 1 row affected (0.00 sec)   mysql> insert into test (name) values (now()); Query OK, 1 row affected (0.01 sec) 三.数据库已经存在: 日志中出现了数据库已经存在而无法创建数据库的错误。 2017-08-19T06:51:50.784471Z 28 [ERROR] Slave SQL for channel 'group_replication_recovery': Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test', Error_code: 1007 2017-08-19T06:51:50.784523Z 28 [Warning] Slave: Can't create database 'test'; database exists Error_code: 1007 2017-08-19T06:51:50.784530Z 28 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000001' position 1082 解决办法： mysql> stop group_replication; Query OK, 0 rows affected (9.43 sec) mysql> drop database test; ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement mysql> set global super_read_only=0; Query OK, 0 rows affected (0.00 sec) mysql> drop database test; Query OK, 0 rows affected (0.00 sec) mysql> set global super_read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; Query OK, 0 rows affected (0.00 sec) mysql> start group_replication; Query OK, 0 rows affected (3.19 sec) 为了防止在MySQL的节点在启动时出现此问题，可将下面的配置加入MySQL配置文件。 loose-group_replication_allow_local_disjoint_gtids_join=on