Severalnines Blog
The automation and management blog for open source databases

Newly Updated “MySQL Load Balancing with HAProxy” Tutorial

Severalnines
March 01, 2016

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!

Related Post

MySQL Replication failover: Maxscale vs MHA (part 3)

Severalnines
February 24, 2016

This post describes how you can implement automated failover with MariaDB using Maxscale and MHA and compares the two with each other.

Posted in: MySQL Cluster

MySQL Replication failover: Maxscale vs MHA (part 1)

Severalnines
February 22, 2016

This post describes how you can implement automated failover with MariaDB MHA.

Posted in: MySQL Cluster

High availability read-write splitting with php-mysqlnd, MySQL Replication and HAProxy

Severalnines
February 08, 2016

In this blog post, we explore the use of php-mysqlnd_ms with a PHP application (Wordpress) on a standard MySQL Replication backend.

Posted in: MySQL Cluster

New ClusterControl User Guide for MySQL based Clusters

Severalnines
November 19, 2014

Following the release of ClusterControl 1.2.8 with a range of cool new features, we have now also published a new ClusterControl User Guide that provides all the steps, tips & tricks to follow in order to successfully deploy, monitor, manage and scale database clusters.

Posted in: MySQL Cluster