Things you should consider before using GTID

Global Transaction ID (GTID) is one of the major features that were introduced in MySQL 5.6 which provides a lot of benefits.
I have talked about the GTID concept, implementation and possible troubleshooting at Percona Live London 2014, you can download the slides from our presentations repository or from my session at Percona Live.

On the other hand, there are some important things you should consider before deploying GTID in production, I'm going to list them here in this blog post.

Table of Content

• Migration to GTID replication
• Non transactionally safe statement will raise errors now
• MySQL Performance in GTID
• mysql_upgrade script
• Errant transactions!
• Filtration on the slave
• Conclusion

Migration to GTID replication

It is required to shutdown MySQL service on all servers in the replication setup in order to perform the migration from classic replication (based on binary logs information) to the transaction-based (GTID) replication which means that the migration process requires downtime.

The online migration to GTID replication is not yet available.
Facebook and Booking.com provided some MySQL patches for this, but they are not yet contained in Oracle's binaries.
So, if you can't afford a downtime during the migration process, then you might not be able to make the change.

Non transactionally safe statement will raise errors now

It is required to enable the system variable (enforce_gtid_consistency) on all servers inside the GTID replication setup which prevent executing the non transactionally safe statements (check GTID restrictions) like:

• CREATE TABLE .. SELECT.
• CREATE TEMPORARY TABLE (inside a transaction).
• Statements that update non-transactional tables inside a transaction.

So, you will have to fix your application first if it contains any of the above statements before using GTID replication.

MySQL Performance in GTID

It is required to enable the variables (bin_log and log_slave_updates) on - at least - the slave servers which affects the performance on those slaves negatively.

So, the performance should be tested very well before the production migration to GTID replication.

mysql_upgrade script

The mysql_upgrade script's problem when executed on a server having gtid_mode=on has been fixed since MySQL 5.6.7, but it is still not recommended to execute mysql_upgrade when gtid_mode=on as it might change system tables that is using MyISAM, which is non transactional.

Errant transactions!

Transactions which are executed on a slave apart from the replication transactions (i.e. not executed on the master) are called "Errant transactions", those transactions cause trouble if that slave later is promoted to be a new master in the a failover process.
Once the other slaves connect to the new master, they send the value of gtid_executed and the master in turn checks those values, compares it with its own gtid_executed set and sends back all missing transactions (the errant transactions) to the slaves which leads to one of the following problems:

• If those transactions still exist in the new master's binary log files, they will be replicated to the other slave which was not intentional when those were executed only on the slave (new master).
• If those transactions do no longer exist in the new master's binary log file, the replication will break on all slaves.

How to avoid such problem?

• Choose some slaves to be possible candidates for promotion in case of failover. Thus, stand alone transactions (which are not coming from the master) should NOT be executed there.
• Use one of the MySQL utilities (mysqlfailover or mysqlrpladmin) to find out if there are any errant transactions on the slave before the promotion or not.

Filtration on the slave

In some cases we might need to make filtration to the replication on the slave(s) i.e. not all tables' or databases' changes are propagated to the slave by using the system variables (replicate_ignore_db or replicate_ignore_table). When the slave receives transactions from the master which modify those ignored tables or databases, it simply skips executing them and when the slave restarted it sends the gtid_excuted to the master and the master finds the missing transactions (those for the ignored tables or databases) and sends them back to the slave.

Again, that leads to one of the following two conditions:

• If those transactions still exist in the master's binary log files, then no problem as the slave will skip executing them again!!
• If those transactions are no longer there in the master's binary log files, the replication will break on the slave.

Well, the above problem is supposed to be fixed in MySQL 5.6.18 (Bug #70048) The fix is injecting empty transactions on the slave for those ones modifying ignored tables or databases instead of just skipping them, and when the slave restarted they won't be sent back again from the master.

I listed the above problem although it should be fixed now because I want to mention that having MySQL always updated to the recent release is a good practice to avoid such problems and to get the most bug fixes.

Conclusion

The following are the main things which should be considered before using GTID:

• Migration from classic replication to transaction-based (GTID) replication requires downtime.
• Non-transactionally safe statements will not be executed in GTID replication.
• MySQL performance is a little bit slower in GTID replication, especially, on the slaves.
• mysql_upgrade script might cause troubles on a server having GTID_MODE=ON and it should be tested first.
• Errant transactions might break the replication in the failover process, thus, planning slaves for promotion will avoid falling in such cases.
• Some GTID bugs are fixed now (like slave filtration issue), thus MySQL server should be updated to the latest version once there is a new release.
• New bugs are expected to be discovered, so the application should be tested very well with GTID before performing the migration on production.