传统复制常见错误及填坑方法_could not execute path not found-CSDN博客

本文链接：https://blog.csdn.net/wukong_666/article/details/72678519

传统复制常见错误及填坑方法
环境
1、表不存在导致插入更新失败
1.1、模拟复制错误产生
1.1.1、生成测试表sbtest.test
1.1.2、在备库上查看是否同步test表
1.1.3、主库上往test表中插入数据
1.1.4、查看备库复制情况
1.2、复制错误跳过
1.2.1、主库上查看event执行信息
1.2.2、结合主备信息跳过错误
1.2.3、手动补上数据
2、ERROR 1062 从库插入了数据，主键重复
2.1、模拟错误产生
2.1.1、创建测试表sbtest.shuihuchuan
2.1.2、备库上查看同步情况
2.1.3、备库上插入数据
2.1.4、主库上插入数据
2.1.5、查看备库复制情况
2.2、主键重复错误解决
2.2.1、备库上删除重复主键的记录
3、ERROR 1032 删除或更新数据，从库找不到记录
3.1、Could not execute Delete_rows错误
3.1.1、模拟复制报错
3.1.2、记录已经不存在，直接跳过错误就可以
3.2、Could not execute Update_rows错误
3.2.1、模拟复制报错
3.2.2、从库恢复不存在的记录，并解决错误

传统复制常见错误及填坑方法

环境

主库：IP：192.168.1.21；版本：5.7.18
备库：IP：192.168.1.128；版本：5.6.36
主库测试库: sbtest
备库配置:Replicate_Do_DB: sbtest

1、表不存在导致插入更新失败

1.1、模拟复制错误产生

1.1.1、生成测试表sbtest.test

在指定的复制库下生成测试表test。但，不在sbtest库下操作，而选择了在mysql库下操作生成sbtest.test

 
 #主库上操作
mysql> use mysql
Database changed
mysql> create table sbtest.test ( id int );
Query OK, 0 rows affected (0.08 sec)
mysql> use sbtest;
Database changed
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sanguoyanyi      |
| test             |
| xiyouji          |
+------------------+
3 rows in set (0.00 sec)

1.1.2、在备库上查看是否同步test表

 
 #备库上操作
root@localhost : sbtest 03:33:13> use sbtest;
Database changed
root@localhost : sbtest 03:41:18> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sanguoyanyi      |
| xiyouji          |
+------------------+
2 rows in set (0.00 sec)
root@localhost : sbtest 03:41:43> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 331
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 463
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 331
              Relay_Log_Space: 636
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
#这也是为什么不推荐使用replicate-do-db, replicate-ignore-db配置参数的原因。原因是设置replicate_do_db或replicate_ignore_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句在Slave上会被忽略。

