Fail Over MySQL with HAProxy

Background

Once you've established replication, you still need to ensure the applications that use this database can always find a healthy replica.

This procedure takes an existing Master-Master Replication and sets up fail over between the two servers, using the excellent open source HAProxy load balancer.

This lesson focuses on getting MySQL ready, and building up a simple HAProxy configuration. In production it would be wise to avoid a single point of failure by using two HAProxies, with keepalived directing traffic between them. It may also be wise to implement more detailed health checks between HAProxy and MySQL. This blog post introduces several shell scripts that can check the presence of vital databases, the status of replication, and even allow a server to voluntarily fail health check for maintenance.

Steps

Check that the Primary and Secondary Databases are Healthy

Primary and Secondary should already be replicating, as at the end of the Establish Master-Master Replication procedure.

Connect to the Primary: primary.example.com

Connect to MySQL and insert a new row:

primary ~ $ mysql -u root

mysql> insert into important.stuff set details = 'Replicating Primary to Secondary';
Query OK, 1 row affected (0.02 sec)

SSH to the Secondary: secondary.example.com

Connect to MySQL and verify the row you inserted on the Primary has replicated:

secondary ~ $ mysql -u root
mysql> select * from important.stuff order by id desc limit 1;
+-----+----------------------------------+---------------------+
| id  | details                          | happened            |
+-----+----------------------------------+---------------------+
| 296 | Replicating Primary to Secondary | 2013-04-12 14:09:26 |
+-----+----------------------------------+---------------------+
1 row in set (0.00 sec)

mysql>

Still on the Secondary, insert another row:

mysql> insert into important.stuff set details = 'Replicating Secondary to Primary';
Query OK, 1 row affected (0.02 sec)

Returning to the Primary, verify the row has replicated:

mysql> select * from important.stuff order by id desc limit 1;
+-----+----------------------------------+---------------------+
| id  | details                          | happened            |
+-----+----------------------------------+---------------------+
| 297 | Replicating Secondary to Primary | 2013-04-12 14:10:26 |
+-----+----------------------------------+---------------------+
1 row in set (0.00 sec)

mysql>
After this step is completed:
  1. On the primary, INSERT a new record in the table important.stuff
  2. Within 1 second, that record is replicated to the secondary
  3. On the secondary, INSERT a new record in the table important.stuff
  4. Within 1 second, that record is replicated to the primary
Test the Application can Connect from the Proxy

The database has a user configured named application with the privileges a web app might require on the table important.stuff.

SSH to the Proxy: proxy.example.com

Make sure the application user can log in from the Proxy server.

proxy ~ $ mysql -h primary.example.com -u application -pHeadGasMeshBass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
proxy ~ $ mysql -h secondary.example.com -u application -pHeadGasMeshBass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
After this step is completed:
  1. From the proxy, the user application can connect to MySQL on the primary.
  2. From the proxy, the user application can connect to MySQL on the secondary.
Install and Configure HAProxy

Install the HAProxy package on the Proxy server:

proxy ~ $ sudo yum install haproxy
Loaded plugins: priorities, security, update-motd, upgrade-helper
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package haproxy.x86_64 0:1.4.22-3.2.amzn1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================
 Package        Arch          Version                  Repository        Size
==============================================================================
Installing:
 haproxy        x86_64        1.4.22-3.2.amzn1         amzn-main        511 k

Transaction Summary
==============================================================================
Install       1 Package(s)

Total download size: 511 k
Installed size: 1.4 M
Is this ok [y/N]: y
Downloading Packages:
haproxy-1.4.22-3.2.amzn1.x86_64.rpm                    | 511 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : haproxy-1.4.22-3.2.amzn1.x86_64                            1/1
  Verifying  : haproxy-1.4.22-3.2.amzn1.x86_64                            1/1

Installed:
  haproxy.x86_64 0:1.4.22-3.2.amzn1

Complete!
proxy ~ $ 

Now edit the HAProxy config file, /etc/haproxy/haproxy.cfg

proxy ~ $ sudoedit /etc/haproxy/haproxy.cfg

First, delete everything below the global block. The defaults that ship with HAProxy are tuned for web servers, not database servers.

