How to Setup Mariadb Clustering on Ubuntu 20.04
What Is MariaDB Galera Cluster?
Galera Cluster is a contemporary multi-master database cluster that is designed keeping in mind the contemporary replication, MySQL (or MariaDB), and InnoDB. It is used to direct read and write databases to any node. An individual node can be lost if the operation is uninterrupted and when the complex failover procedures are not being used.
How Does MariaDB Galera Cluster Work?
Galera Cluster is only available on Linux and supports the InnoDB storage engine. It consists of the database server that uses the Galera Replication Plugins to direct replication efficiently. MariaDB replication plugin API is extended to deliver all the information and secure necessary for true multi-master, synchronous replication. This extended API is known as Write-Set Replication API (or wsrep).
This API enables MariaDB Galera Cluster to deliver certification-based replication. It consists of database rows to replicate and the information related to the entire locks held by the database during the transaction. Then, in the applier queue, each node certifies the replicated write-set against another write-set. This write-set is applied to the conflicting locks that are not required. Now, the transaction is considered committed. Later, each of these nodes continues to apply it to the tablespace.
Features Of MariaDB Galera Cluster
- It is virtually synchronous replication.
- It can read and write to any cluster node.
- It consists of active multi-primary topology.
- It controls the membership automatically and drops the failed nodes from the cluster.
- It joins the node automatically.
- It connects to the client directly by providing the look and feel of native MariaDB.
- It delivers true parallel replication on row level.
- It provides better performance for all the workload.
- Users need not require VIP or master-slave operation if they use MariaDB.
- No downtime is available in terms of failure or intentionally taking down of nodes due to less availability of failover.
- Users can avoid database backup manually.
MariaDB Clustering Setup
In this guide, we will show you how to set up MariaDB Clustering on Ubuntu 20.04.
Installing MariaDB on All Nodes
Prior to MariaDB 10.1, the Galera cluster feature is bundled into MariaDB. So you will need to install the MariaDB server package on all three nodes. You can install it using the following command:
apt-get install mariadb-server -y
Once the MariaDB has been installed, start the MariaDB service on all nodes:
systemctl start mariadb
You can now check the status of the MariaDB with the following command:
systemctl status mariadb
You should see the following output:
● mariadb.service - MariaDB 10.3.31 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2021-10-02 03:37:11 UTC; 20s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 1803 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 31 (limit: 2353)
Memory: 66.0M
CGroup: /system.slice/mariadb.service
└─1803 /usr/sbin/mysqld
Next, you will need to secure the MariaDB installation and set a MariaDB root password on all nodes. You can do it by running the following script:
mysql_secure_installation
Answer all the questions as shown below:
Enter current password for root (enter for none):
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
Configuring Galera Cluster
Next, you will need to create a MariaDB Galera configuration file on each node. You can create a file in the /etc/mysql/conf.d directory.
Configuring the First Node
Login to the first node and create a MariaDB Galera configuration file:
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://45.58.47.29,45.58.35.200,45.58.44.157"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="45.58.47.29"
wsrep_node_name="mariadb01"
Save and close the file when you are finished.
Note: replaced each IP address as per your server’s IP.
Configuring the Second Node
Next, log in to the second node and create a MariaDB Galera configuration file:
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://45.58.47.29,45.58.35.200,45.58.44.157"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="45.58.35.200"
wsrep_node_name="mariadb02"
Save and close the file when you are finished.
Configuring the Third Node
Next, log in to the third node and create a MariaDB Galera configuration file:
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://45.58.47.29,45.58.35.200,45.58.44.157"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="45.58.44.157"
wsrep_node_name="mariadb03"
Save and close the file when you are finished.
Starting the Galera Cluster
At this point, all nodes are configured. You are now ready to bring up the cluster.
Stop MariaDB on All Nodes
First, run the following command on all nodes to stop the MariaDB service:
systemctl stop mariadb
Next, make sure MariaDB service is stopped:
systemctl status mariadb
Sample output:
● mariadb.service - MariaDB 10.3.31 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Sat 2021-10-02 03:41:05 UTC; 22s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 1803 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 1803 (code=exited, status=0/SUCCESS)
Status: "MariaDB server is down"
Start the First Node
Now, go to the first node and use the galera_new_cluster script to start the first node.
galera_new_cluster
You should not get any output if the script is executed successfully.
Next, verify the cluster status using the following command:
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
You should get the following output:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
Start the Second Node
Now, go to the second node and bring up the MariaDB service with the following command:
systemctl start mariadb
Next, verify the cluster status using the following command:
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
The following output indicates that the second node has joined the cluster :
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
Start the Third Node
Finally, bring up the third node by running the following command:
systemctl start mariadb
Next, run the following command to check the cluster status:
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
You should get the following output:
+--------------------+-------+
| Variable_name | Value
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
Verify Replication
At this point, your MariaDB cluster is up and running. Now, you will need to test whether the replication is working or not.
Create a Database and Table on the First Node
First, go to the first node and connect to the MariaDB using the following command:
mysql -u root -p
Once you are connected, create a database named classdb:
MariaDB [(none)]> CREATE DATABASE classdb;
Next, switch the database to classdb and create a table named students:
MariaDB [(none)]> USE classdb;
MariaDB [classdb]> CREATE TABLE students (id int, name varchar(20), surname varchar(20));
Next, insert some data into students table:
MariaDB [classdb]> INSERT INTO students VALUES (1,"vyom","patel");
MariaDB [classdb]> INSERT INTO students VALUES (2,"raj","shah");
Now, verify the inserted data with the following command:
MariaDB [schooldb]> SELECT * FROM students;
You should get the following output:
+------+------+---------+
| id | name | surname |
+------+------+---------+
| 1 | vyom | patel |
| 2 | raj | shah |
+------+------+---------+
Verify Replication on the Second and Third Node
Now, it’s time to verify whether the classdb database is replicated or not.
First, go to the second node and connect to the MariaDB with the following command:
mysql -u root -p
Once you are connected, verify all the databases:
MariaDB [(none)]> SHOW DATABASES;
You should see that your classdb database is replicated to the second node:
+--------------------+
| Database |
+--------------------+
| classdb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
Now, switch the database to classdb and verify your students table:
MariaDB [(none)]> USE classdb;
MariaDB [classdb]> SELECT * FROM students;
You should see the following output:
+------+------+---------+
| id | name | surname |
+------+------+---------+
| 1 | vyom | patel |
| 2 | raj | shah |
+------+------+---------+
Now, add some information in the third row:
MariaDB [classdb]> INSERT INTO students VALUES (3,"nisha","sharma");
Next, go to the third node and connect to the MariaDB with the following command:
mysql -u root -p
Next, switch the database to classdb database and verify your tables:
MariaDB [(none)]> USE classdb;
MariaDB [classdb]> SHOW TABLES;
You should see the following output:
+-------------------+
| Tables_in_classdb |
+-------------------+
| students |
+-------------------+
Now, verify your table records using the following command:
MariaDB [classdb]> SELECT * FROM students;
You should get the following output:
+------+-------+---------+
| id | name | surname |
+------+-------+---------+
| 1 | vyom | patel |
| 2 | raj | shah |
| 3 | nisha | sharma |
+------+-------+---------+
Conclusion
In the above guide, we explained how to set up three nodes MariaDB cluster on Ubuntu 20.04. Now, data modifications on any node are replicated to all other nodes. You can now add more MariaDB nodes to increase the size of the cluster.
