MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB with support for XtraDB/InnoDB storage engines. It has the following top features.
- It provides active-active multi-master topology
- You can read and write to any cluster node
- It has an automatic node joining
- Automatic membership control, failed nodes drop from the cluster
- Has true parallel replication, on row level
- Direct client connections
sudo apt update && sudo apt -y upgrade sudo reboot
Setup Hostnames
Here’re the IP plans of my servers-
Configure static hostnames on each of the three servers for DNS reachability.
$ sudo vim /etc/hosts 172.20.5.200 node1.tweenpath.net node1 172.20.5.201 node2.tweenpath.net node2 172.20.5.202 node3.tweenpath.net node3
Install MariaDB on all nodes
Install the latest version of MariaDB with the commands below:
sudo apt update sudo apt -y install mariadb-server mariadb-client
Start MariaDB
Start MariaDB services on all 3 nodes-
systemctl start mariadb
Check if the service is running by-
systemctl status mariadb
Securing mariadb
Configure MariaDB for first use by running the command below, then run through the configuration appropriately.
$ sudo mysql_secure_installation
In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!
Test connection to each of the databases by running the command below:
$ mysql -u root -p
You will be prompted to input a password that you had setup in the previous step.
Configure Galera Cluster
The next step is to configure galera cluster on our three MariaDB hosts. Comment the bind line on the file /etc/mysql/mariadb.conf.d/50-server.cnf which binds MariaDB service to 127.0.0.1
$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf #bind-address = 127.0.0.1
Step Configure First Node
Add the following content to the MariaDB configuration file. Remember to modify the hostname at “wsrep_node_address” to the hostname or IP of your first host. Place the below configurable texts right before [options] sections-
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="vmdbcl2" wsrep_cluster_address="gcomm://172.20.5.200,172.20.5.201,172.20.5.202" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="172.20.5.200" wsrep_node_name="node1"
Configure Galera nodes (node2 & node3)
Add the following configuration for node2 and node3 respectively:
On Node2:
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="vmdbcl2" wsrep_cluster_address="gcomm://172.20.5.200,172.20.5.201,172.20.5.202" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="172.20.5.201" wsrep_node_name="node2"
and on Node3
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="vmdbcl2" wsrep_cluster_address="gcomm://172.20.5.200,172.20.5.201,172.20.5.202" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="172.20.5.202" wsrep_node_name="node3"
Now stop mariadb service on all the 3 nodes-
systemctl stop mariadb
Initialize galera cluster and restart MariaDB on Node1 ONLY
sudo galera_new_cluster sudo systemctl restart mariadb
Start mariadb service on node2 and node3-
systemctl start mariadb
Validate Galera Settings
Login to any of the three nodes as the root user, then confirm that the cluster settings are OK.
$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 35 Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal 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)]> Check status MariaDB [(none)]> show status like 'wsrep_%'; +-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+ | wsrep_local_state_uuid | c2e29a95-a81f-11eb-a1fc-237e9071c44f | | wsrep_protocol_version | 10 | | wsrep_last_committed | 7 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 7 | | wsrep_received_bytes | 690 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_send_queue_avg | 0 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_max | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.142857 | | wsrep_local_cached_downto | 1 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_active | false | | wsrep_flow_control_requested | false | | wsrep_cert_deps_distance | 0 | | wsrep_apply_oooe | 0 | | wsrep_apply_oool | 0 | | wsrep_apply_window | 0 | | wsrep_commit_oooe | 0 | | wsrep_commit_oool | 0 | | wsrep_commit_window | 0 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 0 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0 | | wsrep_open_transactions | 0 | | wsrep_open_connections | 0 | | wsrep_incoming_addresses | AUTO,AUTO,AUTO | | wsrep_cluster_weight | 3 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0/0/0/0/0 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | e4af838d-a824-11eb-95d0-72f816b84c68 | | wsrep_gmcast_segment | 0 | | wsrep_applier_thread_count | 1 | | wsrep_cluster_capabilities | | | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | c2e29a95-a81f-11eb-a1fc-237e9071c44f | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 2 | | wsrep_provider_capabilities | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 4.7(ree4f10fc) | | wsrep_ready | ON | | wsrep_rollbacker_thread_count | 1 | | wsrep_thread_count | 2 | +-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+ 68 rows in set (0.002 sec) MariaDB [(none)]>
Confirm that we have a cluster size of 3 under:
wsrep_cluster_size 3
Verify Replication
We can create a test database on any of the nodes and check its availability on the other nodes.
root@node1:~# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 49 Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal 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)]> create database test1; Query OK, 1 row affected (0.003 sec)
On node2 and node3
root@node2:~# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 45 Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal 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)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test1 | +--------------------+ 4 rows in set (0.001 sec)
root@node3:~# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 46 Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal 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)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test1 | +--------------------+ 4 rows in set (0.001 sec)
This confirms that the database created on node1 is replicated across the cluster.
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 | +--------------------+-------+
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 | +------+------+---------+
After this you may need to set up a TCP4 load balancer like haproxy to load balance between these 3 nodes. The load balancer for this configuration is available in this configurtion part-
https://tweenpath.net/setup-haproxy-load-balancer-mariadb-galera-cluster/
Src:
https://computingforgeeks.com/install-mariadb-galera-cluster-on-ubuntu-with-proxysql/
https://cloudinfrastructureservices.co.uk/how-to-setup-mariadb-clustering-on-ubuntu-20-04/
