Enabling GTIDs for server replication in MariaDB 10.0

Replication has been one of the most popular MySQL features since it made its way into the application more than a decade ago. However, 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 ofGlobal Transaction IDs, which enable some new, 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 organization are willing to allow.

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

A Simple HA Implementation

Let’s start with a common high-availability (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 – 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, which you can read about in the documentation. 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 a domain ID for server A, 2 for B, and 3 for C 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 the 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 purposes the 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 10.0 and review the binary log files with the mysqlbinlog, you’ll notice that every transaction in the MariaDB 10.0 binary logs has the GTID already included. For the binary log in the examples used in this article:

# 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 10.0 piques your curiosity and encourages you to explore the new replication features.