In this blog post, we’re going to explore How to Setup MariaDB Galera Cluster on Ubuntu 18.04 with HAProxy as Load Balancer. If you have been working with MariaDB – In place replacement of MySQL, you should definitely be aware of MariaDB Galera Cluster. If you are new to these terms, MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB with support for XtraDB/InnoDB storage engines.
Top features of MariaDB Galera Cluster are:
- It provides active-active multi-master topology
- You can read and write to any cluster node
- It has automatic node joining
- Automatic membership control, failed nodes drop from the cluster
- Has true parallel replication, on row level
- Direct client connections
From these features, you get the benefits like no loss in transactions or weird slave lag normally seen in database replication since all servers have up-to-date data. By using MariaDB Galera Cluster on Ubuntu 18.04 server, you also get scalability for both reads and writes with small latencies from connecting clients.
To get fair load balancing based on Roundrobin or least number of connections on the servers, we will make use of HAProxy which is a production-grade open source Load Balancer.
Setup MariaDB Galera Cluster on Ubuntu 18.04
When setting up MariaDB Galera Cluster, It is advisable to have an odd number of servers. Minimum being three servers. My Lab environment will be based on the diagram below:
I prefer using hostnames as opposed to hard-coded IP addresses in all my configurations. In preparation for this, let’s populate /etc/hosts with correct short hostnames and IP addresses for all servers.
$ sudo vim /etc/hosts
10.131.69.129 galera-haproxy-01
10.131.74.92 galera-db-01
10.131.35.167 galera-db-02
10.131.65.13 galera-db-03You must have noticed that my servers are not on the same subnet. This is recommended to ensure there is no single point of failure on the networking side. You can test that everything is working using ping.
Step 1: Install MariaDB database server on Galera nodes
We will start by Installing MariaDB on all Galera cluster nodes:
- galera-db-01
- galera-db-02
- galera-db-03
Use our previous guide Install MariaDB 10.x on Ubuntu to install MariaDB on Ubuntu 18.04. Once you are done with the installation of MariaDB database server on all nodes, proceed to next step.
Step 2: Configure First Galera Cluster node
When setting up Galera Cluster, you need to start with one node which will assume the role of master. Edit first node main configuration to configure default character set.
$ sudo vim /etc/mysql/mariadb.cnfUncomment these lines:
character-set-server = utf8
character_set_server = utf8Then add Galera specific configurations:
sudo vim /etc/mysql/mariadb.conf.d/galera.cnfAdd below on galera-db-01 Galera configuration file:
[mysqld]
bind-address=0.0.0.0
default_storage_engine=InnoDB
binlog_format=row
innodb_autoinc_lock_mode=2
# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.131.74.92,10.131.35.167,10.131.65.13"
wsrep_cluster_name="mariadb-galera-cluster"
wsrep_sst_method=rsync
# Cluster node configuration
wsrep_node_address="10.131.74.92"
wsrep_node_name="galera-db-01"Rember to replace 10.131.74.92,10.131.35.167,10.131.65.13 with Galera cluster nodes IP Addresses.
Step 3: Configure other Galera Cluster Nodes
We now need to add configurations to Galera cluster node 2 and node 3.
For galera-db-02:
[email protected]:~# vim /etc/mysql/mariadb.conf.d/galera.cnf
[mysqld]
bind-address=0.0.0.0
default_storage_engine=InnoDB
binlog_format=row
innodb_autoinc_lock_mode=2
# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.131.74.92,10.131.35.167,10.131.65.13"
wsrep_cluster_name="mariadb-galera-cluster"
wsrep_sst_method=rsync
# Cluster node configuration
wsrep_node_address="10.131.35.167"
wsrep_node_name="galera-db-02"For galera-db-03:
[email protected]:~# vim /etc/mysql/mariadb.conf.d/galera.cnf
[mysqld]
bind-address=0.0.0.0
default_storage_engine=InnoDB
binlog_format=row
innodb_autoinc_lock_mode=2
# Galera cluster configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.131.74.92,10.131.35.167,10.131.65.13"
wsrep_cluster_name="mariadb-galera-cluster"
wsrep_sst_method=rsync
# Cluster node configuration
wsrep_node_address="10.131.65.13"
wsrep_node_name="galera-db-03"You must have noted that what changes on each node is Cluster node configuration
Step 4: Start Galera Cluster on Ubuntu 18.04
Stop mariadb on all three nodes:
sudo systemctl stop mariadbNow start new Galera cluster on node 1 – galera-node-01:
sudo galera_new_clusterCheck Galera status if it’s running:
$ mysql -u root -p -e "show status like 'wsrep_%'"
Enter password:
+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_cert_index_size | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 9f957c6d-76b4-11e8-a71a-17cc0eca13f1 |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_connected | ON |
| 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_flow_control_paused | 0.000000 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_sent | 0 |
| wsrep_gcomm_uuid | 9f945140-76b4-11e8-84c6-a66f9e2978f6 |
| wsrep_incoming_addresses | 10.131.74.92:3306 |
| wsrep_last_committed | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 18446744073709551615 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 0 |
| wsrep_local_index | 0 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_local_state_uuid | 9f957c6d-76b4-11e8-a71a-17cc0eca13f1 |
| wsrep_protocol_version | 8 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <[email protected]> |
| wsrep_provider_version | 25.3.23(r3789) |
| wsrep_ready | ON |
| wsrep_received | 2 |
| wsrep_received_bytes | 148 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_thread_count | 2 |
+------------------------------+--------------------------------------+Initial cluster size should be 1
[email protected]:~# mysql -u root -p -e "show status like 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+On galera-node-02, start mariadb service:
sudo systemctl start mariadbCheck cluster size again, it should have changed to 2
# mysql -u root -p -e "show status like 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+Start mariadb on galera-db-03:
sudo systemctl start mariadbCheck cluster size again, it should be 3
# mysql -u root -p -e "show status like 'wsrep_cluster_size'"
Enter password:
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+If everything was set correctly, you can start testing.
Step 5: Test Galera Cluster Operation
To test our Galera cluster, we’re going to create a test database on one server and confirm that it has been replicated on other nodes. Log in to any member of Galera cluster as root user:
[email protected]:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.3.7-MariaDB-1:10.3.7+maria~bionic-log 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 galera_test;
Query OK, 1 row affected (0.004 sec)Log in to the other node and check if the database exists.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| galera_test |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)Step 6: Install and Configure HAProxy
For installation and Configuration of HAProxy as Load Balancer for MariaDB Galera Cluster, refer to our guide: Galera Cluster High Availability With HAProxy on Ubuntu / CentOS 7.
Install Desktop Database Management Tool
If working with MySQL command line is not your thing, then consider installing a Database Tool to help you. Check out our guide below:
Install and Configure DBeaver on Ubuntu / Debian
That’s all for today. Please subscribe to get the latest updates directly on your email.
Learning courses:
Search Tags:
- How to Install MariaDB Galera Cluster on Ubuntu 18.04
- MariaDB Galera Cluster on Ubuntu 18.04
- MariaDB Galera Cluster on Ubuntu
- Galera Cluster High Availability on Ubuntu 18.04
- MariaDB Setup on Ubuntu 18.04 with Galera Cluster
Whats the point of configuring the hostnames in /etc/hosts if you have to configure MariaDB with IPs?
For cluster operations not necessary when IPs are used. But if you choose to use names it is a requirement.