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)
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:
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 :
1. Here slave1 is replicating to master1
1.a Install MariaDB on both master1 and slave1 using Yum repository
1234567 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 = MariaDBbaseurl = http://yum.mariadb.org/10.2/centos7-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1
1.b Install MariaDB softwares as applicable
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.
123456789101112131415161718192021 [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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/Systemdinnodb_buffer_pool_size=256Mserver_id=10log-binlog_slave_updates=1[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-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
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
1 slave1# systemctl start mysql
6. Create some dummy tables in master1
123456789 master1# mysql -Amysql> 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
123 screen -S test_gtid_screenmaster1# 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
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
123456 master1# mysql -Amysql> 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 master1iptables -I INPUT -m tcp -p tcp --dport 3306 -j ACCEPT
8. Take a mysqldump logical backup to restore it in the slave
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
1 scp /<path>/all_data.sql.gz root@<slave1 ip address>:/<path>/all_data.sql.gz
10. Restore the full backup in slave1
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
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.
123 master1# mysql -Amysql> 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
123456789 slave1# mysql -Amysql> 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
123 slave1# mysql -Amysql> 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
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
12345678910 master2#gtid_domain_id=1 # This is the domain_id which uniquely identifies new GTID masterlog-binlog_slave_updates=1and restart mysql in master2master2# systemctl stop mysqlmaster2# systemctl start mysql
And now master1$ is going to be the slave as well so include the below variable in /etc/my.cnf
12345678 master1#log_slave_updates=1and restart mysql in master1master1# systemctl stop mysqlmaster1# 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
123 master2# mysql -Amysql> grant replication slave on *.* to 'replicant'@'<master1 ip address>' identified by '<some password>';
Now attach the master1 as a slave to master2
1234567 master1# mysql -Amysql> 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.
1234 master1 < —> master2|\/slave2
I hope you try these steps and post me a comment about how does it works,Enjoy!