Mysql基于GTID主从复制跳过异常

首先在mater主机查看当前进行到的GTID事务点，记录下来

 mysql> show master status\G
 *************************** 1. row ***************************
              File: master01.000017
          Position: 35667862
      Binlog_Do_DB: afmweb_news,gw_local,umc_ah
  Binlog_Ignore_DB: afmtimer,afmweb_news_cg,umc_ah_cg,gearman,mysql,information_schema,performance_schema
 Executed_Gtid_Set: a9b9b63d-6b63-11e6-8f20-3440b5860519:1-34339
 1 row in set (0.02 sec)

当前1-34339是进行到此刻的GTID事务点,然后锁表，禁止数据插入变化

 mysql> flush tables with read lock;         //锁表，防止主数据库更新
 mysql> SHOW VARIABLES LIKE 'event_scheduler';
 +-----------------+-------+
 | Variable_name   | Value |
 +-----------------+-------+
 | event_scheduler | ON    |
 +-----------------+-------+
 mysql> SET GLOBAL event_scheduler = off ;         //关闭事件调度器

从库进行数据恢复以后，跳过记录的GTID事务点之前的事务，开始从此事务点和主库同步

 mysql> stop slave;
 Query OK, 0 rows affected (0.07 sec)
 mysql> reset master;
 Query OK, 0 rows affected (0.17 sec)
 mysql> reset slave;
 Query OK, 0 rows affected (0.16 sec)
 mysql> set global gtid_purged='a9b9b63d-6b63-11e6-8f20-3440b5860519:1-34339';
 Query OK, 0 rows affected (0.13 sec)
 mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.140',MASTER_USER='dbslave',MASTER_PASSWORD='yourpassword'
 ,MASTER_AUTO_POSITION = 1;
 Query OK, 0 rows affected, 2 warnings (0.33 sec)
 mysql> start slave;
 Query OK, 0 rows affected (0.11 sec)

主库解锁，开始同步

 mysql> unlock tables;                        //解锁
 mysql> SHOW VARIABLES LIKE 'event_scheduler';
 +-----------------+-------+
 | Variable_name   | Value |
 +-----------------+-------+
 | event_scheduler | OFF   |
 +-----------------+-------+
 mysql> SET GLOBAL event_scheduler = on ;         //开启事件调度器