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:

Shell

1

2

3

4

5

mysql>show slave statusG

[...]

Slave_IO_Running:No

Slave_SQL_Running:Yes

[...]

The error log tells us why the IO thread has not started:

Shell

1

2013-05-1713:21:263130[ERROR]SlaveI/O:The slave IO thread stops because the master has GTID_MODE OFF andthisserver 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:

Shell

1

2013-05-1713:32:082563[ERROR]SlaveI/O:The slave IO thread stops because the master has GTID_MODE ON andthisserver 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.

This time, we will enable GTID replication on slave #1, but not on slave #2:

Shell

1

2

3

4

# slave #1

mysql>change master tomaster_auto_position=1;

mysql>start slave;

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

Shell

1

mysql>create table test.t(idintnotnullauto_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:

Shell

1

2

3

4

5

6

7

8

9

# Slave #1

mysql>show slave statusG

[...]

Auto_Position:1->GTID-based positioning

# Slave #2

mysql>show slave statusG

[...]

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.

These limitations are part of the reason we decided not to merge MySQL 5.6 global transaction ID into MariaDB.

In MariaDB global transaction ID, it is much easier to switch a slave to GTID. Simply STOP SLAVE on the slave server, CHANGE MASTER to make it use GTID to connect, START SLAVE. There is no need for any special configuration or to restart or otherwise touch any other servers.

Yes, because MySQL 5.6 GTID is fundamentally broken in the design. You don’t want to be compatible with it.

I don’t see anyone wanting to try to run a mixed replication cluster of MySQL 5.6 and MariaDB 10 using GTID. You will be kept plenty busy trying to get even a pure MySQL 5.6 gtid replication cluster working …

@Kristian: Mixing versions/variants is common, the most typical scenario is when you want to upgrade. You usually upgrade only a few machines to see how it goes before deploying the new version on every server.

Having such an incompatibilty could unfortunately become a major hurdle in the future for people wanting to switch from MySQL 5.6 to MariaDB 10.

You can replicate from MySQL 5.6 to MariaDB 10.0 or vice versa. You just won’t be able to use the GTID features to automatically pick the correct binlog position when switching to a new master. Old-style replication will work.

That being said, I agree that compatibility is good. If you have any suggestion of how to make MariaDB compatible without merging all of the crap that is MySQL 5.6 global transaction ID, I would very much like to hear it …

It may not be easy to simply switch from regular MySQL replication to using replication using GTIDs

1. Temporary tables – CREATE and DROP TEMPORARY tables are not supported inside transaction. 2. Updates involving non-transactional storage engines – It is not possible to mix non-transactional tables (such as MYISAM) with innodb tables within the same transaction. The same issue can arise if slave host is using different storage engine for the respective table on master databases. Also, if BINLOG_FORMAT is not consistent on master/slave db. 3. CREATE TABLE …. SELECT – It is not supported to perform such type of statements