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.
On both database servers:
To start the replication, on the current slave execute:
Now that the replication has been setup, lets install the MHA tools on both database servers:
Next, on the two manager nodes, lets install MHA manager, HAProxy and keepalived. The manager nodes also need the MHA node packages:
Make sure you have a user with ssh keys deployed that MHA can use to ssh between all servers. The configs for MHA, HAProxy and keepalived follow:
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.
To manually start, check the status and stop the MHA manager run:
To test a failover, stop MySQL on the current master and watch MHA promote the slave to a master, and change the HAProxy config to reflect that.
