MySQL GTID error 1236

amazon ec2gtidMySQL

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.

Best Answer

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/

Related Solutions

Mysql – Broken thesql replication on slave server (error 1236)

I believe the log file and position need to be specified with the host.

Try:

CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

Mysql – Replication stops with GTID_NEXT error after creation/drop of memory table in thesql5.6

I encountered a similar problem that generated the same error. On a master instance I dropped a database.

On a slave there was a file left over <tablename>.exp (I forget what purpose this serves).

The slave replication died with

last_Error: Error 'DROP DATABASE failed; some tables may have been dropped but the database directory remains. The GTID has not been added to GTID_EXECUTED and the statement was not written to the binary log. Fix this as follows: (1) remove all files from the database directory ./db_to_drop/; (2) SET GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506'; (3) DROP DATABASE db_to_drop.' on query. Default database: 'db_to_drop'. Query: 'drop database db_to_drop'

I removed the file, ran the SET and restarted replication:

SET GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506'; start slave; ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506'.

And this, from following the suggestion. Since it's only a development cluster, I wasn't worried about losing it entirely, so I punted and tried setting it to the next GTID.

SET GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066507';

(i.e. + 1). Restarted replication and things got back to normal.

Best Answer

Related Solutions

Mysql – Broken thesql replication on slave server (error 1236)

Mysql – Replication stops with GTID_NEXT error after creation/drop of memory table in thesql5.6

Related Question