/etc/haproxy/haproxy.cfg
#--------------------------------------------------------------------- # Example configuration for a possible web application. See the # full configuration options online. # # http://haproxy.1wt.eu/download/1.4/doc/configuration.txt # #--------------------------------------------------------------------- #--------------------------------------------------------------------- # Global settings #--------------------------------------------------------------------- global # to have these messages end up in /var/log/haproxy.log you will # need to: # # 1) configure syslog to accept network log events. This is done # by adding the '-r' option to the SYSLOGD_OPTIONS in # /etc/sysconfig/syslog # # 2) configure local2 events to go to the /var/log/haproxy.log # file. A line like the following can be added to # /etc/sysconfig/syslog # # local2.* /var/log/haproxy.log # log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid maxconn 4000 user haproxy group haproxy daemon # turn on stats unix socket stats socket /var/lib/haproxy/stats ...Delete all original content below this point...

Then insert this new config at the bottom of the file:

/etc/haproxy/haproxy.cfg
......some content not shown group haproxy daemon # turn on stats unix socket stats socket /var/lib/haproxy/stats mode 777 listen MySQL 0.0.0.0:3306 timeout connect 10s timeout client 1m timeout server 1m mode tcp server Primary primary.example.com:3306 server Secondary secondary.example.com:3306

Restart the HAProxy service to apply the new config.

proxy ~ $ sudo service haproxy restart
Stopping haproxy:                                          [FAILED]
Starting haproxy:                                          [  OK  ]
After this step is completed:
  1. HA Proxy is running.
  2. The stats socket /var/lib/haproxy/stats has permissions srwxrwxrwx
  3. HA Proxy has a listener configured, named MySQL
  4. The listener contains the server primary.example.com
  5. The listener contains the server secondary.example.com
Test Round Robin Load Balancing through HA Proxy

The HAProxy is listening on port 3306 (the default MySQL port) on the Proxy server. Connections to MySQL through the Proxy's hostname, use HAProxy to redirect to either Primary or Secondary.

You can see which server HAProxy has connected you to by looking up the server_id, which has to be unique on every server participating in replication.

The Primary is configured with server_id 10.

The Secondary is configured with server_id 20.

Use the MySQL client's -e flag to get the results of one command, and disconnect.

Repeatedly checking the server_id, shows that the HAProxy connects to a different server each time:

proxy ~ $ mysql -h proxy.example.com -u application -pHeadGasMeshBass -e'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
proxy ~ $ mysql -h proxy.example.com -u application -pHeadGasMeshBass -e'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 20    |
+---------------+-------+
proxy ~ $ 
After this step is completed:
  1. Repeated connections to MySQL on proxy.example.com alternate between server_id 10 and 20.
Set up a Health Check

On the Primary, connect to MySQL and configure a user HAProxy can use to check the health of the two servers. This user doesn't need specific privileges, it will just need to connect and pass authentication.

mysql> GRANT USAGE ON *.* TO 'haproxy'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql>

This new MySQL user will replicate to the Secondary automatically.

Test that the Proxy server can connect as the haproxy user to both the Primary and the Secondary.

proxy ~ $ mysql -h primary.example.com -u haproxy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 268
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
proxy ~ $ mysql -h secondary.example.com -u haproxy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 346
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

Now, edit /etc/haproxy/haproxy.cnf to use the MySQL health check on both servers.

proxy ~ $ sudoedit /etc/haproxy/haproxy.cfg
/etc/haproxy/haproxy.cfg
......some content not shown listen MySQL 0.0.0.0:3306 timeout connect 10s timeout client 1m timeout server 1m mode tcp option mysql-check user haproxy server Primary primary.example.com:3306 check server Secondary secondary.example.com:3306 check

Then restart HAProxy.

proxy ~ $ sudo service haproxy restart
Stopping haproxy:                                          [  OK  ]
Starting haproxy:                                          [  OK  ]

Now we can ask HAProxy for the status of our servers. HAProxy established a socket at /var/lib/haproxy/stats to take commands and show status. (It could also host a web interface.)

proxy ~ $ echo "show stat" | socat stdio /var/lib/haproxy/stats
# pxname,svname,qcur,qmax,scur,smax,slim,stot,bin,bout,dreq,dresp,ereq,econ,eresp,wretr,wredis,status,weight,act,bck,chkfail,chkdown,lastchg,downtime,qlimit,pid,iid,sid,throttle,lbtot,tracked,type,rate,rate_lim,rate_max,check_status,check_code,check_duration,hrsp_1xx,hrsp_2xx,hrsp_3xx,hrsp_4xx,hrsp_5xx,hrsp_other,hanafail,req_rate,req_rate_max,req_tot,cli_abrt,srv_abrt,
MySQL,FRONTEND,,,0,0,2000,0,0,0,0,0,0,,,,,OPEN,,,,,,,,,1,1,0,,,,0,0,0,0,,,,,,,,,,,0,0,0,,,
MySQL,Primary,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,42,0,,1,1,1,,0,,2,0,,0,L7OK,0,1,,,,,,,0,,,,0,0,
MySQL,Secondary,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,42,0,,1,1,2,,0,,2,0,,0,L7OK,0,1,,,,,,,0,,,,0,0,
MySQL,BACKEND,0,0,0,0,2000,0,0,0,0,0,,0,0,0,0,UP,2,2,0,,0,42,0,,1,1,0,,0,,1,0,,0,,,,,,,,,,,,,,0,0,

