High availability for MySQL on Amazon EC2 – Part 4 – The instance restart script


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

From the previous of this series, we now have resources configured but instead of starting MySQL, Pacemaker invokes a script to start (or restart) the EC2 instance running MySQL. This blog post describes the instance restart script. Remember, I am more a DBA than a script writer so it might not be written in the most optimal way.

First, let’s recap what’s the script has to perform (the full script is given below).

  1. Kill the MySQL EC2 instance if running
  2. Make sure the MySQL EC2 instance is stopped
  3. Prepare the user-data script for the new MySQL EC2 instance
  4. Launch the new MySQL instance
  5. Make sure it is running
  6. Reconfigure local heartbeat
  7. Broadcast the new MySQL instance IP to the application servers

Kill the MySQL EC2 instance

In order to kill the existing MySQL EC2 instance, we first have to identify it. This is done by:

by filtering on the AMI type of the instance. Since an instance can be listed at the “stopped” state, it is mandatory to filter for states “running” or “pending”. Then the instance is terminated with:

Make sure the MySQL EC2 instance is stopped

Terminating an EC2 instance is not instantaneous, we can confirm an instance is really stopped by monitoring its status and wait until it is actually “terminated”. The code below is how the script performs this task.

Prepare the user-data script for the new MySQL EC2 instance

The new MySQL instance will be running heartbeat. Since we cannot use neither Ethernet broadcast or multicast, we need to configure the new instance so that it communicates through unicast with its partner node in the cluster, the node on which the restart script is run. This configuration is achieved by providing a user-data script (see the hamysql.user-data below) which completes the heartbeat configuration of the new instance. The hamysql.user-data script only performs a search and replace operation on the /etc/ha.d/ha.cf file and then restart the heartbeat service. In order for this to work properly, we just have to put the IP of the current instance in the script like here:

Launch the new MySQL instance

Once things are ready, a new MySQL instance can be launched with:

Out of this operation, we retrieve the new instance “instance_id”.

Make sure it is running

Since we know the “instance_id” of the new instance, checking if it is running is easy:

Reconfigure local heartbeat

Now, Heartbeat, on the monitoring host, must be informed of the IP address of its new partner. In order to achieve this, a search and replace operation in the local ha.cf file followed of restart of Heartbeat is sufficient.

Broadcast the new MySQL instance IP to the application servers

The final phase is to inform the application servers that the IP of the MySQL has changed. The best way to list those application servers is through a security group and, provided the appropriate ssh keys have been exchanged, this code will push the IP update.

The full script:

The hamysql.user-data script:

The script sets the IP of the monitor host in the heartbeat ha.cf configuration file and then, finishes up some missing configuration settings of the AMI.


Share this post

Comments (6)

  • Yves Trudeau Reply

    If you look in the Pacemaker configuration here:

    You’ll see that you can define the path. The current config points to /usr/local/bin/mysql.

    August 19, 2010 at 12:00 am
  • Luke Reply

    where do the scripts go?

    August 19, 2010 at 12:00 am
  • Colin Reply

    Any idea when you’ll put up Part 4?

    October 13, 2010 at 7:46 pm
  • Yves Trudeau Reply

    Hi Colin,
    I am guessing you are talking about part 5. The draft is partly written, I have been very busy recently but I’ll find some time to complete the series.

    October 14, 2010 at 6:43 am
  • Colin Reply

    Yeah, as soon as I hit “Submit Comment” I realized I hit the wrong number! :-/

    Thanks, these are really interesting and I’m really curious about you’re instance monitoring script. 🙂

    October 14, 2010 at 9:07 am
  • Rickm Reply

    I found these series of notes very interesting and waiting to see your next part hopefully soon.
    Thanks a lot.

    November 24, 2010 at 3:36 pm

Leave a Reply