配置MySQL Group Replication单主模式踩过的坑

问题:
一台从库之前使用gtid复制,没有加入到Group Replication组中,后来在加入组中时,start group_replication启动,日志报错
2018-02-01T06:33:26.894113Z 342 [Note] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_recovery‘ executed‘. Previous state master_host=‘<NULL>‘, master_port= 0, master_log_file=‘‘, master_
log_pos= 4, master_bind=‘‘. New state master_host=‘PS03‘, master_port= 3306, master_log_file=‘‘, master_log_pos= 4, master_bind=‘‘.
2018-02-01T06:33:26.904897Z 342 [Note] Plugin group_replication reported: ‘Establishing connection to a group replication recovery donor 97347859-dfe0-11e7-90cf-000c2911d0be at PS03 port: 3
306.‘
2018-02-01T06:33:26.905254Z 344 [Note] Slave I/O thread: Start asynchronous replication to master ‘slave@PS03:3306‘ in log ‘FIRST‘ at position 4
2018-02-01T06:33:26.905310Z 344 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using
the USER and PASSWORD connection options for START SLAVE; see the ‘START SLAVE Syntax‘ in the MySQL Manual for more information.
2018-02-01T06:33:26.911486Z 344 [Note] Slave I/O thread for channel ‘group_replication_recovery‘: connected to master ‘slave@PS03:3306‘,replication started in log ‘FIRST‘ at position 4
2018-02-01T06:33:26.933345Z 345 [Note] Slave SQL thread for channel ‘group_replication_recovery‘ initialized, starting replication in log ‘FIRST‘ at position 0, relay log ‘./PS04-relay-bin-
group_replication_recovery.000001‘ position: 4
2018-02-01T06:33:26.988936Z 346 [ERROR] Slave SQL for channel ‘group_replication_recovery‘: Worker 1 failed executing transaction ‘18c01a46-df39-11e7-b98f-000c2989d7c7:2‘ at master log mysq
l-bin.000001, end_log_pos 399; Could not execute Write_rows event on table sakura.t1; Duplicate entry ‘121071‘ for key ‘PRIMARY‘, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the
event‘s master log FIRST, end_log_pos 399, Error_code: 1062
2018-02-01T06:33:26.991140Z 345 [Warning] Slave SQL for channel ‘group_replication_recovery‘: ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent
state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to
examine your data (see documentation for details). Error_code: 1756
2018-02-01T06:33:26.991157Z 345 [Note] Slave SQL thread for channel ‘group_replication_recovery‘ exiting, replication stopped in log ‘mysql-bin.000001‘ at position 154
2018-02-01T06:33:26.991277Z 342 [Note] Plugin group_replication reported: ‘Terminating existing group replication donor connection and purging the corresponding logs.‘
2018-02-01T06:33:26.993179Z 344 [Note] Slave I/O thread exiting for channel ‘group_replication_recovery‘, read up to log ‘mysql-bin.000001‘, position 126928
2018-02-01T06:33:27.000685Z 342 [Note] ‘CHANGE MASTER TO FOR CHANNEL ‘group_replication_recovery‘ executed‘. Previous state master_host=‘PS03‘, master_port= 3306, master_log_file=‘‘, master
_log_pos= 4, master_bind=‘‘. New state master_host=‘<NULL>‘, master_port= 0, master_log_file=‘‘, master_log_pos= 4, master_bind=‘‘.
2018-02-01T06:33:27.006279Z 342 [Note] Plugin group_replication reported: ‘Retrying group recovery connection with another donor. Attempt 2/10‘

原因:配置CHANGE MASTER TO MASTER_USER=‘slave‘,MASTER_PASSWORD=‘slave‘ FOR CHANNEL ‘group_replication_recovery‘加入组时,没指定MASTER_LOG_FILE,但是指定MASTER_LOG_FILE又会报其他关于group_replication_recovery的错误。所以同步是根据主库的第一个binlog同步的,如上述错误:
Could not execute Write_rows event on table sakura.t1; Duplicate entry ‘121071‘ for key ‘PRIMARY‘, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
the event‘s master log FIRST, end_log_pos 399, Error_code: 1062
而之前使用的gtid同步过,所以会重复执行同步,处理方法,停止group_replication服务,删除这条记录,重新启动group_replication服务
mysql> stop group_replication;
delete from sakura.t1 where id >=121071;
set global group_replication_bootstrap_group=off;
set global group_replication_allow_local_disjoint_gtids_join=on;
start group_replication;
————————————————
原文链接:https://blog.csdn.net/mic0601/article/details/79231632

配置MySQL Group Replication单主模式踩过的坑

上一篇:Mysql语句


下一篇:利用反射将数据库记录映射到JavaBean