mysql5.7 1032 1062自动修复

随着mysql MS批量式线上部署，带来的问题也较多，今天我们就介绍一下最棘手的一个问题：复制中断，我们该怎么办？

遇到最多的是1032和1062错误，至于错误的原理，这里不想过多分析：记住一条即可，mysql是基于逻辑的复制。很多网上教程使用slave_skip_errors进行跳过（这是掩耳盗铃）；在我们的环境里是禁止的。手工修复，这里也不废话：结合业务和报错位置利用mysqlbinlog工具找出报错位置进行解决即可，速度太慢；结合历史的操作中，整个恢复过程估计需要30min-1h，还是需要熟悉业务。为了解决这个问题，利用python写了一个复制报错的1032和1062自动化修复脚本

脚本见https://github.com/trsenzhang/AOMmysql/项目中的replCheckStatus.py

感觉还可以请:star一下，哈哈

满足如下条件可自动修复1032和1062导致复制中断的错误：
1.数据库的binlog是基于ROW格式
2.binlog版本为v4
3.数据库的版本为5.7
4.脚本运行在slave端
5.在进行大批量的数据修复时，拉去master端binlog时，会导致MS之间网络带宽消耗
6.实际上生产中，建议自己的测试环境中测试后，

mysql> select * from sbtest1 where id>=99800;
+-------+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+-------+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 99811 | 100 | 90165491267-07275869234-19011205074-26444610383-72122436809-46694151734-03204627361-95242202339-88068233961-87971936484 | 89276814852-51067567943-14894208093-22203139505-76590959776 |
| 99831 | 100 | 33781043000-25534118395-26582187843-30125017791-34879940111-81915141800-78796130797-48796519170-69301562901-89603068188 | 21473874594-31729191357-49416492581-96928763763-88555030957 |
| 99850 | 100 | 52997681569-07256577082-70696020254-58728313014-64278657880-11737495601-96272462709-19907083505-55161413902-04166827371 | 03943355714-15233939093-77191977933-80871069856-13998664867 |
| 99860 | 100 | 22149089534-99257768432-42374479937-27983548918-34777085571-60122511474-68156039839-93575679803-25858461064-38693071750 | 61706698622-03408243288-81616510318-77675951439-83647865812 |
| 99866 | 100 | 97130276471-62038331816-81543705125-03598493850-89399270787-00769389132-67423665424-86329756265-55055790846-35049166232 | 29710875742-96213160947-25350860664-06705248824-91892213385 |
| 99887 | 100 | 69817170669-41710523781-33204590013-59847767425-06017308425-48878655484-03102622366-86054674368-67448987765-56237448865 | 77033115449-02879844350-86983597796-12759679132-70415094822 |
| 99897 | 100 | 37254441279-25082190545-71567616264-11611592521-17733316489-36596964903-46764438796-48309081109-47254822344-70662900915 | 45935391601-84007613456-49474460762-07492663463-61332150729 |
| 99949 | 100 | | 34580886030-82638848912-17448500901-05181865304-45488435594 |
| 99968 | 100 | | 00859334580-40505270334-09446901828-56582294486-93512954382 |
+-------+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+

mysql> delete from sbtest1 where id>=99800;
Query OK, 9 rows affected (0.02 sec)

mysql> select * from sbtest1 where id>=99800;
Empty set (0.00 sec)

1.模拟一条数据删除操作

update trsen.sbtest1 set c='111111111111' where id=99968;
delete from trsen.sbtest1 where id=99949;

利用脚本修复后结果如下:

slave端数据
mysql> select * from sbtest1 where id>=99800;
+-------+-----+--------------+-------------------------------------------------------------+
| id | k | c | pad |
+-------+-----+--------------+-------------------------------------------------------------+
| 99968 | 100 | 111111111111 | 00859334580-40505270334-09446901828-56582294486-93512954382 |
+-------+-----+--------------+-------------------------------------------------------------+

master端数据:

