使用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