MariaDB cluster in hybrid cloud

I have been looking for a more robust distributed relational database where one instance is in an on-premises data center and the other is in AWS. Initially, the approach I tried was using MySQL replication between an on-premises server and an AWS RDS instance. While this works, there are limitations, like difficulty in supporting more than two nodes and the overall fragility of a database acting as both master and slave. In my case, the two instances are a long distance apart, so the solution had to be robust enough to deal with the replication latency. The solution also had to be open-source and run on Linux.

I narrowed down my choices to MariaDB Galera Cluster and MySQL NDB Cluster. Unfortunately neither one (cluster) is supported by AWS RDS, so I had to abandon it and run the AWS database in an EC2 instance. The NDB Cluster has its own database engine, has been around longer and has telecom background which is attractive from perspective of robustness. MariaDB cluster is based on the Galera implementation. It is a replication layer on top of the standard InnoDB engine and comes as a part of MariaDB server, so there is nothing extra to install. My application relies on some InnoDB features which are not supported by the NDB engine. This aspect convinced me to use MariaDB cluster.

Here is a quick overview of the steps involved in the setup:

• Set up the on-premises and AWS servers
• Install MariaDB in both
• Configure cluster
• Test cluster functionality and performance

I installed MariaDB version 10.2. It is tempting to use the latest version, but if you want to do database encryption and leverage the AWS KMS plugin, this is the version to get — later versions do not seem to have the plugin at this moment.

I used Ubuntu Server 18.04 as the base OS on both servers. My on-premises server is hosted by VMware. I downloaded the ISO file from the official Ubuntu download host and installed it. The AWS server is a standard Ubuntu 18.04 AMI.

Let’s assume that the on-premises server “node-1” IP address is 10.0.0.10 and the AWS server “node-2” IP address is 172.0.0.10.

Install MariaDB

Don’t forget that version 10.2 is needed in order to use the AWS KMS Encryption Plugin if you plan on using database encryption and want to store encryption keys in KMS. The installation will prompt you to set the database root user password. Don’t forget to set it and save it. If you don’t set it, you’ll have to run the mysql_secure_installation script later.

Here I am doing the installation on node 1. Repeat the same steps on node 2.

ubuntu@node-1:~$ sudo apt-get install software-properties-common
ubuntu@node-1:~$ sudo apt-key adv — recv-keys — keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
ubuntu@node-1:~$ sudo add-apt-repository ‘deb [arch=amd64,arm64,ppc64el] http://mirror.jaleco.com/mariadb/repo/10.2/ubuntu bionic main’
ubuntu@node-1:~$ sudo apt update
ubuntu@node-1:~$ sudo apt install mariadb-server

Check the installation.

ubuntu@node-1:~$ sudo mysql -u root –p

Enter root password. You should see something like this:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1067
Server version: 10.2.21-MariaDB-1:10.2.21+maria~bionic mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

Optionally increase maximum column size. Via mysql command line:

set global innodb_large_prefix=on;
set global innodb_file_format=Barracuda;
set global innodb_default_row_format=DYNAMIC;

Or add the following to the /etc/mysql/my.cnf configuration file:

[mysql]
innodb_large_prefix=on;
innodb_file_format=Barracuda;
innodb_default_row_format=DYNAMIC;

Check network configuration on both nodes. The following ports must be open and reachable:

• TCP 3306 — MySQL clients
• TCP&UDP 4567 — replication traffic
• TCP 4568 — ICT (Incremental State Transfer)
• TCP 4444 — SST (State Snapshot Transfer)

On AWS side verify the security group associated with the EC2 and VPC settings. Also check any internal firewalls that may be setup between the on-premises server and the VPC. Test connectivity between the nodes to confirm that they are able to communicate.

Node 1 configuration

1. Stop MariaDB

ubuntu@node-1:~$ sudo systemctl stop mariadb

2. Edit /etc/mysql/my.cnf configuration file and add the following:

# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=”gcomm://10.0.0.10,172.0.0.10"
wsrep_cluster_name=”mariadb-galera-cluster”
wsrep_sst_method=rsync
# Cluster node configuration
wsrep_node_address=”10.0.0.10"
wsrep_node_name=”node-1"

3. Start cluster

ubuntu@node-1:~$ sudo galera_new_cluster

Here, we are not simply starting the MariaDB server, but are initializing the cluster. This is only done for the first node in the cluster. All of the subsequent nodes are started normally.

4. Check status

ubuntu@node-1:~$ sudo mysql ‐u root ‐p ‐e “show status like ‘wsrep_cluster_size’”

Output should look something like this:

+ — — — — — — — — — — + — — — -+
| Variable_name      | Value   |
+ — — — — — — — — — — + — — — -+
| wsrep_cluster_size | 1       |
+ — — — — — — — — — — + — — — -+

Initial value is 1 since this is the first node in the cluster. When other nodes are started, this value will be incremented accordingly.

Node 2 configuration

1. Stop MariaDB

ubuntu@node-2:~$ sudo systemctl stop mariadb

2. Edit /etc/mysql/my.cnf configuration file and add the following:

# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=”gcomm://10.0.0.10,172.0.0.10"
wsrep_cluster_name=”mariadb-galera-cluster”
wsrep_sst_method=rsync
# Cluster node configuration
wsrep_node_address=”172.0.0.10"
wsrep_node_name=”node-2"

3. Start MariaDB

ubuntu@node-2:~$ sudo systemctl start mariadb

Here, we are just starting MariaDB normally. The server will join the cluster based on configuration settings.

4. Check status

ubuntu@node-2:~$ sudo mysql ‐u root ‐p ‐e “show status like ‘wsrep_cluster_size’”

You should see the following output:

+ — — — — — — — — — — + — — — -+
| Variable_name      | Value   |
+ — — — — — — — — — — + — — — -+
| wsrep_cluster_size | 2       |
+ — — — — — — — — — — + — — — -+

The value should change to 2. Every time a new node is added to the cluster, the value should reflect the total number of nodes.

Test cluster replication

Login to the on-premises server, add a database, table, and some data.

Verify that the changes propagate to the AWS server.

In my case, the latency is not noticeable. With large amounts of data, the delay may cause issues. MariaDB cluster documentation recommends running an odd number of nodes to break transaction deadlocks. The idea is that there is always a “majority” to resolve the deadlock. In practice, this may cause a rollback error which manifests itself as OperationalError: (1213, ‘Deadlock found when trying to get lock; try restarting transaction’) in the database transaction log. According to Galera documentation the general approach to solving these problems is by implementing application layer retry logic: WORKING AROUND MULTI-MASTER CONFLICTS.

References

What is MariaDB Galera Cluster

Getting Started with MariaDB Galera Cluster

Unsupported or Missing Features in NDB Cluster