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.stuffimportant.stuffThe 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 srwxrwxrwxlistener configured, named MySQLlistener contains the server primary.example.comlistener contains the server secondary.example.comThe 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
UPUPIt 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)
DOWNRestart 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
UPIt 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
UPBy 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.