使用GTID时出现MySQL错误1236
2 min read

使用GTID时出现MySQL错误1236

业务场景:

主从复制的一套数据库中主库所在的数据库因为磁盘损坏,导致MySQL故障。重新搭建,将从库的数据导入新的主库,查看slave状态时报如下错误:

Last_IO_Error: 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.'

解决方法

在新的从库上执行以下操作

slave> stop slave;

slave> FLUSH TABLES WITH READ LOCK;

slave> show master status;

'4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4:1-83345127,5b37def1-6189-11e3-bee0-e89a8f22a444:1-13030:13032-13317:13322-13325:13328-653183:653185-654126:654128-1400817:1400820-3423394:3423401-5779965′

(HERE 83345127  Last GTID executed on master and 5779965 Last slave GTID executed on Master )

slave> reset master;

slave> set global GTID_PURGED='4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4:1-83345127,5b37def1-6189-11e3-bee0-e89a8f22a444:1-5779965′;

slave> start slave;

slave> unlock  tables;

slave> show slave status;

参考链接:https://stackoverflow.com/questions/38390765/mysql-error-1236-when-using-gtid

mysql 5.6 GTID replication errors and fixes What is GTID?
4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4
  • This is the server's 128 bit identification number (SERVER_UUID). It identifies where the transaction was originated. Every server has its own SERVER_UUID. What problems GTID solves?
  • It is possible to identify a transaction uniquely across the replication servers. Make the automation of failover process much easier. There is no need to do calculations, inspect the binary log and so on. Just MASTER_AUTO_POSITION=1.
  • At application level, it is easier to do WRITE/READ split. After a write on the MASTER, you have a GTID so just check if that GTID has been executed on the SLAVE that you use for reads.
  • Development of new automation tools isn't a pain now. How can I implement it?

Three variables are needed in ALL servers of the replication chain

  • gtid_mode: It can be ON or OFF (not 1 or 0). It enables the GTID on the server.
  • log_bin: Enable binary logs. Mandatory to create a replication environment.
  • log-slave-updates: Slave servers must log the changes that come from the master in its own binary log.
  • enforce-gtid-consistency: Statements that can't be logged in a transactionally safe manner are denied by the server. ref: http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html
京ICP备19055754号