Newly Updated “MySQL Load Balancing with HAProxy” Tutorial

We are glad to announce that one of our most popular tutorials has just been updated and is available here. In this tutorial, we cover how HAProxy works, and how to deploy, configure and manage HAProxy in conjunction with MySQL using ClusterControl.

Applications typically connect to a database cluster or replicated setup by opening connections on one of the database nodes in order to run transactions. If the database node fails, the client would need to reconnect to another database node before it can continue to serve requests. There are database drive that supports connection pooling, load balancing and failover e.g. Connector/J and PHP MySQL native driver for master slave. However, in other clustering setup like Galera Cluster for MySQL or MariaDB, the JDBC and PHP drivers are not aware of internal Galera state information, and introducing a reverse proxy layer will make your architecture expandable in the future.

In short, there are 3 health check methods that can be used with MySQL:

• mysql-check
• tcp-check
• httpchk

Some health check methods require a health check script tailored for it. Configuring health check is the key component to correctly identify the backend MySQL servers so HAProxy can route the incoming connections to the correct server. It reduces the application complexity when dealing with constantly changing cluster or replication state.

We also covered how HAProxy performs failure detection, what configuration options should we tweak for different environments, what is the recommended architecture to avoid single point of failure (SPOF), etc.

New chapters introduced in this tutorial:

• Read-write splitting with HAProxy
• How health check should be performed on different MySQL setups
• Updated deployment instructions with ClusterControl
• HAProxy redundancy with Keepalived
• Monitoring HAProxy
• Troubleshooting and workaround on common issues

Take a look at the tutorial. Happy load balancing!