MySQL GTID模式 主从复制跳过错误事务

231weq

今天在做基于GTID模式的主从复制时，发现一个跳过主从复制的错误日志的方法，希望发表出来对大家能有帮助。

首先在master端配置了复制账号copy并只给了REPLICATION权限。当前状态是主从复制正常。当我在master端删除''@'$hostname',''@'localhost'这样的匿名账号时从端开始复制模式开始报错。

分析应该是copy权限不够，REPLICATION权限应该不能删除mysql库中用户。所以从端会出错。错误信息如下：
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.101.200.210
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 210-log-bin.000003
          Read_Master_Log_Pos: 2796
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: 210-log-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1396
                   Last_Error: Worker 1 failed executing transaction '' at master log 210-log-bin.000003, end_log_pos 694; Error 'Operation DROP USER failed for ''@'shangjia1.test.com'' on query. Default database: 'mysql'. Query: 'drop user ""@'shangjia1.test.com''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 537
              Relay_Log_Space: 3891
              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: 1396
               Last_SQL_Error: Worker 1 failed executing transaction '' at master log 210-log-bin.000003, end_log_pos 694; Error 'Operation DROP USER failed for ''@'shangjia1.test.com'' on query. Default database: 'mysql'. Query: 'drop user ""@'shangjia1.test.com''
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 200210
                  Master_UUID: 4540fbfd-b055-11e5-8b5d-0050568b7072
             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: 160101 15:37:10
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 4540fbfd-b055-11e5-8b5d-0050568b7072:3-12
            Executed_Gtid_Set: 4540fbfd-b055-11e5-8b5d-0050568b7072:1-2:12,
4e555522-b055-11e5-8b5d-0050568b4f5b:1-2
                Auto_Position: 1
1 row in set (0.01 sec)

所以我在主端先给了copy用户所有权限，所有权限包括了REPLICATION CLIENT（客户端）、REPLICATION SLAVE（服务端）、SUPER（管理）和RELOAD四个权限。具体权限问题请参阅这位网友的介绍
http://www.iyunv.com/thread-160014-1-1.html
给了权限后重启slave后还是会报上面1396错误。
此时我就比较郁闷了。后来就想是否可以跳过这个错误呢？
mysql主从复制时跳过指定的错误请认真阅读这位网友的文章

http://www.iyunv.com/thread-160013-1-1.html

具体操作是，在日志中找到错误代码然后指定跳过的错误代码即可。
我的错误代码是1396.
2016-01-01 15:25:55 28177 [Note] Slave SQL thread initialized, starting replication in log '210-log-bin.000003' at position 537, relay
log '/data/mysql/relay-log/slave-relay-bin.000002' position: 414
2016-01-01 15:25:55 28177 [ERROR] Slave SQL: Worker 1 failed executing transaction '' at master log 210-log-bin.000003, end_log_pos 69
4; Error 'Operation DROP USER failed for ''@'shangjia1.test.com'' on query. Default database: 'mysql'. Query: 'drop user ""@'shangjia1
.test.com'', Error_code: 1396
2016-01-01 15:25:55 28177 [Warning] Slave SQL: ... The slave coordinator and worker threads are stopped, possibly leaving data in inco
nsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables o
r DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
2016-01-01 15:35:41 28177 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
2016-01-01 15:35:41 28177 [Note] Slave I/O thread killed while reading event
2016-01-01 15:35:41 28177 [Note] Slave I/O thread exiting, read up to log '210-log-bin.000003', position 2796
2016-01-01 15:37:10 28177 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is
therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Synta
x' in the MySQL Manual for more information.
2016-01-01 15:37:10 28177 [Note] Slave I/O thread: connected to master 'copy@10.101.200.210:3306',replication started in log '210-log-
bin.000003' at position 2796
2016-01-01 15:37:10 28177 [Note] Slave SQL thread initialized, starting replication in log '210-log-bin.000003' at position 537, relay
log '/data/mysql/relay-log/slave-relay-bin.000002' position: 414
2016-01-01 15:37:10 28177 [ERROR] Slave SQL: Worker 1 failed executing transaction '' at master log 210-log-bin.000003, end_log_pos 69
4; Error 'Operation DROP USER failed for ''@'shangjia1.test.com'' on query. Default database: 'mysql'. Query: 'drop user ""@'shangjia1
.test.com'', Error_code: 1396
2016-01-01 15:37:10 28177 [Warning] Slave SQL: ... The slave coordinator and worker threads are stopped, possibly leaving data in inco
nsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables o
r DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756

所以在/etc/my.cnf中定义如下
slave-skip-errors = 1396 至于为什么跳过指定的错误代码，请查阅http://www.iyunv.com/thread-160013-1-1.html的文章。
然后重启mysql服务即可。