MySQL supports three types for binlog format. For safer binlog based replication its recommended to use ROW based replication. But even though in some worst cases this leads to data inconsistency. Later MySQL came up with the concept of GTID (global transaction identifiers) which generates the unique binlog entries to avoid any data inconsistency. This feature supports in MySQL 5.6+. Percona MySQL Servers is also using the same structure of MySQL’s GTID. But MariaDB GTID is bit different.
As a DBA, I worked a lot in MySQL replication and troubleshooting but not much with GTID. I got stuck with a migration because of this GTID. Then I have confirmed the possibilities with one my Friend from mydbops. Then I started to understand deeply about this GTID in MySQL and MariaDB. After that I taught it worth to share.
What is the Purpose of GTID?
GTID will generate a globally unique transaction ID for each transaction. Lets see a simple example. You are going to replicate a Database from Server M to Server S. You have been set the Master binlog as binlog-00001 and its position as 120. Somehow after the binlog position 150, the replication got break, so by mistake you have mentioned to start replication from 120. This will re-apply all the transactions from binlog position 120. This may lead to duplicate records.
But GTID has an unique ID for each transaction, If you start replication with the GTID XXX:120 then the slave will start keep track on the applied GTID. So again if we start to re-apply the transaction, it’ll not accept those records.
GTID in MySQL:
In MySQL, there are two portions for GTID. The first portion refers to the Server UUID. This UUID is a 32 Character Random string. This value is taken from the auto.cnf file which is located in mysql data directory. The second portion is for sequence.
If you have a single master, then in slave the GTID has represented as single expression.
This refers that the transaction 1 to 10 has been originated from the Server which has the UUID as 2defbe5b-a6b7-11e8-8882-42010a8e0008.
Lets to some tests:
Prepare a database with a table.
Now open your binlog file with the following command.
If you are using Mysql 5.6, then you must need to restart to enable the GTID.
Mysql 5.7 we can do that in online.
If you are using replication without GTID, then you need to enable the GTID on the Master, then Slave.
On Master and Slave, you should have different UUID in the auto.cnf
GTID in MariaDB
Unlike MySQL, MariaDB has implemented a new type of GTID, it has 3 portions. We don’t want to restart the to enable GTID in MariaDB.
If you are using multi-master replication, lets say 3 node setup. The each group commit order should be ordered in the binlog on other servera. You are inserting the 3 records on each node. Due to some network issues, the Node 3 has disconnected, mean while Node 2 executed the drop table command and some sessions are inserting some data on the Node 3. When the network issue is resolved then the Node 3 will lose its track that where it should replicate the data and which node’s data should be applied first. This Domain ID will solve this. So the slave has to know where to start the transaction for Node 1 and Node 2.
This is the mysql’s parameter server-id. This is its second portion where the event group is first logged into the binlog.
This is same as MySQL’s sequence order.
Testing with MariaDB:
Check the Binlog file:
Lessons I learned from the GTID:
MariaDB to MySQL replication is not possible with GTID.
In MySQL master/slave all nodes must have GTID. You can’t enable it on one single node.
MySQL to MariaDB replication possible where GTID enabled on MySQL.
Jean-François Gagné – Has done the GTID without restart the MySQL 5.6. They used some custom patched version of MySQL. You can read it from this link.