复制之跳过异常语句的操作

主健冲突或数据不一致的情况：
(1)停止slave进程
STOP SLAVE;    

(2)设置事务号，事务号从Retrieved_Gtid_Set获取

SET @@SESSION.GTID_NEXT= 'xxxxxxxxxxx'    

(3)设置空事务

BEGIN; COMMIT;    

(4)恢复事务号

SET SESSION GTID_NEXT = AUTOMATIC;    

(5)启动slave进程

START SLAVE;  

----------------------------------------------------------------------------------------

5.reset master信息

 

mysql> reset master;

Query OK, 0 rows affected (0.11 sec)

 

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      154 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

mysql> show global variables like '%gtid%';

+----------------------------------+-------+

| Variable_name                    | Value |

+----------------------------------+-------+

| binlog_gtid_simple_recovery      | ON    |

| enforce_gtid_consistency         | ON    |

| gtid_executed                    |       |

| gtid_executed_compression_period | 1000  |

| gtid_mode                        | ON    |

| gtid_owned                       |       |

| gtid_purged                      |       |

| session_track_gtids              | OFF   |

+----------------------------------+-------+

8 rows in set (0.01 sec)

 

可以看到 gtid_executed  和 gtid_purged 已经没有gtid的信息了

--------------------------------------------------------------------------------------------------------------------------------

主从变成主主

change master to  master_host='192.168.6.1',master_port=3306 , master_user='555',master_password='123456' ,master_auto_position=1;