MariaDB – Setup GTID Replication using MariaBackup Step-by-Step

In this blog post, I’m going to show you how to setup GTID replication using Mariabackup.

There are 2 main benefits of using global transaction:

1. Failover is easier than with file-based replication.
2. the state of the slave is recorded in a crash-safe way.

Here are the general steps:

• Enable binary logging on the master
• Enable GTID
• Create a replication user on the master
• Set a unique server_id on the slave
• Take backup from the master
• Restore on the slave
• Execute the CHANGE MASTER TO command
• Start the replication

1. Ensure that the server_id value and bind_address are configured differently in my.cnf in each of the server that will be part of the replication.

In this example, we will configure a 2 node master-slave setup. The bind-address is the hostname IP.

vi my.cnf

In Master:

server_id=1
bind-address=192.168.1.115

In Replica:

server_id=2
bind-address=192.168.1.131

2. Enable binary logging and GTID strict mode in both servers

show global variables like 'log_bin';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

show global variables like '%gtid_strict_mode%';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| gtid_strict_mode | ON    |
+------------------+-------+

If bin logging is not enabled, you may do so by adding the line below in my.cnf

log-bin = db1-bin

# Or specify an different path

log-bin = /mariadb/bin/logs/bin_logs/bin_log

Restart DB service for the change to take effect.

sudo service mysql stop

sudo serivice mysql start

If gtid is not enabled, add the line below in my.cnf, then enable it globally.

Add this line in cnf

gtid_strict_mode=1

Login to MariaDB, then set global_script_mode=1.

set global gtid_strict_mode=1;

3. Create a user in Master.

The replica is going to use this user connection to read the binary logs on the master and then put those into the relay logs on the replica.

CREATE USER 'repl'@'%' IDENTIFIED BY 'P@$$w0rd';
GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';

4. Install qpress (As root) in both Master and Slave for compression/decompression.

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y

yum install qpress -y

5. In Master, Take a full backup of the database using Mariabackup.

mariabackup --defaults-file=/etc/my.cnf --backup --compress \
--target-dir=/mariadb/backup/full_backup --user=username \
--password=pass --backup --compress --parallel=4

6. In Replica, create a directory where we will place the backup from Master

mkdir -p /mariadb/backup/rep

7. In Master, use scp to transfer the entire backup image to the replica.

# Go to the directory where you placed the backup 
cd /mariadb/backup

scp -rp full_backup mysql@192.168.1.131:/mariadb/backup/rep

8. In Replica, Stop DB Service

sudo service mysql stop

# Verify that DB service has been stopped
ps -ef| grep mysqld

9. In Replica, Remove all contents in Data Directory.

mkdir -p /mariadb/data/old_data
mv /mariadb/data/* /mariadb/data/old_data/
rm -rf /mariadb/data/old_data/

# Ensure that the data directory is empty
cd /mariadb/data
ls -la

10. In Replica, copy the backup image to data directory.

cp -rp /mariadb/backup/rep/full_backup /mariadb/data

11. In Replica, decompress and prepare backup image.

MEMORY=`grep -w innodb_buffer_pool_size /mariadb/bin/etc/my.cnf | cut -d'=' -f2`

mariabackup --decompress --parallel=4 --remove-original --use-memory=$MEMORY --target-dir=full_backup

12. In Replica, prepare backup.

MARIADB_VERSION=`rpm -qa | grep MariaDB-server | grep -v debuginfo | cut -d'-' -f3 | cut -d'.' -f2`

if [ $MARIADB_VERSION -le 3 ] ; then mariabackup --prepare --apply-log-only --use-memory=$MEMORY --target-dir=full_backup; fi

mariabackup --prepare --use-memory=$MEMORY --target-dir=full_backup

13. In Replica, cleanup data directory, and move all files from full_backup to data directory.

ls -1v /mariadb/data | grep -v $FULL | xargs rm -rf
mv /mariadb/data/$FULL/* /mariadb/data

14. In Replica, Delete the full_backup directory.

cd /mariadb/data
rm -rf /mariadb/data/full_backup

15. In Replica, Rotate error logs.

cd /mariadb/bin/logs/db_logs
mv error_mariadb.log error_mariadb_`date +%Y%m%d_%H%M%S`.log
touch error_mariadb.log

16. In Replica, Start DB Service.

sudo service mysql start

# Check if there are any errors
cat /mariadb/bin/logs/db_logs/error_mariadb.log

17. In Replica, Check GTID. Take note of the GTID, because we need to use it for the next step.

cat /mariadb/data/xtrabackup_info | grep -i GTID

18. In Replica, Login in to the DB, and set the global gtid_slave_pos.

stop slave; reset slave; reset slave all; reset master; set global gtid_slave_pos='above_GTID_number_from_step18';

19. In Replica, still logged in to the DB, execute the change master to command.

change master to master_host='10.92.146.102', master_port=6603, master_user='repl', master_password='P@$$w0rd', master_connect_retry=10, master_use_gtid=slave_pos;

20. In Replica, set enable read_only.

set global read_only=1;

21. Start slave.

start slave;

22. Check replication status.

show slave status\G

Cheers!