Enabling GTIDs for Server Replication in MariaDB Server 10.2

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.)

MariaDB implements GTIDs differently from MySQL, making it possible to enable and disable them with no downtime. Here’s how.

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:

MariaDB [(none)]> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mariadb-bin.000001
        Position: 510
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

We can use these coordinates to find the information we need in order to use GTIDs from the active master by running this command:

SELECT BINLOG_GTID_POS('mariadb-bin.000001', 510);
+--------------------------------------------+
| BINLOG_GTID_POS('mariadb-bin.000001', 510) |
+--------------------------------------------+
| 1-101-1                                    |
+--------------------------------------------+
1 row in set (0.00 sec)

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:

STOP SLAVE;
SET GLOBAL gtid_slave_pos = '1-101-1';
CHANGE MASTER TO master_use_gtid=slave_pos;
START SLAVE;

Check the slave status to see that the change has taken effect:

MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 510
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 642
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 1-101-1
1 row in set (0.00 sec)

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:

# at 314
#140807 14:16:01 server id 101  end_log_pos 352         GTID 1-101-1
/*!100001 SET @@session.gtid_domain_id=1*//*!*/;
/*!100001 SET @@session.server_id=101*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;

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.