MySQL GTID error 1236

Question

I am try to reduce machine come to live in production.. MySQL is making bottleneck. What i trying. I update code in one machine and also MySQL restore everything is working fine after this i am making EC2 AMI and launched machine from autoscaling group. now in this instance i am not taking and any live dump. after start MySQL and make it replication but its showing me GTID error. these all processes taking 30 to 40 minutes.

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.

any body can tell what i doing wrong. but if i take fresh dump and restore this in live machine its working. MySQL version 5.6.17.

Answer 1

You need to take dump from Master to replicate.

master has purged binary logs containing GTIDs that the slave requires.

Master delete its binary logs and create a new binary logs file. That's the reason when you implementing replication after taking dump is working because it's getting gtid_executed and gtid_purged that is available in the current binary log.

But when you are implementing without dump, The gtid_executed and gtid_purged getting matched because Mater already purged it.

So you must have to take dump and even sometimes you need to set the gtid_purged manually on slave.

You can read this, it has explained very well. https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/