can be used to replicate between MariaDB Galera Cluster and MariaDB Server. This article will discuss how to do that.
Before we set up replication, we need to ensure that the cluster is configured properly. This involves the following steps:
Set on all nodes in the cluster. 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. It is also needed to .
Set to the same value on all nodes in the cluster. See for more information on what this means.
If you want to use replication, then you also need to configure some things to . For example:
needs to be set on all nodes in the cluster.
needs to be set to the same value on all nodes in the cluster so that each cluster node uses the same domain when assigning for Galera Cluster's write sets.
needs to be enabled on all nodes in the cluster. See about that.
needs to be set to the same path on all nodes in the cluster. See
And as an extra safety measure:
should be set to a different value on all nodes in a given cluster, and each of these values should be different than the configured value. This is to prevent a node from using the same domain used for Galera Cluster's write sets when assigning for non-Galera transactions, such as DDL executed with set or DML executed with set.
Before we set up replication, we also need to ensure that the MariaDB Server replica is configured properly. This involves the following steps:
Set to a different value than the one that the cluster nodes are using.
Set to a value that is different than the and values that the cluster nodes are using.
Set and if you want the replica to log the transactions that it replicates.
Our process to set up replication is going to be similar to the process described at , but it will be modified a bit to work in this context.
The very first step is to start the nodes in the first cluster. The first node will have to be . 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 primary for the MariaDB Server.
Now that the backup has been restored to the MariaDB Server replica, you can start the MariaDB Server process.
You can also set up between the cluster and MariaDB Server, which means that the MariaDB Server replicates from the cluster, and the cluster also replicates from the MariaDB Server.
This page is licensed: CC BY-SA / Gnu FDL
At this point, you need to get the replication coordinates of the primary from the original backup.
The coordinates will be in the file.
mariadb-backup dumps replication coordinates in two forms: and file and position coordinates, like the ones you would normally see from output. In this case, it is probably better to use the coordinates.
For example:
Regardless of the coordinates you use, you will have to set up the primary connection using and then start the replication threads with .
If you want to use GTIDs, then you will have to first set to the coordinates that we pulled from the file, and we would set MASTER_USE_GTID=slave_pos in the command. For example:
If you want to use the file and position coordinates, then you would set MASTER_LOG_FILE and MASTER_LOG_POS in the command to the file and position coordinates that we pulled from the file. For example:
How this is done would depend on whether you want to use the coordinates or the 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 cluster at this stage.
To get the GTID coordinates on the MariaDB server, you can check by executing:
Then on the node acting as a replica in the cluster, you can set up replication by setting to the GTID that was returned and then executing :
To get the file and position coordinates on the MariaDB server, you can execute :
Then on the node acting as a replica in the cluster, you would set master_log_file and master_log_pos in the command. For example:
$ rsync -avrP /var/mariadb/backup dc2-dbserver1:/var/mariadb/backup$ mariadb-backup --copy-back \
--target-dir=/var/mariadb/backup/$ chown -R mysql:mysql /var/lib/mysql/mariadb-bin.000096 568 0-1-2SHOW SLAVE STATUS\GSHOW GLOBAL VARIABLES LIKE 'gtid_current_pos';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;SHOW SLAVE STATUS\G$ mariadb-backup --backup \
--target-dir=/var/mariadb/backup/ \
--user=mariadb-backup --password=mypassword$ mariadb-backup --prepare \
--target-dir=/var/mariadb/backup/CREATE USER 'repl'@'dc2-dbserver1' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'dc2-dbserver1';CREATE USER 'repl'@'c1dbserver1' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'c1dbserver1';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;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;SHOW MASTER STATUSCHANGE 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;