Implementation of GTID replication using MariaDB

 

GTID replication is a simple High Availability solution (HA) offered in MySQL and MariaDB though the implementation technique is unique but compared to MySQL using MariaDB is easy to implement.

As we know Replication is the solution where Master’s data is replicated in the Slave server and the Slave server replay the master’s binary log events locally and be in sync with the master’s data to maintain the exact copy of the master’s data. Slave server also keeps track of the binary log file and offset of where the last event applied in the slave.

GTID (Global Transaction ID) introduces the new event in the event group also called as the Transaction ( set of events of transactional or non transactional) and uniquely identifies the transactional event group in the master, GTID will be always unique across all the servers involved in the replication topology.

GTID consists of three parts –
Example of GTID – 0-10-12345

0 – is known to be Domain ID ( Any integer value defined for each of the server and it should be unique, default is 0 and only one of the server can have default domain-id,this is the 32 bit unsigned integer)
10- is known to be server-id of that server(Any integer value defined for that server -id)
12345 – is known to be transaction identification, this number keeps changing as and when transaction occurs in the masters/slave)

Benefits:

Easy to implement replication and change the slave’s master servers

This is possible because slave always know the GTID of the last event group from the master’s binary log applied in the slave, so it is easy to re attach or resume the replication and also attach to a different master whereas in the non GTID replication slave only know the binary log file name and position and it is difficult to guess correct file name and offset on a new master.

The state of the slave is crash safe

Since slave keeps track of the GTID position of the last event group in the binary log file from the master applied in the slave , this also updates the mysql.gtid_slave_pos table simultaneously as the data updates, so slave will always known to be updated with latest GTID position , and in case of slave’s crash recovery, slave can be easily attached to the master with its current GTID pos.

Because of the above benefits GTID replication is highly recommended over the NON GTID replication:

Implementation :

We want to set up below replication topology with GTID

MariaDB version : 10.0 and above ( Now the current GA version is 10.3)

I have used MariaDB 10.1 version.

OS version : CentOS 7.5

Replication topology :

Use case 1 :

master1->slave1

Steps:

1. Here slave1 is replicating to master1

1.a Install MariaDB on both master1 and slave1 using Yum repository

 

1.b Install MariaDB softwares as applicable

 

2. Replace the /etc/my.cnf contents with the below configuration in master1
Note : The below configuration is the very basic one with all system variables with default values , as per your server’s requirements you would want to include/change relevant variables, also my idea here is to keep this implementation simple enough to make you understand the concepts easily.

 

As you can see in the above configuration gtid_domain_id is not used , which means that gtid_domain_id is 0 (default value)

3. Repeat the above steps in slave1 as well and change only the server_id value in slave1’s /etc/my.cnf to be unique.

4. Start mysql in master1

 

Note: Recommend to run mysql_secure_installation to set the root password to be more secure .

5. Start mysql in slave1

 

6. Create some dummy tables in master1

 

Now insert rows into this table in the interval of some seconds

I would do something in the screen session in master1, if you don’t have the screen , install it as below
yum install screen

and then create screen session as below

 

You can see I am inserting rows in the interval of every 5 seconds.
If you want to exit from the while loop , you can just abort this by hitting control c
and to exit from the screen session just hit control a d and screen session will get detached, to rejoin the screen session

 

— Setup the GTID replication

7. Create a replication user and grant required privileges to that user in the master1

 

8. Take a mysqldump logical backup to restore it in the slave

 

9. Send this backup file to slave server by using secure copy

 

10. Restore the full backup in slave1

 

11. Now get the GTID position from the binary log file and the position recorded in the backup file all_data.sql.gz

 

You will find the CHANGE MASTER statement at the top of this file with the binary log file and the position.

 

You will get the gtid from the above query.

12. Now attach the slave1 to the master1 using above gtid

 

You will find the slave status with USING_GTID column with a value ‘Slave_Pos’, that means this slave1 using the GTID position established GTID replication.

Also Gtid_IO_Pos populated with gtid position currently it has applied and this value will keep changing as and when any transaction occurs.If you see Seconds_Behind_Master: 0 then slave1 is fully caught up with the master1.

13. You can look at the GTID variables in slave1

 

Use case 2 :

Now in scenario 2 , we want to setup the Master<->Master replication using GTID.

14. Replication topology as below

master1<->master2

master2 will be the slave of the master1 and master1 will be the slave of master2.

Now in master2 edit the /etc/my.cnf file and include the below variables

 

And now master1$ is going to be the slave as well so include the below variable in /etc/my.cnf

 

15. Repeat the same steps used to setup replication master1->slave1 above but use master2 for slave1, once you are done with the unidirectional replication setup, now attach the master1 as a slave to master2.

Here you need not run binlog_gtid_pos function to know slave gtid position because master2 already recorded the current position of last event applied on it.

you just have to attach the master1 slave to the current_pos of the master2 like below

Create a replication user and grant privileges to master1 to be slave of master2

 

Now attach the master1 as a slave to master2

 

Now you will see the USING_GTID column with the value ‘Current_Pos’ also GTID_IO_Pos column with two gtid positions (one from domain_id starting from master2 and the other from master1).

Use case 3:

Let’s think of another use case where you want to add another slave2 to the current master1<->master2 setup

16. This is quite easy , just take a backup with master-data=2 from any of those masters , restore that backup in slave2 and finally attach that slave2 to the master where you took backup with master_use_gtid=current_pos in the “CHANGE MASTER…” command as explained in the above steps,  start this slave2 and check the status in the show slave status.

In fact this setup will now be a multi-source replication.

 

I hope you try these steps and post me a comment about how does it works,Enjoy!

Shashishekar H A
Author: Shashishekar H A

I am Shashishekar H A , currently residing in San Antonio , Texas , US, I have been working on MySQL may be since 4+ years , as I am truly passionate about MySQL , I have handled Percona XtraDB Cluster Installtion and Management, MySQL High Availability , Chained Replication and DR recovery setup,Query Optimization and Performance Tuning during my MySQL career. I am expertise in MySQL -Replication, Performance Tuning, Percona Xtrabackup configuration ,xtrabackup and Point in time Recovery (PITR) and also good knowledge on XtraDB Cluster, Percona toolkit and MariaDB Galera Implementation.

Leave a Reply

Your email address will not be published. Required fields are marked *