(Last Updated On: June 23, 2018)

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.

How to 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.

# cat /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-03

You 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.3 on Ubuntu 18.04 and CentOS 7 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.cnf

Uncomment these lines:

character-set-server = utf8
character_set_server = utf8

Then add Galera specific configurations:

# vim /etc/mysql/mariadb.conf.d/galera.cnf

Add 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]:~# cat /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]:~# cat /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:

# systemctl stop mariadb

Now start new Galera cluster on  node 1 – galera-node-01:

# galera_new_cluster

Check 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:

 # systemctl start mariadb

Check 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:

# systemctl start mariadb

Check 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 18.04 / CentOS 7.

That’s all for today. Please subscribe to get latest updates directly on your email.

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