RightScale Blog

Cloud Management Blog
Cloud Management Blog

Setting Up MySQL with the RightScale MySQL Manager

Setting up a redundant MySQL master/slave database using RightScale has become easy using our recently introduced Manager for MySQL. Let's start by setting up the master. For this we define a "MySQL master" ServerTemplate. A ServerTemplate is pretty much what the name implies: a template for a fully configured server that can be launched with one click of a button. Each ServerTemplate is based on an AMI (Amazon Machine Image) and then adds a number of boot and operational RightScripts. (For more details see the rationale for RightScripts.) A boot RightScript is a script (bash, perl, ruby, ...) augmented with input parameters and file attachments. It runs during the launching of an instance and typically configures a software component. An operational RightScript is similar to a boot script but can be run from the RightScale web dashboard anytime after the instance becomes operational. The example below will make this easy to understand.

Below is a screen shot of the freshly created "Demo MySQL master" ServerTemplate with two RightScripts added. The first one switches the /mnt 160GB partition to LVM (linux volume manager) so we can take snapshot backups. The second one installs MySQL onto the server, ready to act as a master node.

MySQL Demo 1

Let's take a look at the mysql install RightScript below to see what's going on. First of all, most of it is a bash script. It's augmented by a number of yum packages that will be installed before the script runs and a file attachment at the bottom that contains the my.cnf config file.

 MySQL Demo 2

The first two lines of the script are worth mentioning: they pull in all the EC2 meta-data and user-data that is passed into the instance at launch time. These are available from EC2 and the RightScale boot-up scripts fetch them, parse them, and store them into convenient bash, perl, and ruby include files that can be easily pulled into scripts. (We don't actually use them in this particular script, we're just in the habit of including this stuff everywhere.)

The script itself moves the MySQL data files onto the LVM volume previously created and sets things up ready for replication.

The next step is to complete the setup by adding more RightScripts from our library to enable monitoring of the MySQL replication, to install S3 and SSH credentials (there are more secure ways to do this than embedding them into RightScripts, but this is the easiest for this demo), to add the Ruby MySQL gem and the RightScale MySQL Manager tools. The resulting config looks like this:

All Boot Scripts

Next come the operational scripts. The cool thing about these scripts is that they can be invoked from the RightScale web site. Here are the four operational RightScripts we need from our library:

Operational Scripts

The four scripts do the following:

  • backup the database to S3 using an LVM snapshot followed by pushing the data files to S3
  • restore the database from S3
  • initialize the server as a slave DB; this is useful after a failover to convert the master into a slave
  • promote from slave to master; this is useful after a failover

Once the ServerTemplate is launched the operational RightScripts are all available as buttons on the server's page. This currently looks like this:

Buttons for Operations RightScripts

These scripts can take parameters. For example, the DB restore script needs an S3 path prefix, so if we hit that button we get a page to enter the missing values, including a drop-down box with a series of values that RightScale already knows about.

Restore Parameters

As soon as the script is launched it shows up in the recent tasks box as queued, and later it will complete:

Restore Task Pending Restore Task Completed

And very conveniently, by clicking onto the task we get an audit entry showing the log file of the script's execution so we can verify that all is well, or troubleshoot if something went awry.

Restore Log File

We now have a MySQL master server up and running and we loaded the initial database data from S3. Now on to setting up a slave server.

Comments

very informative article, btw, if any one wants to manage db, a ready front end tool you can try for sqlyog, it is under open source & is now compatible platforms like Windows version from 98SE to Vista. Runs on Linux with WINE. enjoy!!!
Posted by Nekoda (not verified)   Ι   July 24, 2008   Ι   04:38 AM

Post a comment