Pages

The problem of setting up a highly available and scalable MySQL system has been worked on for more than a decade, with many possible solutions available - MariaDB Galera Cluster, Percona XtraDB Cluster, MySQL Cluster CGE and many other custom setups just to name a few . The one I am going to demonstrate today involves MHA [1], HAProxy [2] (for scaling reads and writes) and keepalived to implement VRRP [3]. The benefit of using MHA is that you can use any storage engine you want, along with Oracle, Maria or Percona SQL servers. It also works well with just traditional replication or with GTIDs.

MHA - or Master High Availability Manager and tools for MySQL - is a set of tools written in Perl, that consist of a manager that sits on a dedicated host, and a collection of scripts residing on the MySQL nodes. The manager monitors the status of the cluster and when the master fails, it promotes the most current slave to be the new master and executes a script to deal with the failover (e.g. moves the virtual IP, or makes a change in a config file etc). This is accomplished by the manager ssh-ing to the MySQL nodes, and running the scripts, scp-ing relay log etc. If allowing a process to ssh to your database servers is not an option for you, then this setup is not the best choice.

For this example we'll have four servers - two for the MHA manager and HAProxy, and two for the master-slave MySQL servers. I'll be using GTID based replication [4].

First lets start by setting up GTID replication between the master and the slave.

The first config is for the MHA manager. Line 11 specifies what script to execute after the new master is promoted. The script just changes the HAProxy config, but it can be anything. Lines 13-17 define the MySQL servers. MHA will determine which one is the master and which one is the slave.
The HAProxy config file is pretty self explanatory, for more information you can read my other HAProxy posts.
The only interesting part about the keepalived config is on line 88, where we specify what script keepalived will trigger if the current MHA server fails - it will basically start haproxy on the standby server along with the MHA manager.