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

Creating a load-balanced MySQL or MariaDB cluster with Pacemaker and HAProxy is a critical task for maintaining high availability and fault tolerance in your database environment. Pacemaker is an open-source high-availability resource manager that ensures your applications and services remain accessible during unexpected failures. HAProxy is a high-performance load balancer that distributes incoming requests to multiple servers to optimize performance and maintain uptime. By combining these two technologies, you can create a resilient and scalable database cluster capable of handling high levels of traffic.

This guide will walk you through the process of setting up a MySQL or MariaDB cluster, configuring Pacemaker for high availability, and using HAProxy to distribute incoming requests evenly across your cluster. The steps provided assume you have a basic understanding of Linux system administration and networking concepts.

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