Setting up MariaDB Replicas - the easy way

The MariaDB page on replication is good, but I feel it lacks a few details to make things easier. Specifically, in moving the data between the master and slave to be able to get the replica running with as little effort as possible.

If we assume that the Master has been configured with the steps in the MariaDB Guide, we can then look at how to get data to the slave for the initial replication to happen.

In my configuration, I use a master server already configured with SSL - you should really do the same for your master BEFORE you set up any replication. I use a LetsEncrypt certificate and this reference.

Using the script below, we can skip the Getting the Master’s Binary Log Co-ordinates step, and export the GTID in a dump - and import that into the new slave in the one command. When running, mariadb-dump will automatically lock the database tables, and unlock again after the transfer has completed.

#!/bin/bash
declare -x MYSQL_PWD="MySqlRootPassword"
declare -x MARIA_MASTER="my.master.server.example.com"
declare -x MARIA_REPL_USER="replication_user"
declare -x MARIA_REPL_PASS="replication_password"

echo "Stopping the local slave (if running)..."
mysql -e "stop slave;"

echo "Transferring initial dataset... Please wait..."
mariadb-dump -A --gtid \
	--add-drop-database \
	--compress=true \
	--master-data=1 \
	--include-master-host-port \
	--ssl=true \
	-h $MARIA_MASTER \
	-u root | mysql

echo "Configuring slave..."
mysql -e "
CHANGE MASTER TO
    MASTER_HOST=\"$MARIA_MASTER\",
    MASTER_USER=\"$MARIA_REPL_USER\",
    MASTER_PASSWORD=\"$MARIA_REPL_PASS\",
    MASTER_PORT=3306,
    MASTER_SSL=1,
    MASTER_SSL_CA=\"/etc/pki/tls/certs/ca-bundle.trust.crt\",
    MASTER_SSL_VERIFY_SERVER_CERT=1;
start slave;
"

After this script completes, you can then check the status of the slave - and confirm the values of Slave_IO_Running and Slave_SQL_Running with:

SHOW SLAVE STATUS \G;

Keep this script handy, as if replication breaks for whatever reason, you can run it again to resync to the master server, and the existing databases on the slave will get dropped as the import happens. Keep in mind though that it won’t drop databases that don’t exist on the master anymore.

NOTE: If you have a large or busy database, you might be better served using the mariabackup tool. This tool will make a local export of all the data to allow you to transfer it out-of-band and therefore reduce the amount of time the master database is locked. MariaDB have a guide to using this tool here. While its more steps, your locking time will be greatly reduced.

I also use the following on the replica in /etc/my.cnf.d/replication.cnf to configure the slave:

[mariadb]
slave_compressed_protocol = 1
log-basename = <slave hostname>

Change <slave hostname> to the hostname of the configured slave. This will use compression for the slave, which is helpful for replication over WAN connections, and setting log-basename will ensure that if the slave host changes its name at some point in the future, that replication won’t break.