您的位置:首页 > 数据库 > > 正文

mysql主从复制忽略问题(MySQL复制问题的三个参数分析)

更多 时间:2021-10-24 10:39:07 类别:数据库 浏览量:831

mysql主从复制忽略问题

MySQL复制问题的三个参数分析

    今天星期二,早上居然起晚了,上班迟到了,简直是。。。废话不多说,在昨天的文章中,我们提到了三个参数,分别是:

  • slave_exec_mode参数;
  • sql_slave_skip_counter=N参数;
  • slave-skip-errors=N参数。

这三个参数都可以解决并行复制中的一些指定的错误,例如duplicate key 1062错误等,今天我们简单试验一下,这三个参数的区别:

 

01 sql_slave_skip_counter参数

这个参数的设置主要是为了跳过某些错误的"event",注意这里的用词是event而不是事务,是因为它的本质是跳过一个一个事件,需要注意的是,这个参数需要在偏移量复制模式中使用,如果使用的是gtid的复制模式,则不可以使用这个参数。我们来看例子,首先搭建一套复制关系:

master   10.30.124.68

slave     10.30.124.128

这俩实例互为主从。我们创建测试表test.yeyz,并插入一些数据,其中id为主键,具有唯一性,如下:

master上

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • mysql:(none) 22:25:56>>select from test.yeyz;
  • +----+------+
  • | id | age  |
  • +----+------+
  • |  1 |    1 |
  • |  2 |    2 |
  • |  3 |    3 |
  • |  4 |    4 |
  • +----+------+
  • rows in set (0.00 sec)
  • slave上

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • mysql:(none) 22:25:38>>select from test.yeyz;
  • +----+------+
  • | id | age  |
  • +----+------+
  • |  1 |    1 |
  • |  2 |    2 |
  • |  3 |    3 |
  • |  4 |    4 |
  • |  5 |    5 |
  • +----+------+
  • rows in set (0.00 sec)
  • 我们可以发现,从节点的数据比主节点多一条,多了id=5的记录,然后我们在主节点上插入数据:

  • ?
  • 1
  • 2
  • 3
  • mysql:(none) 22:26:06>>insert into test.yeyz values (5,5),(6,6);
  • Query OK, 2 rows affected (0.00 sec)
  • Records: 2  Duplicates: 0  Warnings: 0
  • 此时观察从节点:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • mysql:(none) 22:26:34>>show slave status\G
  •                   Master_Host: 10.30.124.68
  •                   Master_User: dba_repl
  •                   Master_Port: 4306
  •                 Connect_Retry: 60
  •               Master_Log_File: mysqlbin.000002
  •           Read_Master_Log_Pos: 523
  •                Relay_Log_File: slave-relay-bin.000002
  •                 Relay_Log_Pos: 319
  •         Relay_Master_Log_File: mysqlbin.000002
  •              Slave_IO_Running: Yes
  •             Slave_SQL_Running: No
  •                    Last_Errno: 1062
  •                    Last_Error: Coordinator stopped because there were error(s) 
  • in the worker(s). The most recent failure being:
  •  Worker 0 failed executing transaction 'ANONYMOUS' at
  •  master log mysqlbin.000002, end_log_pos 492.
  •  See error log and/or performance_schema.replication_applier_status_by_worker
  •  table for more details about this failure or others, if any.
  •                  Skip_Counter: 0
  • 可以发现,从节点已经SQL线程断开了, 这个时候,在主节点上查询这个错误position 492处的binlog,可以看到:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • mysql:(none) 22:30:28>>show binlog events in 'mysqlbin.000002' from 194;  
  • +-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
  • | Log_name        | Pos | Event_type     | Server_id | End_log_pos | Info                                       |
  • +-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
  • | mysqlbin.000002 | 194 | Anonymous_Gtid |       192 |         259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'       |
  • | mysqlbin.000002 | 259 | Query          |       192 |         327 | BEGIN                                      |
  • | mysqlbin.000002 | 327 | Rows_query     |       192 |         391 | # insert into test.yeyz values (5,5),(6,6) |
  • | mysqlbin.000002 | 391 | Table_map      |       192 |         439 | table_id: 108 (test.yeyz)                  |
  • | mysqlbin.000002 | 439 | Write_rows     |       192 |         492 | table_id: 108 flags: STMT_END_F            |
  • | mysqlbin.000002 | 492 | Xid            |       192 |         523 | COMMIT /* xid=38 */                        |
  • +-----------------+-----+----------------+-----------+-------------+--------------------------------------------+
  • rows in set (0.00 sec)
  • 从上面的binlog可以看出来,我们的一个insert操作实际上生成了5个enent,分别对应的pos是从259~492,关于event,待会儿再说。

    因为主节点上插入了id=5的记录,跟从节点上的记录冲突了,查看错误日志,可以发现:

  • ?
  • 1
  • 2
  • 3
  • 4
  • Duplicate entry '5' for key 'PRIMARY',
  •  Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
  • the event's master log FIRST
  • end_log_pos 492 | 2019-07-16 22:26:25
  • 我们通过sql_slave_skip_counter参数的设置来解决这个问题,步骤如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • mysql:(none) 22:29:32>>stop slave;
  • Query OK, 0 rows affected, 1 warning (0.00 sec)
  •  
  • mysql:(none) 22:32:45>>set global sql_slave_skip_counter=1;
  • Query OK, 0 rows affected (0.00 sec)
  •  
  • mysql:(none) 22:33:06>>start slave;
  •    在昨天的文章中我们说过,sql_slave_skip_counter后面跟的值是event的个数,所以这里我们相当于跳过了一个event,mysql中规定,如果跳过一个event之后,还在某一个事务里面,那么会继续跳过这个事务。

       使用这个参数跳过一个event之后,我们再来看从库表中的数据和复制情况,可以看到:

    slave表:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • mysql:(none) 22:33:10>>show slave status\G
  • *************************** 1. row ***************************
  •                Slave_IO_State: Waiting for master to send event
  •                   Master_Host: 10.30.124.68
  •                   Master_User: dba_repl
  •                   Master_Port: 4306
  •                 Connect_Retry: 60
  •               Master_Log_File: mysqlbin.000002
  •           Read_Master_Log_Pos: 523
  •                Relay_Log_File: slave-relay-bin.000003
  •                 Relay_Log_Pos: 319
  •         Relay_Master_Log_File: mysqlbin.000002
  •              Slave_IO_Running: Yes
  •             Slave_SQL_Running: Yes
  •  
  •  
  • mysql:(none) 22:33:16>>select from test.yeyz;
  • +----+------+
  • | id | age  |
  • +----+------+
  • |  1 |    1 |
  • |  2 |    2 |
  • |  3 |    3 |
  • |  4 |    4 |
  • |  5 |    5 |
  • +----+------+
  • rows in set (0.00 sec)
  • 看看master表:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • mysql:(none) 22:33:36>>select from test.yeyz;
  • +----+------+
  • | id | age  |
  • +----+------+
  • |  1 |    1 |
  • |  2 |    2 |
  • |  3 |    3 |
  • |  4 |    4 |
  • |  5 |    5 |
  • |  6 |    6 |
  • +----+------+
  • rows in set (0.00 sec)
  •    可以发现,master中数据插入成功,而slave中数据插入失败,也就是说:

    该参数跳过错误的时候,会导致主从的数据不一致。

     

    02 slave_skip_errors参数

        这个参数是跳过制定的错误,也就是说,需要我们设置对应的error_code,从下面的日志中的内容可以看出,error_code的值为1062

  • ?
  • 1
  • 2
  • 3
  • 4
  • Duplicate entry '5' for key 'PRIMARY',
  •  Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; 
  • the event's master log FIRST
  • end_log_pos 492 | 2019-07-16 22:26:25
  •    我们需要手动将这个参数的值也该为1062,需要注意的是,这个参数的改动需要重启mysql服务,因为这个参数是一个只读的参数。

       修改后的情况如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • mysql--dba_admin@127.0.0.1:(none) 22:38:55>>show variables like '%errors%';
  • +--------------------+---------+
  • | Variable_name      | Value   |
  • +--------------------+---------+
  • | max_connect_errors | 1000000 |
  • | slave_skip_errors  | 1062    |
  • +--------------------+---------+
  • rows in set (0.01 sec)
  •    此时我们更新master表和slave表的数据,更新后的情况如下:

    master:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • mysql:(none) 22:39:15>>select from test.yeyz;
  • +----+------+
  • | id | age  |
  • +----+------+
  • |  1 |    1 ||  2 |    2 |
  • |  3 |    3 ||  4 |    4 |
  • |  5 |    5 ||  6 |    6 |
  • +----+------+
  • rows in set (0.00 sec)
  • slave上:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • mysql:(none) 22:40:15>>select from test.yeyz;
  • +----+------+
  • | id | age  |
  • +----+------+
  • |  1 |    1 |
  • |  2 |    2 |
  • |  3 |    3 |
  • |  4 |    4 |
  • |  5 |    5 |
  • |  6 |    6 |
  • |  7 |    7 |
  • +----+------+
  • rows in set (0.00 sec)
  • 我们发现,slave表比master表多一条数据,也就是id=7的记录,此时我们在master上执行:

  • ?
  • 1
  • 2
  • 3
  • mysql:(none) 22:34:15>>insert into test.yeyz values (7,7),(8,8);
  • Query OK, 2 rows affected (0.00 sec)
  • Records: 2  Duplicates: 0  Warnings: 0
  • 查看slave上面的复制情况和数据情况,如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • mysql:(none) 22:39:05>>show slave status\G
  • *************************** 1. row ***************************
  •                Slave_IO_State: Waiting for master to send event
  •                   Master_Host: 10.30.124.68
  •                   Master_User: dba_repl
  •                   Master_Port: 4306
  •                 Connect_Retry: 60
  •               Master_Log_File: mysqlbin.000002
  •           Read_Master_Log_Pos: 852
  •                Relay_Log_File: slave-relay-bin.000005
  •                 Relay_Log_Pos: 648
  •         Relay_Master_Log_File: mysqlbin.000002
  •              Slave_IO_Running: Yes
  •             Slave_SQL_Running: Yes
  •               Replicate_Do_DB: 
  •           Replicate_Ignore_DB: 
  •            Replicate_Do_Table: 
  •        Replicate_Ignore_Table: 
  •       Replicate_Wild_Do_Table: 
  •  
  •  
  • mysql:(none) 22:40:15>>select from test.yeyz;
  • +----+------+
  • | id | age  |
  • +----+------+
  • |  1 |    1 |
  • |  2 |    2 |
  • |  3 |    3 |
  • |  4 |    4 |
  • |  5 |    5 |
  • |  6 |    6 |
  • |  7 |    7 |
  • +----+------+
  • rows in set (0.00 sec)
  •    可以看到,复制没有出现错误,即使从库上已经有id=7的记录。而且发现,从库的数据跟之前保持一致,也就是说,主库插入的id=8的记录没有被同步过来。

       总结一下:该参数在跳过复制错误的时候,需要重启mysql服务,然后可能导致主从数据不一致。

     

    03 slave-skip-errors=N参数

      再看最后一个参数,这个参数表示的是并行复制过程中的从库复制模式,默认值是strict严格模式,和上面一样,我们先看主库和从库的数据情况:

    master数据:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • mysql:(none) 22:39:20>>select from test.yeyz;                 
  • +----+------+
  • | id | age  |
  • +----+------+
  • |  1 |    1 |
  • |  2 |    2 |
  • |  3 |    3 |
  • |  4 |    4 |
  • |  5 |    5 |
  • |  6 |    6 |
  • |  7 |    7 |
  • |  8 |    8 |
  • +----+------+
  • rows in set (0.00 sec)
  • slave数据:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 标签:mysql 复制
    您可能感兴趣