mysql 5.7 gtid主从同步错误修复

post by rocdk890 / 2018-3-22 10:49 Thursday linux技术

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

系统: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