mysql gitd 数据结构同步失败_mysql 5.7 gtid主从同步错误修复

错误发生的原因是我们在从库上插入了一条数据,又马上在主库上插入相同的数据,这样就造成主从不同步了.

系统:centos 7.x(64位)

软件版本:mysql 5.7(64位)

以下错误都是事后找的:

第一个错误:

2018-03-21T08:39:48.606372Z 8 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction 'c4e174e2-1368-11e7-8120-00163e12b9a9:401338043' at master log master-bin.001906, end_log_pos 287308541; Could not execute Write_rows event on table qx_lottery_g2.lot_audit_log; Duplicate entry '1074' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.001906, end_log_pos 287308541, Error_code: 1062

解决办法:

1.查看表结构:

[root@localhost][(none)]> desc qx_lottery_g2.lot_audit_log;

+-------------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------------+--------------+------+-----+---------+-------+

| _id | bigint(20) | NO | PRI | NULL | |

| _company_id | bigint(20) | YES | | NULL | |

| _open_id | varchar(255) | YES | | NULL | |

| _name | varchar(255) | YES | | NULL | |

| _apply_open_id | varchar(255) | YES | | NULL | |

| _apply_name | varchar(255) | YES | | NULL | |

| _apply_wx_img | varchar(255) | YES | | NULL | |

| _telephone | varchar(255) | YES | | NULL | |

| _store_id | bigint(20) | YES | | NULL | |

| _street_name | varchar(255) | YES | | NULL | |

| _area_name | varchar(255) | YES | | NULL | |

| _store_name | varchar(255) | YES | | NULL | |

| _status | int(11) | YES | | NULL | |

| _create_time | bigint(20) | YES | | NULL | |

| _product_id | bigint(20) | YES | | NULL | |

| _apply_product_id | bigint(20) | YES | | NULL | |

| _member_id | bigint(20) | YES | | NULL | |

| _apply_member_id | bigint(20) | YES | | NULL | |

| _apply_wx_name | varchar(100) | YES | | NULL | |

+-------------------+--------------+------+-----+---------+-------+

19 rows in set (0.00 sec)

19 rows in set (0.00 sec)

2.删除重复的主键

[root@localhost][qx_lottery_g2]>stop slave;

[root@localhost][qx_lottery_g2]>delete from lot_audit_log where _id=1074;

[root@localhost][qx_lottery_g2]>start slave;

第二个错误:

Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'c4e174e2-1368-11e7-8120-00163e12b9a9:401340325' at master log master-bin.001906, end_log_pos 295832505. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

解决办法:

stop slave;

Set @@SESSION.GTID_NEXT='c4e174e2-1368-11e7-8120-00163e12b9a9:401340325';

Begin;

Commit;

Set @@SESSION.GTID_NEXT = AUTOMATIC;

start slave;

show slave status\G

看是否还有错误,如果还报错误的话,再执行,我们这里就报错了:

Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'c4e174e2-1368-11e7-8120-00163e12b9a9:401341631' at master log master-bin.001906, end_log_pos 295832505. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

再执行了一次:

stop slave;

Set @@SESSION.GTID_NEXT='c4e174e2-1368-11e7-8120-00163e12b9a9:401341631';

Begin;

Commit;

Set @@SESSION.GTID_NEXT = AUTOMATIC;

start slave;

show slave status\G

这次就没有报错了,mysql主从正常同步了.

ps:

如果你想实时查看mysql主从同步状态,可以用shell脚本监控mysql主从同步状态这个脚本.

夜空- 本站版权

1、本站所有主题由该文章作者发表，该文章作者与夜空享有文章相关版权

2、其他单位或个人使用、转载或引用本文时必须同时征得该文章作者和夜空的同意

3、本帖部分内容转载自其它媒体，但并不代表本站赞同其观点和对其真实性负责

4、如本帖侵犯到任何版权问题，请立即告知本站，本站将及时予与删除并致以最深的歉意

5、原文链接：blog.slogra.com/post-720.html