November 23, 2014

High availability for MySQL on Amazon EC2 – Part 5 – The instance monitoring script

This post is the fifth of a series that started here.

From the previous posts of this series, we now have an instance restart script that can restart the database node in case of failure and automatically reconfigure Pacemaker and the other servers that needs to access the MySQL server. What we will cover in this post is the monitoring script that run on the MySQL node.

At its smallest expression, the instance monitoring script is a simple empty loop that will run forever like:

Although fully functional, this monitoring script is rather weak could be not working and the cluster would unaware of it. Very complex monitoring scripts can be written but let’s provide a basic functional one that monitor MySQL with the mysqladmin ping command.

For the ones that knows Pacemaker a bit, they could wonder why I didn’t use crm_resource to migrate the MySQL resource to the other node. The problem with this approach is that the migration with crm_resource is achieved by setting the host affinity for the resource to INFINITY for the Monitor node. When the resource is moved, the MySQL node is restarted and its local copy of the cluster configuration is lost. After restart, the copy of the cluster configuration on the Monitor node will be pulled back by the MySQL node and since this copy has INFINITY as affinity of the resource to stay on the monitor host, the resource will stay there. If the resource is not move away quickly (within 5 min) from the Monitor node after the restart of the MySQL node, the instance restart script on the Monitor node will loop and restart again the MySQL node. Stopping the heartbeat service achieve the desired result without polluting the cluster configuration.

The following post in this series will add some details as how the IP of the MySQL server is broadcast to the client nodes.

About Yves Trudeau

Yves is a Principal Consultant at Percona, specializing in technologies such as MySQL Cluster, Pacemaker and DRBD. He was previously a senior consultant for MySQL and Sun Microsystems. He holds a Ph.D. in Experimental Physics.

Speak Your Mind

*