show stat returns a lot of information in comma-separated value format. Use the cut command to show just the first, second, and 18th column.

proxy ~ $ echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18
# pxname,svname,status
MySQL,FRONTEND,OPEN
MySQL,Primary,UP
MySQL,Secondary,UP
MySQL,BACKEND,UP

The first column, MySQL is the name of our proxy, set in the listen MySQL line of our config file.

The rows MySQL,FRONTEND and MySQL,BACKEND are generated automatically because we used the simplified listen configuration. More complicated configurations define the frontend and backend separately, see the HAProxy documentation.

MySQL,Primary and MySQL,Secondary are the servers, and both report that they are UP

After this step is completed:
  1. HAProxy reports the Primary is UP
  2. HAProxy reports the Secondary is UP
Configure HAProxy to be Active-Passive

It is not a good idea to commit writes to both masters at the same time. The Repair Replication procedure illustrates a primary key collision, but there are many ways asynchronous replication can cause inconsistencies.

Instead, as long as the Primary is available, HAProxy should always direct traffic to it.

Edit /etc/haproxy/haproxy.cnf to set the Secondary to be a backup.

proxy ~ $ sudoedit /etc/haproxy/haproxy.cfg
/etc/haproxy/haproxy.cfg
......some content not shown listen MySQL 0.0.0.0:3306 timeout connect 10s timeout client 1m timeout server 1m mode tcp option mysql-check user haproxy server Primary primary.example.com:3306 check server Secondary secondary.example.com:3306 check backup

Then restart HAProxy.

proxy ~ $ sudo service haproxy restart
Stopping haproxy:                                          [  OK  ]
Starting haproxy:                                          [  OK  ]

Ask HAProxy for the status of the servers again. This time, add column 20 and 21 to the cut command.

proxy ~ $ echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
# pxname,svname,status,act,bck
MySQL,FRONTEND,OPEN,,
MySQL,Primary,UP,1,0
MySQL,Secondary,UP,0,1
MySQL,BACKEND,UP,1,1

Adding the backup option in the config file has separated the servers into two groups, active and backup. HAProxy won't direct any traffic to any backup server until all the active servers are down.

On the BACKEND row, the act and bck columns count the number of healthy servers in each state: there is one active server, and one backup server.

On the server rows, the act and bck columns tell us which type a server is. The Primary is active (1,0) and the Secondary is backup (0,1)

Now, connecting repeatedly through HAProxy should always return server_id 10:

proxy ~ $ mysql -h proxy.example.com -u application -pHeadGasMeshBass -e'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
proxy ~ $ mysql -h proxy.example.com -u application -pHeadGasMeshBass -e'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
proxy ~ $ 
After this step is completed:
  1. HAProxy shows the Primary is UP and active.
  2. HAProxy shows the Secondary is UP and backup.
  3. Repeated connections to MySQL through the Proxy always connect to the Primary
Test Failover by taking the Primary Down

Stop the MySQL service on the Primary.

mysql> exit
Bye
primary ~ $ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]

Now, on the Proxy, verify that HAProxy's health check has failed:

proxy ~ $ echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
# pxname,svname,status,act,bck
MySQL,FRONTEND,OPEN,,
MySQL,Primary,DOWN,1,0
MySQL,Secondary,UP,0,1
MySQL,BACKEND,UP,0,1

There are two highlighted changes here. First, the Primary has been detected as DOWN. Second, there are now 0 active servers in the MySQL,BACKEND row, so traffic will be directed to the 1 live backup server.

Connect to MySQL through the proxy:

proxy ~ $ mysql -h proxy.example.com -u application -pHeadGasMeshBass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1991
Server version: 5.5.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>        

Validate you've been connected to the Secondary:

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 20    |
+---------------+-------+
1 row in set (0.00 sec)

While the Primary is down, the Secondary can keep handling read and write traffic from the application. INSERT a new record in important.stuff.