因为99811-99897区间数据还没有进行操作，主从复制不会出现sql_thread异常
mysql> select * from sbtest1 where id>=99800;
+-------+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+-------+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 99811 | 100 | 90165491267-07275869234-19011205074-26444610383-72122436809-46694151734-03204627361-95242202339-88068233961-87971936484 | 89276814852-51067567943-14894208093-22203139505-76590959776 |
| 99831 | 100 | 33781043000-25534118395-26582187843-30125017791-34879940111-81915141800-78796130797-48796519170-69301562901-89603068188 | 21473874594-31729191357-49416492581-96928763763-88555030957 |
| 99850 | 100 | 52997681569-07256577082-70696020254-58728313014-64278657880-11737495601-96272462709-19907083505-55161413902-04166827371 | 03943355714-15233939093-77191977933-80871069856-13998664867 |
| 99860 | 100 | 22149089534-99257768432-42374479937-27983548918-34777085571-60122511474-68156039839-93575679803-25858461064-38693071750 | 61706698622-03408243288-81616510318-77675951439-83647865812 |
| 99866 | 100 | 97130276471-62038331816-81543705125-03598493850-89399270787-00769389132-67423665424-86329756265-55055790846-35049166232 | 29710875742-96213160947-25350860664-06705248824-91892213385 |
| 99887 | 100 | 69817170669-41710523781-33204590013-59847767425-06017308425-48878655484-03102622366-86054674368-67448987765-56237448865 | 77033115449-02879844350-86983597796-12759679132-70415094822 |
| 99897 | 100 | 37254441279-25082190545-71567616264-11611592521-17733316489-36596964903-46764438796-48309081109-47254822344-70662900915 | 45935391601-84007613456-49474460762-07492663463-61332150729 |
| 99968 | 100 | 111111111111 | 00859334580-40505270334-09446901828-56582294486-93512954382 |
+-------+-----+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+

2.模拟批量删除和更新

update trsen.sbtest1 set c='222222222' where id>=99811 and id<=99850;
delete from trsen.sbtest1 where id >=99860 and id <=99968

slave端数据:

mysql> select * from sbtest1 where id>=99800;
+-------+-----+-----------+-------------------------------------------------------------+
| id | k | c | pad |
+-------+-----+-----------+-------------------------------------------------------------+
| 99811 | 100 | 222222222 | 89276814852-51067567943-14894208093-22203139505-76590959776 |
| 99831 | 100 | 222222222 | 21473874594-31729191357-49416492581-96928763763-88555030957 |
| 99850 | 100 | 222222222 | 03943355714-15233939093-77191977933-80871069856-13998664867 |
+-------+-----+-----------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

master端数据

mysql> select * from sbtest1 where id>=99800;
+-------+-----+-----------+-------------------------------------------------------------+
| id | k | c | pad |
+-------+-----+-----------+-------------------------------------------------------------+
| 99811 | 100 | 222222222 | 89276814852-51067567943-14894208093-22203139505-76590959776 |
| 99831 | 100 | 222222222 | 21473874594-31729191357-49416492581-96928763763-88555030957 |
| 99850 | 100 | 222222222 | 03943355714-15233939093-77191977933-80871069856-13998664867 |
+-------+-----+-----------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

测试中日志信息如下：

