今天在做基于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://gfsunny.blog.51cto.com/990565/1554627


给了权限后重启slave后还是会报上面1396错误。

此时我就比较郁闷了。后来就想是否可以跳过这个错误呢？

mysql主从复制时跳过指定的错误请认真阅读这位网友的文章

http://blog.csdn.net/wulantian/article/details/38369259


具体操作是，在日志中找到错误代码然后指定跳过的错误代码即可。

我的错误代码是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://blog.csdn.net/wulantian/article/details/38369259的文章。

然后重启mysql服务即可。


如果有错误的地方请各位网友指正。谢谢！