GTID Replication – Differences between MySQL and MariaDB

In this blog post, I’m gonna highlight the differences in GTID replication between MySQL and MariaDB

I will not go thru step-by-step setup, because they are many resources out there that do.

The main reason to use GTID is that it makes it easier to track and compare replicated transaction between master and replica; hence, allowing simpler failover and recovery.

Here are the differences between MySQL and MariaDB

1. Composition of GTID

In MariaDB, it is composed of three separated dashed numbers like x-y-z

• x: first number – domain ID
• y: second number – server ID
• z: third number – sequence number

In MySQL, there are 2 parts:

• source_id
• transaction_id

2. Enabling GTIDs

To enable GTID, we have to set the following parameters in my.cnf

In MySQL:

• gtid_mode
• enforce_gtid_consistency

gtid-mode=ON
enforce-gtid-consistency

In MariaDB:

• gtid_strict_mode

gtid_strict_mode=1

enforce_gtid_consistency does not exist in MariaDB

3. CHANGE MASTER TO statement

In order for the Replica to identify its Master (data source), and to use GTID-based auto-positioning, we need execute the CHANGE MASTER TO statement. In MySQL, we use the MASTER_AUTO_POSITION option to tell the replica that transactions will be identified by GTIDs.

Example In MySQL:

change master to
master_host = '192.168.1.120',
master_port=3306, 
master_user = 'repl',
master_password = 'password',
master_auto_position=1;

In MariaDB, A slave is configured to use GTID by CHANGE MASTER TO master_use_gtid=slave_pos. The replication will start at the position of the last GTID replicated to slave when the slave connects to the master. Refer to the official documentation for more info https://mariadb.com/kb/en/gtid/

Example In MariaDB:

change master to 
master_host='192.168.1.120', 
master_port=3306, 
master_user='repl', 
master_password='password', 
master_use_gtid=slave_pos;

Cheers!