Replication in MySQL 5.6: GTIDs benefits and limitations – Part 1

global transactions identifiers are one of the new features regarding replication in mysql 5.6. they open up a lot of opportunities to make the life of dbas much easier when having to maintain servers under a specific replication topology. however you should keep in mind some limitations of the current implementation. this post is the first one of a series of articles focused on the implications of enabling gtids on a production setup.

the manual describes very nicely how to switch to gtid-based replication, i won’t repeat it.

basically the steps are:

• make the master read-only so that the slaves can execute all events and be in sync with the master
• change configuration for all servers and restart them
• use change master to to instruct all servers to use gtids
• disable read-only mode

this procedure will switch all your servers from regular replication to gtid replication. but if you are running a production system, you will probably want to gradually enable gtid replication for an easier rollback in the event of a problem. and some items in the documentation are not so clear.

for instance:

• do we really need to restart all the servers at the same time? downtime is something we like to avoid!
• is it necessary to make the master read-only?
• can we use regular replication for some slaves and gtid replication for other slaves at the same time?

to find an answer to these questions, let’s create a simple replication configuration with one master and two slaves, all running mysql 5.6 with gtids disabled.

first try: configure only one of the servers with gtids

let’s stop slave #2, change configuration and restart it:

mysql> show slave status\g
[...]
slave_io_running: no
slave_sql_running: yes
          [...]

the error log tells us why the io thread has not started:

2013-05-17 13:21:26 3130 [error] slave i/o: the slave io thread stops because the master has gtid_mode off and this server has gtid_mode on, error_code: 1593

so unfortunately if you want replication to work correctly, gtid_mode must be on on all servers or off on all servers, but not something in the middle.

what if we try to reconfigure the master? this time, replication on slave #1 will stop:

2013-05-17 13:32:08 2563 [error] slave i/o: the slave io thread stops because the master has gtid_mode on and this server has gtid_mode off, error_code: 1593

these simple tests answer the first two questions: replication works only if all servers have the same value for gtid_mode, so you should restart them at the same time, which is best done by making the master read-only. however, “at the same time” means “at the same binlog position”, so you can perfectly restart the servers one by one.

second try: gtids enabled, mixing regular replication and gtid replication

this time, we will enable gtid replication on slave #1, but not on slave #2:

# slave #1
mysql> change master to master_auto_position = 1;
mysql> start slave;

and let’s create a new table on the master:

mysql> create table test.t (id int not null auto_increment primary key);

executing show tables from test on both slaves shows that the table has been created everywhere. so once gtids are enabled on all servers, you can have some slaves using file-based positioning and some other slaves using gtid-based positioning.

this answers the second question: we can have different replication modes on different servers, but only if all servers have gtid_mode set to on. could it be interesting to run file-based replication when gtid_mode is on? i can’t think of any use case, so in practice, you’ll probably use either file-based replication only (gtid_mode=off for all servers) or gtid-based replication only (gtid_mode=on for all servers).

additional question: how can you know if a slave is using gtid-based replication by inspecting the output of show slave status ? look at the last field, auto_position :

# slave #1
mysql> show slave status\g
[...]
auto_position: 1  -> gtid-based positioning
# slave #2
mysql> show slave status\g
[...]
auto_position: 0  -> file-based positioning

conclusion

enabling gtid-based replication can be tricky if your application does not easily tolerate downtime or read-only mode, especially if you have a lot of servers to reconfigure. it would be really nice to be able to mix servers where gtid_mode is on with servers where gtid_mode is off. this would greatly simplify the transition to gtid-based replication and allow easier rollbacks if something goes wrong.