Database clustering is the process of combining multiple servers by connecting them to a single database. Clustering improves your database’s availability by distributing load to different servers. If any server fails, others are quickly available to continue serving.
MariaDB Galera is a multi-master clustering solution that allows you to read and write to any node in the cluster. With MariaDB Galera, a change made to any one node is replicated to all nodes. MariaDB Galera supports the XtraDB/InnoDB storage engines and is available on Linux only.
Features
- Active-active multi-master topology
- Automatic node joining
- Ability read and write to any cluster node
- Automatic membership control, failed nodes drop from the cluster
- Automatic node provisioning
In this tutorial, we will show you how to set up a three-node MariaDB Galera cluster on Ubuntu 18.04.
Prerequisites
- Three fresh Ubuntu 18.04 VPSes on the Atlantic.Net Cloud Platform.
- A root password configured on all servers.
For the purpose of this tutorial, we will use three servers with the following configuration:
server1 : 192.168.0.101
server2 : 192.168.0.102
server3 : 192.168.0.103
Step 1 – Install MariaDB Server on All Servers
By default, the latest version of the MariaDB Server (Version 10.4) is not available in the Ubuntu 18.04 default repository, so you will need to add the official MariaDB repository to your server.
First, add the MariaDB repository key with the following command:
apt-get install software-properties-common apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
Next, add the repository using the following command:
add-apt-repository "deb [arch=amd64,arm64,ppc64el] http://mariadb.mirror.liquidtelecom.com/repo/10.4/ubuntu $(lsb_release -cs) main"
Once the repository has been added, update the repository with the following command:
apt-get update -y
Finally, install the MariaDB server with the following command:
apt-get install mariadb-server -y
Beginning with version 10.1, the Galera cluster feature is bundled into MariaDB. The MariaDB Galera Server package is automatically installed with the MariaDB server.
By default, the MariaDB root user does not have a password, so you will need to set a password for MariaDB root user.
You can set up it with the following command:
mysql_secure_installation
Answer all the questions, as shown below:
Enter current password for root (enter for none): Provide your root user password Switch to unix_socket authentication [Y/n] n Change the root password? [Y/n] Y New password: Re-enter new password: Remove anonymous users? [Y/n] Y Disallow root login remotely? [Y/n] Y Remove test database and access to it? [Y/n] Y Reload privilege tables now? [Y/n] Y
Note: Please repeat the above steps on all three servers.
Step 2 – Configure Each Server in the Cluster
At this point, we have installed and configured the MariaDB server on each server. Next, you will need to configure the Galera cluster to communicate between the servers. To do so, you will need to create a common configuration file on each server.
In this section, we will show you how to configure each server in the cluster.
Configure First Server
First, log in to the first server and create a Galera configuration file with the following command:
nano /etc/mysql/conf.d/galera.cnf
Add the following lines (make sure you input your own server IP addresses):
[mysqld] binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="galera_cluster" wsrep_cluster_address="gcomm://192.168.0.101,192.168.0.102,192.168.0.103" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="192.168.0.101" wsrep_node_name="server1"
Save and close the file when you are finished. Next, you can proceed to the second server.
Configure Second Server
Next, log in to the second server and create a Galera configuration file with the following command:
nano /etc/mysql/conf.d/galera.cnf
Add the following lines:
[mysqld] binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="galera_cluster" wsrep_cluster_address="gcomm://192.168.0.101,192.168.0.102,192.168.0.103" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="192.168.0.102" wsrep_node_name="server2"
Save and close the file when you are finished. Next, you can proceed to the third server.
Configure Third Server
Next, log in to the third server and create a Galera configuration file with the following command:
nano /etc/mysql/conf.d/galera.cnf
Add the following lines:
[mysqld] binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="galera_cluster" wsrep_cluster_address="gcomm://192.168.0.101,192.168.0.102,192.168.0.103" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="192.168.0.103" wsrep_node_name="server3"
Save and close the file when you are finished.
At this point, we have configured all three servers to communicate with each other.
Step 3 – Initialize the Galera Cluster
Before starting the cluster, you will need to stop the MariaDB service on all servers.
Run the following command to stop the MariaDB service on all servers.
systemctl stop mariadb
Next, initialize the cluster in the first node with the following command:
galera_new_cluster
The above command will start the cluster and add server1 to the cluster.
You can check it with the following command:
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'" Enter password:
Provide your root password and hit Enter. You should see the following output:
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 1 | +--------------------+-------+
Next, go to the second server and start the MariaDB service:
systemctl start mariadb
Next, verify your cluster size with the following command:
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'" Enter password:
Provide your root password and hit Enter. You should see that the second server has joined the cluster.
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 2 | +--------------------+-------+
Next, go to the third server and start the MariaDB service:
systemctl start mariadb
Next, verify your cluster size with the following command:
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'" Enter password:
Provide your root password and hit Enter. You should see that the third server has joined the cluster.
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+
Step 4 – Test Galera Cluster Replication
Your Galera cluster is now up and running. It’s time to test and see whether replication is working.
To do so, create a database on the first server and check whether it has been replicated to other servers.
On server1, log in to MySQL shell with the following command:
mysql -u root -p
Provide your root password when prompted, then create a database with the following command:
create database replicadb; create database mydb;
Next, exit from the MySQL shell with the following command:
exit;
On server2, log in to the MySQL shell with the following command:
mysql -u root -p
Provide your root password when prompted and check if the database exists.
show databases;
You should get the following output:
+--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | replicadb | +--------------------+
On server3, log in to the MySQL shell with the following command:
mysql -u root -p
Provide your root password when prompted and check if the database exists.
show databases;
You should get the following output:
+--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | replicadb | +--------------------+
The above output clearly indicates that replication is working properly.
Conclusion
Congratulations! You have successfully installed and configured a three-node MariaDB Galera cluster on Ubuntu 18.04 VPS. You should now enough knowledge to set up a MariaDB Galera cluster in a production environment. You can visit the MariaDB Galera Documentation for more information.
