Configuring MariaDB Replication between Two MariaDB Galera Clusters
Contents
- Configuring the Clusters
- Setting up Replication
- Start the First Cluster
- Backup the Database on the First Cluster's Master Node and Prepare It
- Copy the Backup to the Second Cluster's Slave
- Restore the Backup on the Second Cluster's Slave
- Bootstrap the Second Cluster's Slave
- Create a Replication User on the First Cluster's Master
- Start Replication on the Second Cluster's Slave
- Check the Status of the Second Cluster's Slave
- Start the Second Cluster
- Circular Replication
- Create a Replication User on the Second Cluster's Master
MariaDB replication can be used to replication between two MariaDB Galera Clusters. This article will discuss how to do that.
Configuring the Clusters
Before we set up replication, we need to ensure that the clusters are configured properly. This involves the following steps:
- Set
log_slave_updates=ON
on all nodes in both clusters. See Configuring MariaDB Galera Cluster: Writing Replicated Write Sets to the Binary Log and Using MariaDB Replication with MariaDB Galera Cluster: Configuring a Cluster Node as a Replication Master for more information on why this is important.
- Set
server_id
to the same value on all nodes in a given cluster, but be sure to use a different value in each cluster. See Using MariaDB Replication with MariaDB Galera Cluster: Setting server_id on Cluster Nodes for more information on what this means.
- Set
wsrep_gtid_mode=ON
and follow the other directions at Using MariaDB GTIDs with MariaDB Galera Cluster: Wsrep GTID Mode to properly configure wsrep GTID mode. For example, it is important to ensure thatwsrep_gtid_domain_id
is set to the same value on each node in a given cluster, but set to a different value on each cluster.
Setting up Replication
Our process to set up replication is going to be similar to the process described at Setting up a Replication Slave with Mariabackup, but it will be modified a bit to work in this context.
Start the First Cluster
The very first step is to start the nodes in the first cluster. The first node will have to be bootstrapped. The other nodes can be started normally.
Once the nodes are started, you need to pick a specific node that will act as the replication master for the second cluster.
Backup the Database on the First Cluster's Master Node and Prepare It
The first step is to simply take and prepare a fresh full backup of the node that you have chosen to be the replication master. For example:
$ mariabackup --backup \ --target-dir=/var/mariadb/backup/ \ --user backup_user --password backup_passwd
And then you would prepare the backup as you normally would. For example:
$ mariabackup --prepare \ --target-dir=/var/mariadb/backup/ \ --user backup_user --password backup_passwd
Copy the Backup to the Second Cluster's Slave
Once the backup is done and prepared, you can copy it to the node in the second cluster that will be acting as slave. For example:
$ rsync -avrP /var/mariadb/backup c2dbserver:/var/mariadb/backup
Restore the Backup on the Second Cluster's Slave
At this point, you can restore the backup to the datadir
, as you normally would. For example:
$ mariabackup --copy-back \ --target-dir=/var/mariadb/backup/ \ --user backup_user --password backup_passwd
And adjusting file permissions, if necessary:
$ chown -R mysql:mysql /var/lib/mysql/
Bootstrap the Second Cluster's Slave
Now that the backup has been restored to the second cluster's slave, you can start the server by bootstrapping the node.
Create a Replication User on the First Cluster's Master
Before the second cluster's slave can begin replicating from the first cluster's master, you need to create a user account on the master that the slave can use to connect, and you need to grant the user account the REPLICATION SLAVE
privilege. For example:
CREATE USER 'repl'@'c2dbserver1' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'c2dbserver1';
Start Replication on the Second Cluster's Slave
At this point, you need to get the replication coordinates of the master from the original backup.
The coordinates will be in the xtrabackup_binlog_info
file.
Mariabackup dumps replication coordinates in two forms: GTID strings and binary log file and position coordinates, like the ones you would normally see from SHOW MASTER STATUS
output. In this case, it is probably better to use the GTID coordinates.
Regardless of the coordinates you use, you will have to set up the master connection using CHANGE MASTER TO
and then start the replication threads with START SLAVE
.
GTIDs
If you want to use GTIDs, then you will have to first set gtid_slave_pos
and we would set MASTER_USE_GTID=slave_pos
in the CHANGE MASTER TO
command. For example:
SET GLOBAL gtid_slave_pos = "0-1-2"; CHANGE MASTER TO MASTER_HOST="c1dbserver1", MASTER_PORT=3310, MASTER_USER="repl", MASTER_PASSWORD="password", MASTER_USE_GTID=slave_pos; START SLAVE;
File and Position
If you want to use the binary log file and position coordinates, then you would set master_log_file
and master_log_pos
in the CHANGE MASTER TO
command. For example:
CHANGE MASTER TO MASTER_HOST="c1dbserver1", MASTER_PORT=3310, MASTER_USER="repl", MASTER_PASSWORD="password", MASTER_LOG_FILE='mariadb-bin.000096', MASTER_LOG_POS=568, START SLAVE;
Check the Status of the Second Cluster's Slave
You should be done setting up the slave now, so you should check its status with SHOW SLAVE STATUS
. For example:
SHOW SLAVE STATUS\G
Start the Second Cluster
If the slave is replicating normally, then the next step would be to start the MariaDB Server process on the other nodes in the second cluster.
Circular Replication
You can also set up circular replication between the two clusters, so that the first cluster also replicates from the second cluster.
Create a Replication User on the Second Cluster's Master
Before circular replication can begin, you also need to create a user account on the second cluster's master that the first cluster's slave can use to connect, and you need to grant the user account the REPLICATION SLAVE
privilege. For example:
CREATE USER 'repl'@'c1dbserver1' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'c1dbserver1';
Start Circular Replication on the First Cluster
How this is done would depend on whether you want to use the GTID coordinates or the binary log file and position coordinates.
Regardless, you need to ensure that the second cluster is not accepting any writes other than those that it replicates from the first cluster at this stage.
GTIDs
To get the GTID coordinates on the second cluster, you can check gtid_current_pos
by executing:
SHOW GLOBAL VARIABLES LIKE 'gtid_current_pos';
Then on the first cluster, you can set up replication by setting gtid_slave_pos
to the GTID that was returned and then executing CHANGE MASTER TO
:
SET GLOBAL gtid_slave_pos = "0-1-2"; CHANGE MASTER TO MASTER_HOST="c2dbserver1", MASTER_PORT=3310, MASTER_USER="repl", MASTER_PASSWORD="password", MASTER_USE_GTID=slave_pos; START SLAVE;
File and Position
To get the binary log file and position coordinates on the second cluster, you can execute SHOW MASTER STATUS
:
SHOW MASTER STATUS
Then on the first cluster, you would set master_log_file
and master_log_pos
in the CHANGE MASTER TO
command. For example:
CHANGE MASTER TO MASTER_HOST="c2dbserver1", MASTER_PORT=3310, MASTER_USER="repl", MASTER_PASSWORD="password", MASTER_LOG_FILE='mariadb-bin.000096', MASTER_LOG_POS=568, START SLAVE;
Check the Status of the Circular Replication
You should be done setting up the circular replication on the node in the first cluster now, so you should check its status with SHOW SLAVE STATUS
. For example:
SHOW SLAVE STATUS\G