Simplest way to Rebuild GTID based replication using Mysqldump

If you have been working on GTID based replication then you might have faced issues in rebuilding the replication in case the slave throws any error like ‘master has purged the binary logs’ etc.
In this post I’ll share the easiest way to rebuild GTID based replication using Mysqldump utility.

Rebuild GTID based replication using Mysqldump

Slave:
mysqldump -u user -p --all-databases --single-transaction --flush-logs --triggers --routines --events -h masterhost.com > master.sql

Login to MySQL console:
slave> stop slave;
slave> show global variables like 'gtid_executed'; (make sure the value is empty)
slave> reset slave;
slave> reset master;
slave> show global variables like 'gtid_executed'; (it should be empty now)

slave> source master.sql;
show global variables like 'gtid_executed'; (it should have the value now)
slave> CHANGE MASTER TO MASTER_HOST ='masterhost.com', MASTER_USER='repluser',MASTER_PASSWORD='replpassword', MASTER_PORT=3311, MASTER_AUTO_POSITION=1;
slave> start slave;
slave> show slave status;

Now the slave should be in sync with master.

In case you are using a master-master replication then you have perform the below step additionally:

Master:

master> stop slave;
master> change master to master_auto_position=1;
master> show slave status\G

That is it, your replication should be running fine now.