Restoring slave from master server on mysql 5.6

Currently, I am working on moving one giant complicated webapp from our on-premises network to Amazon AWS Cloud. There are many reason for moving webapp from on-premise to Amazon Cloud. To mention couple are redundancy, scalability, HA. For me only the drawback of moving to Cloud is security and you don’t have complete control of Hardware and architecture.

Anyway, currently we are using mysql server 5.1, so while moving to AWS cloud, I decided to go with mysql server 5.6 because of lots of enhancement and specially because of GTID replication and mysql utilities.
After running master server successfully, while trying to replicate slave with master, it was not a easy task. Probably if I have gone on detail how mysql replication works using GTID that would have helped.
I was getting following error on my slave server.
2014-01-26 22:40:02 16119 [ERROR] Error reading packet from server: Found old binary log without GTIDs while looking for the oldest binary log that contains any GTID that is not in the given gtid set ( server_errno=1236)
2014-01-26 22:40:02 16119 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘Found old binary log without GTIDs while looking for the oldest binary log that contains any GTID that is not in the given gtid set’, Error_code: 1236
You need to know how GTID replication process works to resolve this.
How to set up a new slave
With introduction of GTID on mysql 5.6, Binary Logs and Position are not needed anymore if you are using GTID for replication. Instead we need to know in which GTID is the master and set it on the slave. MySQL keeps two global variables with GTID numbers on it:
gtid_executed: it contains a representation of the set of all transaction logged in the binary log
gtid_purged: it contains a representation of the set of all transactions deleted from the binary log
So the process to replicate slave with master is
1.    take a backup from the master and store the value of gtid_executed
2.    restore the backup on the slave and set gtid_purged with the value of gtid_executed from the master
3.    The new mysqldump can do those tasks for us.
Let’s go through example of how to take a backup from the master and restore it on the slave to set up a new replication server.
Restore a slave
Let’s imagine that our slave server is behind the schedule of master server. This is the error we are going to get:
Slave_IO_Running:No
Slave_SQL_Running: Yes

First, we get the GTID_EXECUTED from the master:
master > show global variables like ‘GTID_EXECUTED’;
+—————+——————————————————————————–+
| Variable_name | Value                                                                              |
+—————+——————————————————————————–+
| gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-14       |
+—————+———————————————————————————+

master > show global variables like ‘GTID_EXECUTED’;
+———————————————————————————————-+
Variable_name | Value                                                                             |
+—————+—————————————————————————–+
| gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-14   |
+—————+—————————————————————————–+
And we set it on the slave:
slave1 > set global GTID_PURGED=”9a511b7b-7059-11e2-9a24-08002762b8af:1-14″;
ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.

GTID_EXECUTED should be empty before changing GTID_PURGED manually but we can’t change it with SET because is a read only variable. The only way to change it is with reset master (yes, on a slave server):
slave1> reset master;
slave1 > show global variables like ‘GTID_EXECUTED’;
+—————+————————-+
| Variable_name | Value          |
+—————+———————–+
| gtid_executed |                    |
+—————+———————-+
slave1 > set global GTID_PURGED=”9a511b7b-7059-11e2-9a24-08002762b8af:1-14″;
slave1> start slave io_thread;
slave1> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes