ITPub博客

首页 > 数据库 > MySQL > MySQL组复制的几个常见问题以及解决办法

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

MySQL 作者:chenfeng 时间:2018-10-09 10:20:42 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15498/viewspace-2215643/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
交流MySQL,MongoDB和Redis技术。 微信或QQ:410294

注册时间:2015-12-07

  • 博文量
    756
  • 访问量
    2191329