How-to: Setup a MariaDB Cluster with Galera and HAProxy

If you’re looking to scale MariaDB or are interested in making sure it is highly available, one of the options you have is to set up Galera Cluster with HAProxy.

This how-to will guide you through the process of creating a three-node configuration with Galera Cluster 4, MariaDB 10.4 and HAProxy running on Ubuntu 18.04 (Bionic Beaver). Our goal in writing this is to offer a complete guide from beginning to end to cover all of the necessary steps. We have found a number of guides for individual pieces, but the full process was missing a number of details.

Note: MariaDB Server 10.4+ has Galera 4 installed by default, therefore we will cover all steps to install and configure MariaDB and the steps necessary to configure the default install of Galera Cluster.

MariaDB and Galera Cluster

1. Instantiate a server with Ubuntu 18.04 (Bionic Beaver) and name it galera-mariadb-1. We will refer it to by name going forward.
2. Download an example dataset from Kaggle, save it to your home directory and extract the CSV from the zip file.
3. Copy the extracted CSV to galera-mariadb-1:

cd ~/inpatient-hospital-charges/

# Choose (1) or (2) and replace the capitalized parameters

# (1) If you are not using GCP, use the scp command directly
scp ./inpatientCharges.csv USER_NAME@GALERA_MARIADB_1_ADDRESS:~/ -i PATH_TO_YOUR_KEY

# (2) If you are using GCP, use gcloud command instead, so you won't have to
# worry about the server address
gcloud compute scp ./inpatientCharges.csv USER_NAME@galera-mariadb-1:~/ \
--project PROJECT_NAME \
--zone SERVER_ZONE

4. Login via SSH to install MariaDB:

# Choose (1) or (2) and replace the capitalized parameters

# (1) If you are not using GCP, use the ssh command directly
ssh USER_NAME@GALERA_MARIADB_1_ADDRESS -i PATH_TO_YOUR_KEY

# (2) If you are using GCP, use gcloud command instead, so you won't have to
# worry about the server address
gcloud compute ssh --project PROJECT_NAME --zone SERVER_ZONE galera-mariadb-1

5. Run the following commands to install MariaDB Server 10.4:

sudo apt-get remove mariadb-server
sudo apt-get remove mariadb-server
sudo apt-get install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mariadb.mirror.liquidtelecom.com/repo/10.4/ubuntu bionic main'
sudo apt update
sudo apt -y install mariadb-server mariadb-client
sudo mysql_secure_installation

# Set a password to root: 'somepassword'
# Questions and answers:
# Switch to unix_socket authentication [Y/n] n
# Change the root password? [Y/n] n
# Remove anonymous users? [Y/n] y
# Disallow root login remotely? [Y/n] n
# Remove test database and access to it? [Y/n] y
# Reload privilege tables now? [Y/n] y

6. Open MariaDB console to create a database and table to be able to load our test data:

# Open console:
mysql -u root -p
-- Run scripts:
CREATE OR REPLACE DATABASE test;
USE test;
CREATE TABLE inpatient_hospital_charges (
  drg_definition VARCHAR(200),
  provider_id INT,
  provider_name VARCHAR(200),
  provider_street_address VARCHAR(200),
  provider_city VARCHAR(50),
  provider_state VARCHAR(10),
  provider_zip_code INT,
  hospital_referral_region_description VARCHAR(50),
  total_discharges INT,
  average_covered_charges VARCHAR(20),
  average_total_payments VARCHAR(20),
  average_medicare_payments VARCHAR(20)
);
-- Leave MariaDB console
exit

7. Import the CSV into the database:

# Move and rename the file so `mysqlimport` can import to the table created previously:

sudo mv ~/inpatientCharges.csv /var/lib/mysql/inpatient_hospital_charges.csv
mysqlimport --user='root' -p \
--fields-terminated-by=, --lines-terminated-by=' ' \
--replace --low-priority --fields-optionally-enclosed-by='"' \
--ignore-lines='1' --verbose \
test '/var/lib/mysql/inpatient_hospital_charges.csv'

8. Add an auto increment id to the imported table:

mysql -u root -p
USE test;
ALTER TABLE inpatient_hospital_charges ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