mysql> insert into important.stuff set details='Inserted with primary down';
Query OK, 1 row affected (0.01 sec)
After this step is completed:
  1. On the Primary, MySQL service is stopped.
  2. HAProxy reports the Primary is DOWN
  3. Connections to MySQL through the Proxy always connect to the Secondary
Restore the Primary, Verify Replication Brings it Up to Date

Restart the MySQL process on the Primary:

primary ~ $ sudo service mysqld start
Starting mysqld:                                           [  OK  ]

Back on the Proxy server, close your MySQL connection through the HAProxy. Verify that HAProxy detects that the Primary is back up.

mysql> exit
Bye
proxy ~ $ echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
# pxname,svname,status,act,bck
MySQL,FRONTEND,OPEN,,
MySQL,Primary,UP,1,0
MySQL,Secondary,UP,0,1
MySQL,BACKEND,UP,1,1

Connect to MySQL through the Proxy again, and verify that you've reached the Primary:

proxy ~ $ mysql -h proxy.example.com -u application -pHeadGasMeshBass

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
1 row in set (0.00 sec)

Check that the Primary has replicated the changes made on the Secondary while the Primary was out of service.

mysql> select * from important.stuff order by id desc limit 1;
+-----+----------------------------+---------------------+
| id  | details                    | happened            |
+-----+----------------------------+---------------------+
| 289 | Inserted with primary down | 2013-05-02 17:22:38 |
+-----+----------------------------+---------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
After this step is completed:
  1. On the Primary, MySQL service is running.
  2. HAProxy reports the Primary is UP
  3. Connections to MySQL through the Proxy always connect to the Primary
Take the Primary Offline for Maintenance

It will occasionally be necessary to choose to failover to the Secondary, for example to perform maintenance or upgrades on the Primary. In that case, we'll need to tell HAProxy to ignore the health check and never send traffic to the Primary.

First, we need to configure the HAProxy socket to take changes. Edit /etc/haproxy/haproxy.cfg

proxy ~ $ sudoedit /etc/haproxy/haproxy.cfg

And update the stats socket to enable admin access.

/etc/haproxy/haproxy.cfg
......some content not shown group haproxy daemon # turn on stats unix socket stats socket /var/lib/haproxy/stats mode 777 level admin listen MySQL 0.0.0.0:3306 timeout connect 10s timeout client 1m ......some content not shown

Restart the HAProxy service to apply the new config.

proxy ~ $ sudo service haproxy restart
Stopping haproxy:                                          [FAILED]
Starting haproxy:                                          [  OK  ]

Now tell HAProxy to disable the Primary server.

proxy ~ $ echo "disable server MySQL/Primary" | socat stdio /var/lib/haproxy/stats

proxy ~ $ echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
# pxname,svname,status,act,bck
MySQL,FRONTEND,OPEN,,
MySQL,Primary,MAINT,1,0
MySQL,Secondary,UP,0,1
MySQL,BACKEND,UP,0,1

The Primary server reports its status as MAINT and does not receive any health check traffic.

After this step is completed:
  1. HAProxy reports the Primary is in MAINT mode.
  2. Connections to MySQL through the Proxy always connect to the Secondary
Restore Primary from Maintenance

To restore service, re-enable the Primary server through HAProxy:

proxy ~ $ echo "enable server MySQL/Primary" | socat stdio /var/lib/haproxy/stats

proxy ~ $ echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
# pxname,svname,status,act,bck
MySQL,FRONTEND,OPEN,,
MySQL,Primary,UP,1,0
MySQL,Secondary,UP,0,1
MySQL,BACKEND,UP,1,1
After this step is completed:
  1. HAProxy reports the Primary is UP
  2. Connections to MySQL through the Proxy always connect to the Primary
Set HAProxy to Start Automatically

By default the HAProxy service doesn't start automatically after a reboot.

You can verify this by running:

proxy ~ $ chkconfig --list haproxy
haproxy            0:off    1:off    2:off    3:off    4:off    5:off    6:off

We want HAProxy to start automatically for any appropriate runlevel. This command starts HAProxy in all the default runlevels, excluding 0 (used to turn the system off), 1 (used to recover from severe issues), and 6 (used to reboot).

proxy ~ $ sudo chkconfig haproxy on
proxy ~ $ 

Now check this server's current (normal) runlevel, and verify that HAProxy will start automatically the next time the server starts.

proxy ~ $ runlevel
N 3
proxy ~ $ chkconfig --list haproxy
haproxy            0:off    1:off    2:on    3:on    4:on    5:on    6:off
After this step is completed:
  1. On the Proxy, chkconfig reports that HAProxy will start automatically in runlevel 3.