1.1.3、主库上往test表中插入数据

 
 mysql> insert into test values (2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

1.1.4、查看备库复制情况

 
 root@localhost : sbtest 04:05:27> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 590
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 463
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1146
                   Last_Error: Error executing row event: 'Table 'sbtest.test' doesn't exist'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 331
              Relay_Log_Space: 895
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error executing row event: 'Table 'sbtest.test' doesn't exist'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 170522 04:05:17
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
#报错显示表sbtest.test不存在

1.2、复制错误跳过

1.2.1、主库上查看event执行信息

 
 mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000026 |      590 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000026';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                             |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------+
| mysql-bin.000026 |   4 | Format_desc    |     12001 |         123 | Server ver: 5.7.18-log, Binlog ver: 4            |
| mysql-bin.000026 | 123 | Previous_gtids |     12001 |         154 |                                                  |
| mysql-bin.000026 | 154 | Anonymous_Gtid |     12001 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000026 | 219 | Query          |     12001 |         331 | use `mysql`; create table sbtest.test ( id int ) |
| mysql-bin.000026 | 331 | Anonymous_Gtid |     12001 |         396 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| mysql-bin.000026 | 396 | Query          |     12001 |         470 | BEGIN                                            |
| mysql-bin.000026 | 470 | Table_map      |     12001 |         519 | table_id: 334 (sbtest.test)                      |
| mysql-bin.000026 | 519 | Write_rows     |     12001 |         559 | table_id: 334 flags: STMT_END_F                  |
| mysql-bin.000026 | 559 | Xid            |     12001 |         590 | COMMIT /* xid=1091 */                            |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------+
9 rows in set (0.00 sec)

1.2.2、结合主备信息跳过错误

 
 #show slave status\G显示的有用信息
Master_Log_File: mysql-bin.000026
Read_Master_Log_Pos: 590
Exec_Master_Log_Pos: 331

 
 #结合主库的show binlog events in 'mysql-bin.000026'以及备库的show slave status\G可知需要跳过519（insert）以及559（commit）两个事务
root@localhost : sbtest 04:29:56> stop slave;
Query OK, 0 rows affected (0.01 sec)
root@localhost : sbtest 04:30:41> set global sql_slave_skip_counter=2;
Query OK, 0 rows affected (0.00 sec)
root@localhost : sbtest 04:30:44> start slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost : sbtest 04:30:47> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 590
               Relay_Log_File: mysql-relay-bin.000006
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 590
              Relay_Log_Space: 625
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

1.2.3、手动补上数据

 
 root@localhost : sbtest 04:53:01> CREATE TABLE `test` (
    ->   `id` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    -> ;
Query OK, 0 rows affected (0.23 sec)
root@localhost : sbtest 04:53:24> insert into test values (2);
Query OK, 1 row affected (0.05 sec)
root@localhost : sbtest 04:54:11> select * from test;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

2、ERROR 1062 从库插入了数据，主键重复

2.1、模拟错误产生

2.1.1、创建测试表sbtest.shuihuchuan

 
 #主库上创建测试表sbtest.shuihuchuan
mysql> CREATE TABLE `shuihuchuan` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `people` varchar(20) DEFAULT NULL,
    ->   `nickname` varchar(30) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.09 sec)
mysql> insert into shuihuchuan(nickname,people) values ('hubaoyi','songjiang'),('tuotatianwang','chaogai'),('yuqinlin','lujunyi'),('zhiduoxing','wuyong');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from shuihuchuan;
+----+-----------+---------------+
| id | people    | nickname      |
+----+-----------+---------------+
|  1 | songjiang | hubaoyi       |
|  2 | chaogai   | tuotatianwang |
|  3 | lujunyi   | yuqinlin      |
|  4 | wuyong    | zhiduoxing    |
+----+-----------+---------------+
4 rows in set (0.00 sec)

2.1.2、备库上查看同步情况

 
 root@localhost : sbtest 05:34:54> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sanguoyanyi      |
| shuihuchuan      |
| test             |
| xiyouji          |
+------------------+
4 rows in set (0.00 sec)
root@localhost : sbtest 05:34:56> select * from shuihuchuan;
+----+-----------+---------------+
| id | people    | nickname      |
+----+-----------+---------------+
|  1 | songjiang | hubaoyi       |
|  2 | chaogai   | tuotatianwang |
|  3 | lujunyi   | yuqinlin      |
|  4 | wuyong    | zhiduoxing    |
+----+-----------+---------------+
4 rows in set (0.00 sec)

2.1.3、备库上插入数据

 
 root@localhost : sbtest 05:36:46> insert into shuihuchuan(nickname,people) values ('ruyunlong','gongsunsheng');
Query OK, 1 row affected (0.03 sec)
root@localhost : sbtest 05:38:50> select * from shuihuchuan;
+----+--------------+---------------+
| id | people       | nickname      |
+----+--------------+---------------+
|  1 | songjiang    | hubaoyi       |
|  2 | chaogai      | tuotatianwang |
|  3 | lujunyi      | yuqinlin      |
|  4 | wuyong       | zhiduoxing    |
|  5 | gongsunsheng | ruyunlong     |
+----+--------------+---------------+
5 rows in set (0.00 sec)

2.1.4、主库上插入数据

 
 mysql> insert into shuihuchuan(nickname,people) values ('ruyunlong','gongsunsheng');
Query OK, 1 row affected (0.02 sec)
mysql> select * from shuihuchuan;
+----+--------------+---------------+
| id | people       | nickname      |
+----+--------------+---------------+
|  1 | songjiang    | hubaoyi       |
|  2 | chaogai      | tuotatianwang |
|  3 | lujunyi      | yuqinlin      |
|  4 | wuyong       | zhiduoxing    |
|  5 | gongsunsheng | ruyunlong     |
+----+--------------+---------------+
5 rows in set (0.00 sec)

2.1.5、查看备库复制情况

 
 root@localhost : sbtest 05:41:14> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 2801
               Relay_Log_File: mysql-relay-bin.000006
                Relay_Log_Pos: 2202
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table sbtest.shuihuchuan; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000026, end_log_pos 2770
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2506
              Relay_Log_Space: 2836
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table sbtest.shuihuchuan; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000026, end_log_pos 2770
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170522 05:40:50
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
#可以看到报主键重复的错误

2.2、主键重复错误解决

2.2.1、备库上删除重复主键的记录

 
 root@localhost : sbtest 05:46:29> stop slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost : sbtest 05:41:17> delete from shuihuchuan where id = 5;
Query OK, 1 row affected (0.04 sec)
root@localhost : sbtest 05:46:40> start slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost : sbtest 05:46:46> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 2801
               Relay_Log_File: mysql-relay-bin.000007
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2801
              Relay_Log_Space: 2836
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

3、ERROR 1032 删除或更新数据，从库找不到记录

3.1、Could not execute Delete_rows错误

3.1.1、模拟复制报错

 
 #先在备库上将id=2记录删除
root@localhost : sbtest 06:19:08> delete from shuihuchuan where id = 2;
Query OK, 1 row affected (0.02 sec)
#在主库上删除记录
mysql> delete from shuihuchuan where id = 2;
Query OK, 1 row affected (0.03 sec)
#查看备库复制状态
root@localhost : sbtest 06:19:27> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 3095
               Relay_Log_File: mysql-relay-bin.000007
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1032
                   Last_Error: Could not execute Delete_rows event on table sbtest.shuihuchuan; Can't find record in 'shuihuchuan', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000026, end_log_pos 3064
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2801
              Relay_Log_Space: 3130
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table sbtest.shuihuchuan; Can't find record in 'shuihuchuan', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000026, end_log_pos 3064
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170522 06:21:01
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

3.1.2、记录已经不存在，直接跳过错误就可以

 
 root@localhost : sbtest 06:25:11> stop slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost : sbtest 06:26:22> set global sql_slave_skip_counter=2;
Query OK, 0 rows affected (0.00 sec)
#具体跳过多少事务需要结合slave status中的Read_Master_Log_Pos、Exec_Master_Log_Pos以及show binlog events in 'mysql-bin.000026'中的事务项来判断
root@localhost : sbtest 06:26:26> start slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost : sbtest 06:26:28> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 3095
               Relay_Log_File: mysql-relay-bin.000009
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3095
              Relay_Log_Space: 625
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

3.2、Could not execute Update_rows错误

3.2.1、模拟复制报错

 
 #备库上删除id=5的记录
root@localhost : sbtest 06:30:10> delete from shuihuchuan where id = 5;
Query OK, 1 row affected (0.03 sec)
#主库上更新id=5的记录
mysql> update shuihuchuan set people = 'guansheng',nickname = 'dadao' where id = 5 ;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
#查看备库复制情况
root@localhost : sbtest 06:30:36> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 3412
               Relay_Log_File: mysql-relay-bin.000009
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table sbtest.shuihuchuan; Can't find record in 'shuihuchuan', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000026, end_log_pos 3381
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3095
              Relay_Log_Space: 942
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table sbtest.shuihuchuan; Can't find record in 'shuihuchuan', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000026, end_log_pos 3381
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170522 06:32:13
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

3.2.2、从库恢复不存在的记录，并解决错误

 
 #利用mysqlbinlog找出出错对应位置的执行语句，通过sql语句找到当时update对应的主键值
[root@host-192-168-1-21 binlog]#  mysqlbinlog -vv --base64-output=decode-rows --start-position=3095 --stop-position=3381 mysql-bin.000026 |grep -i -10 update 
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 3234
#170522  6:31:47 server id 12001  end_log_pos 3296 CRC32 0xc7c33055     Table_map: `sbtest`.`shuihuchuan` mapped to number 337
# at 3296
#170522  6:31:47 server id 12001  end_log_pos 3381 CRC32 0x16f7949a     Update_rows: table id 337 flags: STMT_END_F
### UPDATE `sbtest`.`shuihuchuan`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gongsunsheng' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @3='ruyunlong' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='guansheng' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
###   @3='dadao' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
ROLLBACK /* added by mysqlbinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
#利用主键值找到主库对应的数据行
mysql> select * from shuihuchuan where id = 5;
+----+-----------+----------+
| id | people    | nickname |
+----+-----------+----------+
|  5 | guansheng | dadao    |
+----+-----------+----------+
1 row in set (0.00 sec)
#在备库中插入主库查询到的记录
root@localhost : sbtest 06:50:23> stop slave;
Query OK, 0 rows affected (0.02 sec)
root@localhost : sbtest 06:49:56> insert into shuihuchuan(id,people,nickname) values (5,'guansheng','dadao');
Query OK, 1 row affected (0.04 sec)
root@localhost : sbtest 06:50:06> select * from shuihuchuan;
+----+-----------+------------+
| id | people    | nickname   |
+----+-----------+------------+
|  1 | songjiang | hubaoyi    |
|  3 | lujunyi   | yuqinlin   |
|  4 | wuyong    | zhiduoxing |
|  5 | guansheng | dadao      |
+----+-----------+------------+
4 rows in set (0.00 sec)
root@localhost : sbtest 06:50:29> start slave;
Query OK, 0 rows affected (0.03 sec)
root@localhost : sbtest 06:50:35> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 3412
               Relay_Log_File: mysql-relay-bin.000010
                Relay_Log_Pos: 286
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sbtest
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3412
              Relay_Log_Space: 942
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12001
                  Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)