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.
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>
important.stuff
important.stuff
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
application
can connect to MySQL on the primary.application
can connect to MySQL on the secondary.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 ]
/var/lib/haproxy/stats
has permissions srwxrwxrwx
listener
configured, named MySQL
listener
contains the server primary.example.com
listener
contains the server secondary.example.com
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 ~ $
proxy.example.com
alternate between server_id
10 and 20.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
UP
UP
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 ~ $
UP
and active.UP
and backup.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)
DOWN
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
UP
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.
MAINT
mode.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
UP
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
chkconfig
reports that HAProxy will start automatically in runlevel 3.