The Ultimate Guide to High Availability with MariaDB

Configuring MariaDB Replication between MariaDB Galera Cluster and MariaDB Server

Contents

  1. Configuring the Cluster
    1. Configuring Wsrep GTID Mode
  2. Configuring the Replica
  3. Setting up Replication
    1. Start the Cluster
    2. Backup the Database on the Cluster's Primary Node and Prepare It
    3. Copy the Backup to the Replica
    4. Restore the Backup on the Second Cluster's Replica
    5. Start the New Replica
    6. Create a Replication User on the Cluster's Primary
    7. Start Replication on the New Replica
      1. GTIDs
      2. File and Position
    8. Check the Status of the New Replica
  4. Setting up Circular Replication
    1. Create a Replication User on the MariaDB Server Primary
    2. Start Circular Replication on the Cluster
      1. GTIDs
      2. File and Position
    3. Check the Status of the Circular Replication

MariaDB replication can be used to replicate between MariaDB Galera Cluster and MariaDB Server. This article will discuss how to do that.

Configuring the Cluster

Before we set up replication, we need to ensure that the cluster is configured properly. This involves the following steps:

Configuring Wsrep GTID Mode

If you want to use GTID replication, then you also need to configure some things to enable wsrep GTID mode. For example:

  • wsrep_gtid_domain_id 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 GTIDs for Galera Cluster's write sets.
  • log_bin needs to be set to the same path on all nodes in the cluster. See MDEV-9856 about that.

And as an extra safety measure:

  • gtid_domain_id 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 wsrep_gtid_domain_id value. This is to prevent a node from using the same domain used for Galera Cluster's write sets when assigning GTIDs for non-Galera transactions, such as DDL executed with wsrep_sst_method=RSU set or DML executed with wsrep_on=OFF set.

Configuring the Replica

Before we set up replication, we also need to ensure that the MariaDB Server replica is configured properly. This involves the following steps:

  • Set server_id to a different value than the one that the cluster nodes are using.

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 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 primary for the MariaDB Server.

Backup the Database on the Cluster's Primary 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 primary. For example:

$ mariabackup --backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariabackup --password=mypassword

And then you would prepare the backup as you normally would. For example:

$ mariabackup --prepare \
   --target-dir=/var/mariadb/backup/

Copy the Backup to the Replica

Once the backup is done and prepared, you can copy it to the MariaDB Server that will be acting as replica. For example:

$ rsync -avrP /var/mariadb/backup dc2-dbserver1:/var/mariadb/backup

Restore the Backup on the Second Cluster's Replica

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/

And adjusting file permissions, if necessary:

$ chown -R mysql:mysql /var/lib/mysql/

Start the New Replica

Now that the backup has been restored to the MariaDB Server replica, you can start the MariaDB Server process.

Create a Replication User on the Cluster's Primary

Before the MariaDB Server replica can begin replicating from the cluster's primary, you need to create a user account on the primary that the replica can use to connect, and you need to grant the user account the REPLICATION SLAVE privilege. For example:

CREATE USER 'repl'@'dc2-dbserver1' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'dc2-dbserver1';

Start Replication on the New Replica

At this point, you need to get the replication coordinates of the primary 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.

For example:

mariadb-bin.000096 568 0-1-2

Regardless of the coordinates you use, you will have to set up the primary 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 to the GTID coordinates that we pulled from the xtrabackup_binlog_info file, 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 to the file and position coordinates that we pulled the xtrabackup_binlog_info file. 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 New Replica

You should be done setting up the replica now, so you should check its status with SHOW SLAVE STATUS. For example:

SHOW SLAVE STATUS\G

Now that the MariaDB Server is up, ensure that it does not start accepting writes yet if you want to set up circular replication between the cluster and the MariaDB Server.

Setting up Circular Replication

You can also set up circular replication 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.

Create a Replication User on the MariaDB Server Primary

Before circular replication can begin, you also need to create a user account on the MariaDB Server, since it will be acting as replication primary to the cluster's replica, 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 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 cluster at this stage.

GTIDs

To get the GTID coordinates on the MariaDB Server you can check gtid_current_pos by executing:

SHOW GLOBAL VARIABLES LIKE 'gtid_current_pos';

Then on the node acting as replica in the 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 MariaDB Server, you can execute SHOW MASTER STATUS:

SHOW MASTER STATUS

Then on the node acting as replica in the 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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.