MySQL - Master to Passive Master Replication and fail-over

with the help of HAproxy.

The manual needs to be followed word by word...
as usual ... no warranties ...

Set-up

this set-up is for 2 haproxy servers with a VIP and keepalived running
Check that the Master and the Passive Master Databases are Healthy
Insert another row into the IMPORTANT database:
alter table stuff add happened TIMESTAMP AFTER details;
insert into important.stuff set details = 'Replicating Primary to Secondary';
check here:   select * from important.stuff order by id desc limit 1;

go to the second server and log into mysll
check here:   select * from important.stuff order by id desc limit 1;

Result :  identical !!!

Check the second server now:
insert into important.stuff set details = 'Replicating Secondary to Primary';
do the same checks on both servers.
you should see the identical results.

Now it is time to create the Application user(s) and database(s); well and some very-very-very-long-secure-password(s) ...
on server 1 ( replication is taking care of the rest )
use mysql;
create database <name1>
create database <name2>
GRANT ALL on <name1>.* TO '<name1>'@'%' IDENTIFIED BY PASSWORD '<name1>_pass';
GRANT ALL on <name2>.* TO '<name2>'@'%' IDENTIFIED BY PASSWORD '<name2>_pass';
GRANT ALL on important.* TO 'haproxy'@'%';  ( no password for haproxy checks )
check with:
show databases;
select user, host, password from user;
check that all is fine
from the proxy servers all accounts need testing
log on to each proxy cluster member and every account on every database server:
mysql <database> -h <db-server> -u <user-name> -p<password>
ensure every log-in is working before continuing

Configure HAproxy for the first time.
Create a basic haproxy config on all haproxy servers for the first tests. Use this example basic_haproxy.cfg
replace the default config /etc/haproxy/haproxy.cfg with the example (amend server names)
start the service on all servers:  service haproxy restart
now check the access from every proxy server several times:
mysql  -h localhost  -u haproxy -e'show variables like "server_id";'
you will see round robin database responses, then all is fine.
now check the output of:
echo "show stat" | socat stdio /var/lib/haproxy/stats
you should see the status print; if not fix it !!!
next go to one or more servers that will need connection to the database
check for several times via the VIP and via the individual proxy servers:
mysql  -h <proxy-vip>  -u haproxy -e'show variables like "server_id";'
you will see round robin database responses, then all is fine; if not fix it !!!  ( iptables, firewall et al )

Set up a Health Check
add details to the haproxy.cfg:
add this line above the server Primary line:  option mysql-check user haproxy
add after each port number (3306) the word:  check
restart the service: service haproxy restart
check the difference in the output of
echo "show stat" | socat stdio /var/lib/haproxy/stats
you should see the status print with mysql status checks and mysql version; if not fix it !!!
from now on we use the filtered status check:
echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
run it and see the output.

Configure HAProxy to be Active-Passive
all we need to do is to add the following:
add to the server Secondary <server-name>:3306 check, the word  "backup"  at the end
restart the service: service haproxy restart
all connection will go to the primary server
run the status check
echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
explanation: MySQL,Primary is active (1,0) and MySQL,Secondary is backup (0,1)

Job done - Active Passive is now running

Test Fail-over by taking the Primary Down
log-on to the primary server and run:
service mysqld stop
back on any of the proxy servers run:
echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
and see MySQL,Primary,DOWN,1,0
check the MySQL connection, all goes to the Secondary, after a short delay.

Job done - Fail-over to Secondary is working

Enabling the Maintenance Mode
This needs a small addendum to all Proxy server configs:
add to the end of stats socket line:  level admin
restart the service on all servers:  service haproxy restart
To enable the maintenance mode a message needs to be sent to the stats socket:
echo "disable server MySQL/Primary" | socat stdio /var/lib/haproxy/stats
run: echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
and it shows:  MySQL,Primary,MAINT,1,0
all traffic is routed to the secondary without any health check traffic

Now stop both slaves, Primary first, then Secondary.
take a  FULL BACK-UP !!!
then do Maintenance, Updates, Changes etc.

Once finished start the slave on the Primary to get the data changes from the slave.
Activate the Primary Database as required with:
echo "enable server MySQL/Primary" | socat stdio /var/lib/haproxy/stats
run: echo "show stat" | socat stdio /var/lib/haproxy/stats | cut -d, -f1,2,18,20,21
and it shows:  MySQL,Primary,UP,1,0
all traffic is routed to the primary, again with health check traffic
Lastly start the slave on the secondary to apply the changes.

Job done - Maintenance mode is available

Quote:
Don't take the author's word for anything; prove it to yourself.
Do the exercises and invent your own.
—James Hague