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;
mysql 5.6 GTID replication errors and fixes What is GTID?
- 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