从库轻松跳过某个事务号

场景

某燃气公司由于配置主从同步时,一些不合理操作,导致从库SQL进程应用日志失败。

报错信息

后台日志报错信息如下:

2018-04-09T14:41:16.445626Z 7 [ERROR] Error reading packet from server for channel '': The slave is connecting using CHANGE MASTER

TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. (server_errno=1236)

2018-04-09T14:41:16.445645Z 7 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log:

'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs

that the slave requires.', Error_code: 1236

2018-04-09T14:41:16.445651Z 7 [Note] Slave I/O thread exiting for channel '', read up to log 'FIRST', position 4

解决方式操作步骤

查看事务ID号

查询主库上先哪些gtid被purge了:

show global variables like 'gtid_purged';

然后拿着这个value,去从库上依次执行

stop slave; set global gtid_purged = '0edad259-2b45-11e8-9ceb-7cd30ad38468:1-149365,'

启动从库

start slave;

这样能跳过执行被主库已经purge的事务了.

查看从库运行状态

登录从库

mysql> show slave status \G;

Slave_IO_State: Waiting for master to send event

Master_Host: rm-bp18760xit79n871z.mysql.rds.aliyuncs.com

Master_User: embc_root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000446

Read_Master_Log_Pos: 18662830

Relay_Log_File: appserver-f1-relay-bin.000787

Relay_Log_Pos: 18663043

Relay_Master_Log_File: mysql-bin.000446

Slave_IO_Running: Yes Slave_SQL_Running: Yes

处理成功

发表评论

发表评论

*

沙发空缺中,还不快抢~