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:
- Failover is easier than with file-based replication.
- 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!
