MySQL开启GTID主从复制出错处理

前段时间给开发部署了MySQL主从环境使用，今天下午告诉我说从库数据不动了，让我看下怎么回事。

连上去一看，是从库复制报错 1060：

在Master binlog上查找错误Pos，是执行增加字段语句出现问题：

后查看从库此表上已有此字段，应为开发人员使用root用户在从库上直接操作所致

然后向其告知原因，以后注意操作。

接下来就处理Slave本身同步失败问题；既然是字段已存在，那就跳过这条执行事件即可。

在没开启GTID的情况下执行如下命令即可：

mysql> stop slave; 
Query OK, 0 rows affected (0.04 sec)

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

可以看到，由于开启了GITD，执行时会报错。上面也提供了方法，就是将你想要跳过的GTID事务替换为空事务即可。

这里我们要跳过的GTID事务上面的截图也已标明，这里操作下即可：

mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)

mysql> SET GTID_NEXT='b5a3fc76-5155-11ea-ae29-00e04c063666:38711';
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.07 sec)

mysql> SET GTID_NEXT='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1.zongs-data.com
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000030
          Read_Master_Log_Pos: 13016523
               Relay_Log_File: dn2-relay-bin.000078
                Relay_Log_Pos: 429693900
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 429693687
              Relay_Log_Space: 493538592
              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: 89903
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: 4000
                  Master_UUID: b5a3fc76-5155-11ea-ae29-00e04c063666
             Master_Info_File: /data/mysql_slaver/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for dependent transaction to commit
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b5a3fc76-5155-11ea-ae29-00e04c063666:3-114986
            Executed_Gtid_Set: b5a3fc76-5155-11ea-ae29-00e04c063666:1-38763
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

启动后从库状态正常，继续执行之后的操作。