MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

I originally wrote this post in 2014 after the release of MariaDB Server 10.0. Most of what was in that original post still applies, but I've made some tweaks and updates since replication and high availability (HA) remain among the most popular MariaDB/MySQL features.

Replication first appeared on the MySQL scene more than a decade ago, and as replication implementations became more complex over time, some limitations of MySQL’s original replication mechanisms started to surface. To address those limitations, MySQL v5.6 introduced the concept of global transaction identifiers (GTIDs), which enable some advanced replication features. MySQL DBAs were happy with this but complained that in order to implement GTIDs, you needed to stop all the servers in the replication group and restart them with the feature enabled. There are workarounds; for instance, Booking.com documented a procedure to enable GTIDs with little or no downtime, but it involves more complexity than most organizations are willing to allow. (Check out this blog post for more on how Booking.com handles replication and high availability.)

A Simple HA Implementation

Let’s start with a common HA implementation with three servers running MariaDB 10.0 or higher: One active master (server A), one passive master (server B) and a slave replicating from the active master (server C). The active and passive masters are set up to replicate master-master.

I’m not showing it, but between servers A and B and the application, you would want an additional layer to switch database traffic to server B in case A fails. Some organizations might deploy another slave replicating from B or a mechanism to move server C to replicate from B such as Master High Availability Manager (MHA), but let’s keep things simple here.

Step 1: Setting Up the Configuration Files

GTIDs in MariaDB 10 have three parts: server ID, transaction ID, and domain ID. The server ID and transaction ID are similar in concept to those found in MySQL 5.6. In MariaDB, the server ID is a number and not a UUID, and it is taken from the server_id global variable. The domain ID is an important new concept for multi-source replication, which you can read more about in the domain ID article in the MariaDB knowledge base. In our case, this is the only variable we need to set up; the server ID should already be set up if replication is functional. Let’s use one for server A's domain ID, two for server B's, and three for server C's by executing commands like the following on each of the servers:

SET GLOBAL gtid_domain_id = 1;

Keep in mind that each session can have its own value for gtid_domain_id, so you'll have to reset all existing connections to properly reset the gtid_domain_id. Finally, persist the values in the corresponding my.cnf files:

# Domain = 1 for active master: server A
gtid-domain-id=1

Step 2: Changing Replication on the Slave

Running SHOW MASTER STATUS on server A, the active master, shows the current coordinates for its binary log file:

Note that the GTID can be an empty string; for clarity, these examples work with non-empty GTID values. The result from the function call is the current GTID, which corresponds to the binary file position on the master. With this value, we can now modify the slave configuration on servers B and C, executing the following statements on each of them:

The last two lines of SHOW SLAVE STATUS indicate that the slave is now using GTIDs to track replication.

Conclusion

As you can see, the procedure to enable GTIDs is straightforward and doesn’t require restarting servers or planning for downtime. If you want to revert back to regular replication using binary log position, you can do so by using RESET SLAVE on the slave and resetting the proper binary log coordinates the traditional way. In fact, once you update your servers to use MariaDB Server and review the binary log files with the mysqlbinlog, you'll notice that every transaction in the MariaDB binary logs has the GTID already included. For the binary log in the examples used in this article, here's what you see:

I hope that the ease of implementing GTIDs in MariaDB Server piques your curiosity and encourages you to explore the variety of replication features. For more on replication and other high availability/disaster recovery strategies, check out our white paper, High Availability With MariaDB TX: The Definitive Guide.

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.