9. Exit the ssh session.
10. Create two copies of galera-mariadb-1 instance and name them galera-mariadb-2 and galera-mariadb-3. If you are using Google Cloud, you’ll want to tale note of the following steps
    1. Go to Google Cloud Compute Engine and click on galera-mariadb-1. Select “Create Machine Image” option and proceed to the creation.
    2. With the machine image created, go to “Machine images”, select the image and click on “Create instance”;
    3. Define then name of the new instance as galera-mariadb-2 and select the same region and zone;
    4. Confirm the creation of the instance galera-mariadb-2 and repeat this process to create galera-mariadb-3
11. SSH to each instance beginning in galera-mariadb-1
12. Run sudo vi /etc/mysql/my.cnf, go to the galera section and configure the parameters as follows, filling the appropriate gaps as stated in comments:

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Replace IP1, IP2 and IP3 with the local IP of each galera-mariadb server:
wsrep_cluster_address="gcomm://IP1,IP2,IP3"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0

13. If you are in instance galera-mariadb-1, start the cluster:

sudo galera_new_cluster

If you are in instances galera-mariadb-2 or galera-mariadb-3, start the MariaDB service:

sudo service mariadb start

14. Test if the cluster is running properly by running the following steps:

SSH to galera-mariadb-1 and check the number of nodes on the cluster:

sudo mysql -u root -e "show status like 'wsrep_cluster%';"

In case parameter wsrep_cluster_size == 3, move to the next step, otherwise check the troubleshooting session;

Insert the following data in galera-mariadb-1:

use test;
insert into inpatient_hospital_charges(drg_definition) values ("wilson's test");

Go to galera-mariadb-2 and galera-mariadb-3 and check if they were synchronized:

use test;
select * from inpatient_hospital_charges where drg_definition like 'wilson%';

15. SSH to galera-mariadb-1 and create a db user that can be used to connect to the database using its public IP. Note: these settings are for testing purposes only, you should lock this down further. One common settings would be only allowing connections from internal IPS at the network level:

CREATE USER 'cyral'@'localhost' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON *.* TO 'cyral'@'localhost' WITH GRANT OPTION;
CREATE USER 'cyral'@'%' IDENTIFIED BY 'somepassword';
GRANT ALL PRIVILEGES ON *.* TO 'cyral'@'%' WITH GRANT OPTION;

Installing HAProxy

There is a good tutorial here. You may follow it and add the bind keyword after listen galera or just follow the steps bellow:

1. Instantiate another instance with Ubuntu 18.04 (Bionic Beaver) and name it galera-load-balancer.
2. SSH to galera-load-balancer and install HAProxy:

apt-get install haproxy
sudo vi /etc/haproxy/haproxy.cfg
# Add the following lines to the end of file:
listen galera
    # Replace "IP" by server local IP
    bind IP:3306
    balance roundrobin
    mode tcp
    option tcpka
    option mysql-check user haproxy
    # Replace "IP" by galera-mariadb-1 local IP
    server galera-mariadb-1 IP:3306 check weight 1
    # Replace "IP" by galera-mariadb-2 local IP
    server galera-mariadb-2 IP:3306 check weight 1
    # Replace "IP" by galera-mariadb-3 local IP
    server galera-mariadb-3 IP:3306 check weight 1

Save file and leave ssh session.

3. SSH to galera-mariadb-X (X may be any database instance) and add a new user:

-- Replace IP by galera-load-balancer local IP
CREATE USER 'haproxy'@'IP';
FLUSH PRIVILEGES;

4. SSH back to galera-load-balancer and start HAProxy:

sudo service start haproxy

5. Test if a connection succeeds on localhost:

nc -z -v localhost 3306

6. Test if a connection succeeds on its local IP address:

# Replace IP with local IP
nc -z -v IP 3306

Troubleshooting

• Cluster must be started by the most advanced node (regarding database state). After that, any node can be started.
• Stop the cluster:

sudo killall -KILL mysql mysqld_safe mysqld mysql-systemd
sudo service mariadb stop

• Check the cluster status:

sudo mysql -u root -e "show status like 'wsrep_%';"

• If any other than the master node won’t start, try resetting the Galera quorum.

MariaDB with Galera and HAProxy is a great option if you need to scale your MariaDB or even MySQL backend. With a minimum of the 3 nodes we set up above, a single node can go down and your application and users won’t ever notice it. Galera offers great protection against data loss, inconsistent databases and future scalability issues. As we undertook this project, we didn’t find any complete documentation on how to create this specific configuration so we hope it’s helpful for your scaling projects.