[root@dzst151 log]# more auto_check_repl_status_repair-2019-07-02.log 
2019-07-02 16:00:17.522898 : INFO : The platform check pass.
2019-07-02 16:00:17.522898 : INFO : get_rpl_mode r -> ON
2019-07-02 16:00:17.522898 : INFO : rpl_mode 1 
2019-07-02 16:00:17.522898 : INFO : 1032
2019-07-02 16:00:17.522898 : INFO : multi dml have SMTM_END_F,is ok.
2019-07-02 16:00:17.522898 : INFO : end_log_pos :2646
2019-07-02 16:00:17.522898 : INFO : row_recode : ['---line---', '### DELETE `trsen`.`sbtest1`', '### WHERE', '### @1=99949', '### @2=100', "### @3=''", "### @4='34580886030-82638848912-17448500901-05181865304-45488435594'", '
---line---']
2019-07-02 16:00:17.522898 : INFO : sql_list : ['DELETE `trsen`.`sbtest1`', 'WHERE', 'id=99949', 'and k=100', "and c=''", "and pad='34580886030-82638848912-17448500901-05181865304-45488435594'", '---line---']
2019-07-02 16:00:17.522898 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99949 and k=100 and c='' and pad='34580886030-82638848912-17448500901-05181865304-45488435594';
2019-07-02 16:00:17.522898 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99949 , 100 , '' , '34580886030-82638848912-17448500901-05181865304-45488435594');
2019-07-02 16:00:17.522898 : INFO : slave repaired.
2019-07-02 16:01:10.580024 : INFO : The platform check pass.
2019-07-02 16:01:10.580024 : INFO : get_rpl_mode r -> ON
2019-07-02 16:01:10.580024 : INFO : rpl_mode 1 
2019-07-02 16:01:10.580024 : INFO : 1032
2019-07-02 16:01:10.580024 : INFO : multi dml have SMTM_END_F,is ok.
2019-07-02 16:01:10.580024 : INFO : end_log_pos :3138
2019-07-02 16:01:10.580024 : INFO : row_recode : ['---line---', '### UPDATE `trsen`.`sbtest1`', '### WHERE', '### @1=99968', '### @2=100', "### @3=''", "### @4='00859334580-40505270334-09446901828-56582294486-93512954382'", '
---line---']
2019-07-02 16:01:10.580024 : INFO : sql_list : ['UPDATE `trsen`.`sbtest1`', 'WHERE', 'id=99968', 'and k=100', "and c=''", "and pad='00859334580-40505270334-09446901828-56582294486-93512954382'", '---line---']
2019-07-02 16:01:10.580024 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99968 and k=100 and c='' and pad='00859334580-40505270334-09446901828-56582294486-93512954382';
2019-07-02 16:01:10.580024 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99968 , 100 , '' , '00859334580-40505270334-09446901828-56582294486-93512954382');
2019-07-02 16:01:10.580024 : INFO : slave repaired.
2019-07-02 16:05:30.649844 : INFO : The platform check pass.
2019-07-02 16:05:30.649844 : INFO : get_rpl_mode r -> ON
2019-07-02 16:05:30.649844 : INFO : rpl_mode 1 
2019-07-02 16:05:30.649844 : INFO : 1032
2019-07-02 16:05:30.649844 : INFO : multi dml have SMTM_END_F,is ok.
2019-07-02 16:05:30.649844 : INFO : end_log_pos :4303
2019-07-02 16:05:30.649844 : INFO : row_recode : ['---line---', '### UPDATE `trsen`.`sbtest1`', '### WHERE', '### @1=99811', '### @2=100', "### @3='90165491267-07275869234-19011205074-26444610383-72122436809-46694151734-0320462
7361-95242202339-88068233961-87971936484'", "### @4='89276814852-51067567943-14894208093-22203139505-76590959776'", '---line---', '### UPDATE `trsen`.`sbtest1`', '### WHERE', '### @1=99831', '### @2=100', "### @3='33781043000
-25534118395-26582187843-30125017791-34879940111-81915141800-78796130797-48796519170-69301562901-89603068188'", "### @4='21473874594-31729191357-49416492581-96928763763-88555030957'", '---line---', '### UPDATE `trsen`.`sbtest1`', '
### WHERE', '### @1=99850', '### @2=100', "### @3='52997681569-07256577082-70696020254-58728313014-64278657880-11737495601-96272462709-19907083505-55161413902-04166827371'", "### @4='03943355714-15233939093-77191977933-808710
69856-13998664867'", '---line---']
2019-07-02 16:05:30.649844 : INFO : sql_list : ['UPDATE `trsen`.`sbtest1`', 'WHERE', 'id=99811', 'and k=100', "and c='90165491267-07275869234-19011205074-26444610383-72122436809-46694151734-03204627361-95242202339-88068233961-8797193
6484'", "and pad='89276814852-51067567943-14894208093-22203139505-76590959776'", '---line---', 'UPDATE `trsen`.`sbtest1`', 'WHERE', 'id=99831', 'and k=100', "and c='33781043000-25534118395-26582187843-30125017791-34879940111-81915141
800-78796130797-48796519170-69301562901-89603068188'", "and pad='21473874594-31729191357-49416492581-96928763763-88555030957'", '---line---', 'UPDATE `trsen`.`sbtest1`', 'WHERE', 'id=99850', 'and k=100', "and c='52997681569-072565770
82-70696020254-58728313014-64278657880-11737495601-96272462709-19907083505-55161413902-04166827371'", "and pad='03943355714-15233939093-77191977933-80871069856-13998664867'", '---line---']
2019-07-02 16:05:30.649844 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99811 and k=100 and c='90165491267-07275869234-19011205074-26444610383-72122436809-46694151734-03204627361-95242202339-88068233961-879719
36484' and pad='89276814852-51067567943-14894208093-22203139505-76590959776';
2019-07-02 16:05:30.649844 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99811 , 100 , '90165491267-07275869234-19011205074-26444610383-72122436809-46694151734-03204627361-95242202339-88068
233961-87971936484' , '89276814852-51067567943-14894208093-22203139505-76590959776');
2019-07-02 16:05:30.649844 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99831 and k=100 and c='33781043000-25534118395-26582187843-30125017791-34879940111-81915141800-78796130797-48796519170-69301562901-896030
68188' and pad='21473874594-31729191357-49416492581-96928763763-88555030957';
2019-07-02 16:05:30.649844 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99831 , 100 , '33781043000-25534118395-26582187843-30125017791-34879940111-81915141800-78796130797-48796519170-69301
562901-89603068188' , '21473874594-31729191357-49416492581-96928763763-88555030957');
2019-07-02 16:05:30.649844 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99850 and k=100 and c='52997681569-07256577082-70696020254-58728313014-64278657880-11737495601-96272462709-19907083505-55161413902-041668
27371' and pad='03943355714-15233939093-77191977933-80871069856-13998664867';
2019-07-02 16:05:30.649844 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99850 , 100 , '52997681569-07256577082-70696020254-58728313014-64278657880-11737495601-96272462709-19907083505-55161
413902-04166827371' , '03943355714-15233939093-77191977933-80871069856-13998664867');
2019-07-02 16:05:30.649844 : INFO : get_rpl_mode r -> ON
2019-07-02 16:05:30.649844 : INFO : rpl_mode 1 
2019-07-02 16:05:30.649844 : INFO : 1032
2019-07-02 16:05:30.649844 : INFO : multi dml have SMTM_END_F,is ok.
2019-07-02 16:05:30.649844 : INFO : end_log_pos :5489
2019-07-02 16:05:30.649844 : INFO : row_recode : ['---line---', '### DELETE `trsen`.`sbtest1`', '### WHERE', '### @1=99860', '### @2=100', "### @3='22149089534-99257768432-42374479937-27983548918-34777085571-60122511474-6815603
9839-93575679803-25858461064-38693071750'", "### @4='61706698622-03408243288-81616510318-77675951439-83647865812'", '---line---', '### DELETE `trsen`.`sbtest1`', '### WHERE', '### @1=99866', '### @2=100', "### @3='97130276471
-62038331816-81543705125-03598493850-89399270787-00769389132-67423665424-86329756265-55055790846-35049166232'", "### @4='29710875742-96213160947-25350860664-06705248824-91892213385'", '---line---', '### DELETE `trsen`.`sbtest1`', '
### WHERE', '### @1=99887', '### @2=100', "### @3='69817170669-41710523781-33204590013-59847767425-06017308425-48878655484-03102622366-86054674368-67448987765-56237448865'", "### @4='77033115449-02879844350-86983597796-127596
79132-70415094822'", '---line---', '### DELETE `trsen`.`sbtest1`', '### WHERE', '### @1=99897', '### @2=100', "### @3='37254441279-25082190545-71567616264-11611592521-17733316489-36596964903-46764438796-48309081109-47254822344-
70662900915'", "### @4='45935391601-84007613456-49474460762-07492663463-61332150729'", '---line---', '### DELETE `trsen`.`sbtest1`', '### WHERE', '### @1=99968', '### @2=100', "### @3='111111111111'", "### @4='00859334580-4
0505270334-09446901828-56582294486-93512954382'", '---line---']
2019-07-02 16:05:30.649844 : INFO : sql_list : ['DELETE `trsen`.`sbtest1`', 'WHERE', 'id=99860', 'and k=100', "and c='22149089534-99257768432-42374479937-27983548918-34777085571-60122511474-68156039839-93575679803-25858461064-3869307
1750'", "and pad='61706698622-03408243288-81616510318-77675951439-83647865812'", '---line---', 'DELETE `trsen`.`sbtest1`', 'WHERE', 'id=99866', 'and k=100', "and c='97130276471-62038331816-81543705125-03598493850-89399270787-00769389
132-67423665424-86329756265-55055790846-35049166232'", "and pad='29710875742-96213160947-25350860664-06705248824-91892213385'", '---line---', 'DELETE `trsen`.`sbtest1`', 'WHERE', 'id=99887', 'and k=100', "and c='69817170669-417105237
81-33204590013-59847767425-06017308425-48878655484-03102622366-86054674368-67448987765-56237448865'", "and pad='77033115449-02879844350-86983597796-12759679132-70415094822'", '---line---', 'DELETE `trsen`.`sbtest1`', 'WHERE', 'id=998
97', 'and k=100', "and c='37254441279-25082190545-71567616264-11611592521-17733316489-36596964903-46764438796-48309081109-47254822344-70662900915'", "and pad='45935391601-84007613456-49474460762-07492663463-61332150729'", '---line---
', 'DELETE `trsen`.`sbtest1`', 'WHERE', 'id=99968', 'and k=100', "and c='111111111111'", "and pad='00859334580-40505270334-09446901828-56582294486-93512954382'", '---line---']
2019-07-02 16:05:30.649844 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99860 and k=100 and c='22149089534-99257768432-42374479937-27983548918-34777085571-60122511474-68156039839-93575679803-25858461064-386930
71750' and pad='61706698622-03408243288-81616510318-77675951439-83647865812';
2019-07-02 16:05:30.649844 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99860 , 100 , '22149089534-99257768432-42374479937-27983548918-34777085571-60122511474-68156039839-93575679803-25858
461064-38693071750' , '61706698622-03408243288-81616510318-77675951439-83647865812');
2019-07-02 16:05:30.649844 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99866 and k=100 and c='97130276471-62038331816-81543705125-03598493850-89399270787-00769389132-67423665424-86329756265-55055790846-350491
66232' and pad='29710875742-96213160947-25350860664-06705248824-91892213385';
2019-07-02 16:05:30.649844 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99866 , 100 , '97130276471-62038331816-81543705125-03598493850-89399270787-00769389132-67423665424-86329756265-55055
790846-35049166232' , '29710875742-96213160947-25350860664-06705248824-91892213385');
2019-07-02 16:05:30.649844 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99887 and k=100 and c='69817170669-41710523781-33204590013-59847767425-06017308425-48878655484-03102622366-86054674368-67448987765-562374
48865' and pad='77033115449-02879844350-86983597796-12759679132-70415094822';
2019-07-02 16:05:30.649844 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99887 , 100 , '69817170669-41710523781-33204590013-59847767425-06017308425-48878655484-03102622366-86054674368-67448
987765-56237448865' , '77033115449-02879844350-86983597796-12759679132-70415094822');
2019-07-02 16:05:30.649844 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99897 and k=100 and c='37254441279-25082190545-71567616264-11611592521-17733316489-36596964903-46764438796-48309081109-47254822344-706629
00915' and pad='45935391601-84007613456-49474460762-07492663463-61332150729';
2019-07-02 16:05:30.649844 : INFO : slave execute sql: error:1032 --run SQL: INSERT INTO `trsen`.`sbtest1` VALUES( 99897 , 100 , '37254441279-25082190545-71567616264-11611592521-17733316489-36596964903-46764438796-48309081109-47254
822344-70662900915' , '45935391601-84007613456-49474460762-07492663463-61332150729');
2019-07-02 16:05:30.649844 : INFO : will execute sql: SELECT 1 from `trsen`.`sbtest1` WHERE id=99968 and k=100 and c='111111111111' and pad='00859334580-40505270334-09446901828-56582294486-93512954382';
2019-07-02 16:05:30.649844 : INFO : slave repaired.