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

create a MariaDB.repo file under /etc/yum.repos.d/ , add the below content in the file # http://downloads.mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.2/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1

1 2 3 4 5 6 7

create a MariaDB.repo file under /etc/yum.repos.d/ , add the below content in the file # http://downloads.mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.2/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1

1.b Install MariaDB softwares as applicable

yum install -y MariaDB-server MariaDB-client MariaDB-common MariaDB-compat MariaDB-shared

1	yum install -y MariaDB-server MariaDB-client MariaDB-common MariaDB-compat MariaDB-shared

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.

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd innodb_buffer_pool_size=256M server_id=10 log-bin log_slave_updates=1 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd innodb_buffer_pool_size=256M server_id=10 log-bin log_slave_updates=1 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid   # # include all files from the config directory # !includedir /etc/my.cnf.d

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

master1# systemctl start mysql

1	master1# systemctl start mysql

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

5. Start mysql in slave1

slave1# systemctl start mysql

1	slave1# systemctl start mysql

6. Create some dummy tables in master1

master1# mysql -A mysql> create database test_gtid_database; mysql> CREATE TABLE `test_table_gtid` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `address` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=37951 DEFAULT CHARSET=latin1;

1 2 3 4 5 6 7 8 9

master1# mysql -A mysql> create database test_gtid_database;   mysql> CREATE TABLE `test_table_gtid` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `address` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=37951 DEFAULT CHARSET=latin1;

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

screen -S test_gtid_screen master1# while true ; do `mysql -e " use test_gtid_database ; Insert into test_table_gtid (name,address) values ('xxx','yyy') ; "` | sleep 5;done;

1 2 3

screen -S test_gtid_screen   master1# while true ; do `mysql -e " use test_gtid_database ; Insert into test_table_gtid (name,address) values ('xxx','yyy') ; "` | sleep 5;done;

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

screen -x test_gtid_screen

1	screen -x test_gtid_screen

— Setup the GTID replication

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

master1# mysql -A mysql> grant replication slave on *.* to 'replicant'@'<slave1 ip address>' identified by '<some password>'; master1# implement a firewall rule to allow the 3306 port on this master1 iptables -I INPUT -m tcp -p tcp --dport 3306 -j ACCEPT

1 2 3 4 5 6

master1# mysql -A mysql> grant replication slave on *.* to 'replicant'@'<slave1 ip address>' identified by '<some password>';   master1# implement a firewall rule to allow the 3306 port on this master1   iptables -I INPUT -m tcp -p tcp --dport 3306 -j ACCEPT

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

master1# mysqldump —master-data=2 -A | gzip -1 > all_data.sql.gz

1	master1# mysqldump —master-data=2 -A | gzip -1 > all_data.sql.gz

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

scp /<path>/all_data.sql.gz root@<slave1 ip address>:/<path>/all_data.sql.gz

1	scp /<path>/all_data.sql.gz root@<slave1 ip address>:/<path>/all_data.sql.gz

10. Restore the full backup in slave1

slave1# zcat <path>/all_data.sql.gz | mysql

1	slave1# zcat <path>/all_data.sql.gz | mysql

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

master1# zless <path>/all_data.sql.gz | more

1	master1# zless <path>/all_data.sql.gz | more

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

master1# mysql -A mysql> select binlog_gtid_pos('binary log file you found above',position);

1 2 3

master1# mysql -A   mysql> select binlog_gtid_pos('binary log file you found above',position);

You will get the gtid from the above query.

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

slave1# mysql -A mysql> set global gtid_slave_pos=“<gtid you got from the above query>”; mysql> CHANGE MASTER TO MASTER_HOST='<ip address of the master1>',MASTER_USER='replicant',MASTER_PASSWORD='<password you set for the replicant user in the grant statement in the master1>',MASTER_USE_GTID=slave_pos; mysql> start slave; mysql> show slave status \G

1 2 3 4 5 6 7 8 9

slave1# mysql -A   mysql> set global gtid_slave_pos=“<gtid you got from the above query>”;   mysql> CHANGE MASTER TO MASTER_HOST='<ip address of the master1>',MASTER_USER='replicant',MASTER_PASSWORD='<password you set for the replicant user in the grant statement in the master1>',MASTER_USE_GTID=slave_pos;   mysql> start slave;   mysql> show slave status \G

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

slave1# mysql -A mysql> show variables like 'gtid%';

1 2 3

slave1# mysql -A   mysql> show variables like 'gtid%';

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

master2# gtid_domain_id=1 # This is the domain_id which uniquely identifies new GTID master log-bin log_slave_updates=1 and restart mysql in master2 master2# systemctl stop mysql master2# systemctl start mysql

1 2 3 4 5 6 7 8 9 10

master2#   gtid_domain_id=1 # This is the domain_id which uniquely identifies new GTID master log-bin log_slave_updates=1   and restart mysql in master2   master2# systemctl stop mysql master2# systemctl start mysql

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

master1# log_slave_updates=1 and restart mysql in master1 master1# systemctl stop mysql master1# systemctl start mysql

1 2 3 4 5 6 7 8

master1#   log_slave_updates=1   and restart mysql in master1   master1# systemctl stop mysql master1# systemctl start mysql

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

master2# mysql -A mysql> grant replication slave on *.* to 'replicant'@'<master1 ip address>' identified by '<some password>';

1 2 3

master2# mysql -A   mysql> grant replication slave on *.* to 'replicant'@'<master1 ip address>' identified by '<some password>';

Now attach the master1 as a slave to master2

master1# mysql -A mysql> CHANGE MASTER TO MASTER_HOST='<ip address of the master2>',MASTER_USER='replicant',MASTER_PASSWORD='<password you set for the replicant user in the grant statement in the master2>',MASTER_USE_GTID=current_pos; mysql> start slave; mysql> show slave status \G

1 2 3 4 5 6 7

master1# mysql -A   mysql> CHANGE MASTER TO MASTER_HOST='<ip address of the master2>',MASTER_USER='replicant',MASTER_PASSWORD='<password you set for the replicant user in the grant statement in the master2>',MASTER_USE_GTID=current_pos;   mysql> start slave;   mysql> show slave status \G

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.

master1 < —> master2 | \/ slave2

1 2 3 4

master1 < —> master2 | \/ slave2

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