Security Bloggers Network 
SBN

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

by Wilson de Carvalho on June 11, 2020

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 directlyscp ./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 addressgcloud compute scp ./inpatientCharges.csv USER_NAME@galera-mariadb-1:~/ \--project PROJECT_NAME \--zone SERVER_ZONE
  1. 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 directlyssh 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 addressgcloud compute ssh --project PROJECT_NAME --zone SERVER_ZONE galera-mariadb-1
  1. Run the following commands to install MariaDB Server 10.4:
sudo apt-get remove mariadb-serversudo apt-get remove mariadb-serversudo apt-get install software-properties-commonsudo 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 updatesudo apt -y install mariadb-server mariadb-clientsudo 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
  1. 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 consoleexit
  1. 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.csvmysqlimport --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'
  1. Add an auto increment id to the imported table:
mysql -u root -pUSE test;ALTER TABLE inpatient_hospital_charges ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
  1. Exit the ssh session.
  2. 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
  3. SSH to each instance beginning in galera-mariadb-1
  4. 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 settingswsrep_on=ONwsrep_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=rowdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2## Allow server to accept connections on all interfaces.#bind-address=0.0.0.0
  1. 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
  1. 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%';
  1. 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 haproxysudo 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.

  1. SSH to galera-mariadb-X (X may be any database instance) and add a new user:
-- Replace IP by galera-load-balancer local IPCREATE USER 'haproxy'@'IP';FLUSH PRIVILEGES;
  1. SSH back to galera-load-balancer and start HAProxy:
sudo service start haproxy
  1. Test if a connection succeeds on localhost:
nc -z -v localhost 3306
  1. Test if a connection succeeds on its local IP address:
# Replace IP with local IPnc -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-systemdsudo service mariadb stop
  • Check the cluster status:
sudo mysql -u root -e "show status like 'wsrep_%';"

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.

The post How-to: Setup a MariaDB Cluster with Galera and HAProxy appeared first on Cyral.

*** This is a Security Bloggers Network syndicated blog from Blog – Cyral authored by Wilson de Carvalho. Read the original post at: https://cyral.com/blog/how-to-galera-mariadb-haproxy/

Wilson de Carvalho