Enabling GTIDs for Server Replication in MariaDB Server

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.

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 multisource replication, which you can read more about in the domain ID article in the MariaDB knowledgebase. 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 1 for server A’s domain ID, 2 for server B’s and 3 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

If server C is currently replicating from server A using MASTER_LOG_FILE and MASTER_LOG_POS, then we first need to stop the slave threads on server C with STOP SLAVE:

MariaDB [mysql]> STOP SLAVE;

After the slave threads are stopped, then we need to execute SHOW SLAVE STATUS on server C:

MariaDB [mysql]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: NULL
                  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: No
            Slave_SQL_Running: No
...
          Exec_Master_Log_Pos: 510
...
1 row in set (0.00 sec)

We need to extract the values of Relay_Master_Log_File and Exec_Master_Log_Pos from the output. In this case, the values are mariadb-bin.000001 and 510.

Now, we can use these coordinates along with the BINLOG_GTID_POS function on server A, which is server C’s master, to find the GTID position that corresponds to these binary log coordinates.

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 GTID position that corresponds to these binary log coordinates on the given server. With this value, we can now modify the slave configuration on server C by executing the following statements:

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
...
          Exec_Master_Log_Pos: 510
...
                   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.The above process would have to be repeated on Server B as well.

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 the MariaDB Platform High Availability Guide and our latest webinar, Best Practices and Recommendations for Disaster Recovery with MariaDB Platform.