MySQL GTID复制中断修复过程

slave中出现错误：2020-04-09T07:40:18.719203Z 16 [ERROR] Slave SQL for channel ”: Could not execute Write_rows event on table mytestdb.t1; Duplicate entry ‘6’ for key ‘PRIMARY’, Error_c开发云主机域名ode: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000050, end_log_pos 437, Error_code: 10622020-04-09T07:40:18.719237Z 16 [Warning] Slave: Duplicate entry ‘6’ for key ‘PRIMARY’ Error_code: 10622020-04-09T07:40:18.719246Z 16 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000050’ position 194.这是由于我人为往表中制造了主键冲突查看slave的gtid信息:mysql> show global variables like ‘%gtid%’;+———————————-+—————————————————————————————+| Variable_name | Value |+———————————-+—————————————————————————————+| binlog_gtid_simple_recovery | ON || enforce_gtid_consistency | ON || gtid_executed | 2ff0b1ed-5dc8-11ea-9878-000c29872e9a:1-6957,3853efe2-5dc8-11ea-86cb-000c295618b3:1-2 || gtid_executed_compression_period | 1000 || gtid_mode | ON || gtid_owned | || gtid_purged | 2ff0b1ed-5dc8-11ea-9878-000c29872e9a:1-2 || session_track_gtids | OFF |+———————————-+—————————————————————————————+查看master的gtid信息：root@dv 15:40: : [(none)]>show global variables like ‘%gtid%’;+———————————-+———————————————+| Variable_name | Value |+———————————-+———————————————+| binlog_gtid_simple_recovery | ON || enforce_gtid_consistency | ON || gtid_executed | 2ff0b1ed-5dc8-11ea-9878-000c29872e9a:1-6958 || gtid_executed_compression_period | 1000 || gtid_mode | ON || gtid_owned | || gtid_purged | 2ff0b1ed-5dc8-11ea-9878-000c29872e9a:1-2 || session_track_gtids | OFF |+———————————-+———————————————+设置从库的gtid_next
mysql> SET GTID_NEXT=”2ff0b1ed-5dc8-11ea-9878-000c29872e9a:1-6957″;
ERROR 1774 (HY000): Malformed GTID specification ‘2ff0b1ed-5dc8-11ea-9878-000c29872e9a:1-6958’.
mysql> SET GTID_NEXT=”2ff0b1ed-5dc8-11ea-9878-000c29872e9a:6957″;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
这里是模拟一个事务，代替出错的事务
mysql> SET GTID_NEXT=”AUTOMATIC”
-> ;
Query OK, 0 rows affected (0.00 sec)
紧接着start slave即可

相关推荐: pt-query-digest 日常使用方法

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析，可以说pt-query-dig…