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.
sudo apt-get update && sudo apt-get install -y mariadb-server mariadb-client pacemaker pcs haproxy
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 0.0.0.0 wsrep_provider = /usr/lib/galera/libgalera_smm.so wsrep_cluster_address = "gcomm://NODE1_IP,NODE2_IP,NODE3_IP"
sudo systemctl start mariadb --wsrep-new-cluster
sudo systemctl start mariadb
mysql -u root -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
sudo pcs cluster setup --name my_cluster NODE1_HOSTNAME NODE2_HOSTNAME NODE3_HOSTNAME
sudo systemctl enable pacemaker corosync && sudo systemctl start pacemaker corosync
sudo pcs resource create Cluster_VIP ocf:heartbeat:IPaddr2 ip=VIRTUAL_IP_ADDRESS cidr_netmask=NETMASK op monitor interval=30s
sudo nano /etc/haproxy/haproxy.cfg
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
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.
Comment anonymously. Login not required.