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.
Step guide to create a load-balanced MySQL cluster with Pacemaker and HAProxy:
- Install the required packages on all nodes.
sudo apt-get update && sudo apt-get install -y mariadb-server mariadb-client pacemaker pcs haproxy
- Configure the MariaDB or MySQL service on all nodes.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
- 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"
- Start the MariaDB or MySQL service on the first node with the –wsrep-new-cluster option.
sudo systemctl start mariadb --wsrep-new-cluster
- Start the MariaDB or MySQL service on the remaining nodes.
sudo systemctl start mariadb
- Verify that the cluster is up and running by checking the cluster size.
mysql -u root -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
- Create a virtual IP address for the cluster using pcs.
sudo pcs cluster setup --name my_cluster NODE1_HOSTNAME NODE2_HOSTNAME NODE3_HOSTNAME
- Enable and start the Pacemaker and Corosync services.
sudo systemctl enable pacemaker corosync && sudo systemctl start pacemaker corosync
- 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
- Configure the HAProxy load balancer by editing its configuration file.
sudo nano /etc/haproxy/haproxy.cfg
- 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
- Enable and start the HAProxy service.
sudo systemctl enable haproxy && sudo systemctl start haproxy
Always maintain a backup of your database before making significant changes. Ensure you test this setup in a non-production environment before deploying in a live environment.
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.
Mohd Shakir Zakaria is an experienced cloud architect with a strong development and open-source advocacy background. He boasts multiple certifications in AWS, Red Hat, VMware, ITIL, and Linux, underscoring his expertise in cloud architecture and system administration.
Comment anonymously. Login not required.