How to create load-balanced MySQL or MariaDB cluster with Pacemaker and HAProxy

High availability is vital for mission-critical applications, and this holds especially true for databases, where any downtime can lead to significant losses, both financially and in terms of data integrity. MySQL and MariaDB are among the most popular relational database management systems (RDBMS) and can be set up in a high-availability cluster configuration using Pacemaker and HAProxy.

Pacemaker is an open-source high-availability resource manager used to manage and monitor cluster services. On the other hand, HAProxy stands out as a leading load balancer, efficiently distributing incoming requests across multiple nodes. When combined, these two tools provide a seamless and highly available database setup ensuring minimal service interruptions.

This tutorial will guide you through the creation of a load-balanced MySQL or MariaDB cluster using Pacemaker and HAProxy. While our examples utilize Debian-based commands, adaptations may be required for different Linux distributions.

1. Install the required packages on all nodes.

   sudo apt-get update && sudo apt-get install -y mariadb-server mariadb-client pacemaker pcs haproxy

2. Configure the MariaDB or MySQL service on all nodes.

   sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

3. Add the following lines to the [mysqld] section in the configuration file and save your changes.

   bind-address = 0.0.0.0
   wsrep_provider = /usr/lib/galera/libgalera_smm.so
   wsrep_cluster_address = "gcomm://NODE1_IP,NODE2_IP,NODE3_IP"

4. Start the MariaDB or MySQL service on the first node with the –wsrep-new-cluster option.

   sudo systemctl start mariadb --wsrep-new-cluster

5. Start the MariaDB or MySQL service on the remaining nodes.

   sudo systemctl start mariadb

6. Verify that the cluster is up and running by checking the cluster size.

   mysql -u root -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

7. Create a virtual IP address for the cluster using pcs.

   sudo pcs cluster setup --name my_cluster NODE1_HOSTNAME NODE2_HOSTNAME NODE3_HOSTNAME

8. Enable and start the Pacemaker and Corosync services.

   sudo systemctl enable pacemaker corosync && sudo systemctl start pacemaker corosync

9. Configure a Pacemaker resource for the virtual IP address.

   sudo pcs resource create Cluster_VIP ocf:heartbeat:IPaddr2 ip=VIRTUAL_IP_ADDRESS cidr_netmask=NETMASK op monitor interval=30s

10. Configure the HAProxy load balancer by editing its configuration file.

    sudo nano /etc/haproxy/haproxy.cfg

11. Add the following lines to the configuration file to define a frontend, backend, and listen section for the MySQL or MariaDB cluster, then save your changes.

    frontend mysql_frontend
        bind *:3306
        mode tcp
        default_backend mysql_backend
    
    backend mysql_backend
        mode tcp
        balance leastconn
        option tcp-check
        tcp-check connect port 3306
        server node1 NODE1_IP:3306 check
        server node2 NODE2_IP:3306 check
        server node3 NODE3_IP:3306 check

12. Enable and start the HAProxy service.

    sudo systemctl enable haproxy && sudo systemctl start haproxy

Setting up a load-balanced MySQL or MariaDB cluster might seem daunting, but by following the steps outlined and understanding the significance of each action, you'll be well-equipped to manage a high-availability RDBMS configuration.