Objective

To create a redundant MySQL database setup that uses EBS snapshots on S3 as backups instead of saving tar files to S3. 

Prerequisites

All of the steps that are part of the Deployment Setup.

This tutorial assumes that you are building the setup from scratch with a MySQL dump of your database.  If you already have a setup that uses the "MySQL Bootstrap" and "MySQL Additional" ServerTemplates, you will need to see the MySQL Database Migration: S3 to EBS tutorial. 

Overview

The RightScale Dashboard makes it easy to create a redundant MySQL database on EC2 with complete failover and recovery.  The Master and Slave databases will reside on EBS, with backup snapshots to S3 for peristance.

EBS volumes and snapshots are EC2 region-specific.  You cannot use a volume/snapshot that you created in EC2-US in a different region like EC2-EU.  This tutorial assumes you are creating an EBS setup for EC2-US.

To learn more about Elastic Block Store and why you should use it for MySQL databases, see Overview of MySQL EBS Setups. You can also refer to the Elastic Block Store (EBS) section to learn more generic information about EBS.

The tutorial below uses a single ServerTemplate ("MySQL EBS") to build both the master and slave database servers.  See the "MySQL EBS" ServerTemplate Flowchart to get an understanding of how you can use the ServerTemplate and its RightScripts to create both types of servers.

NOTE:  Be sure to lower the TTL setting for your DNS entries for your Master/Slave databases.  The Master/Slave switch cannot take effect until the Master database's TTL has expired.

Steps

Step 1: Create a Deployment

Note: Some may have already created a Deployment as part of the "Deployment Setup" prerequisite steps.  If so, please skip to the next step.

Go to Manage -> Deployments and select the "New" button to create a new deployment and call it "MySQL EBS."  Provide a brief description and select a preferred availability zone.  Later, when you add servers to a deployment you can define a specific availability zone.  The availability zone that is defined at the deployment level will only take effect if you add a server with '-any-' as its availability zone setting.  For this example, select "us-east-1a" and click Save.

Step 2: Launch a Server to create an EBS Snapshot

The "MySQL EBS" ServerTemplate is based off of EBS snapshots.  Snapshots are highly efficient backups representing an EBS volume at any point in time.  New EBS volumes are created from a snapshot and are attached to a master/slave instance.  When you launch a new server using the "MySQL EBS" ServerTemplate, it will look for the most recent EBS snapshot to begin the boot process. 

Since you are creating this EBS setup from scratch, you do not have a snapshot of the database.  Therefore, the first step is to create your first EBS snapshot of the database.  You will need to create a volume, attach it to a server, populate the database, take a snapshot, and then terminate the instance.  By default, a decommission script will delete the volume when the instance is terminated.  When dealing with EBS volumes, you should always attach a fresh volume that was created from the most recent snapshot. 

Go to Design -> ServerTemplates -> View Library -> RightScale tab and import the "MySQL EBS" ServerTemplate to your 'Local' view.  You should also import the "MySQL S3/EBS Toolbox" ServerTemplate.  It is a 'Toolbox' ServerTemplate that doesn't actually launch a server.  Instead it contains several useful RightScripts that you will most likely need to use in the future to perform various tasks.   You will need to import it so that you can either add its RightScripts to a ServerTemplate or execute one as an 'Any Script' operation.  Later in the tutorial, you will need to use one of its RightScripts ("DB register master").

From the "MySQL EBS" deployment's Servers tab, select the 'EC2 US' cloud region and click the Add Server button to add a server to the deployment.  

Select either the imported version.  Provide a nickname (ex: ebs-db1) and select your SSH Key and Security Group.  Keep the default availability zone (ex: us-east-1a).  (Note:  When you create an EBS volume later in this tutorial, you will need to create the volume in the same availability zone as the server that you will attach it to.) Click Add.

Note:  The "MySQL EBS" ServerTemplate is designed for m1.small instance types.  If you want to use a large (m1.large) instance type, you will need to use a 64bit image (ex: "RightImage CentOS_5.2_x64_v4.2.4")

Next, we will define some common Input parameters.  By defining Input parameters at the Deployment level, all servers will inherit these Inputs and their values, so we will only have to define them once.  At the Deployment level, click the Inputs tab and configure the following Input parameters:

 * The username and password Inputs shown here are labeled as "Text".  You are of course free to label them "text" and type in the exact string required.  Alternatively, they can be set up in your Credential store.  When doing so, you simply select "Cred" and pick the appropriate set of credentials from the drop down menu provided.  Some consider seting up their Credentials first, and then referring to those credentials here when building out your Deployment to be a best practice. 

When ready, select Save to save your Inputs.

Go back to the Servers tab and launch the database server. 

When the input confirmation screen appears, provide the following parameters.  Since you don't have a Master-DB for the server to synchronize with, we need to make sure that the server does not try to initialize at boot time.  You must set the value for the INIT_SLAVE_AT_BOOT input parameter to "False." 

Click the Launch action button to continue launching your database server instance.

In a few minutes, you will have a new server, but it has an empty database.  Be sure to wait for the new server to boot-up and enter the "operational" state before continuing to the next step.  Under the CPU column, a real-time graph will be drawn of the instance's CPU usage. Click on it to view a larger graph of the thumbnail.

The next step is to populate the database with data on the "ebs-db1" server.  This tutorial assumes that you already have a MySQL dump of your database.

Step 3: Create an EBS Volume

Go to Clouds -> AWS US -> EBS Volumes.  Click New. 

• Nickname - The nickname that you give the volume must match the name that you used for DB_EBS_PREFIX (ex: ebs-db1) so that the "MySQL EBS" ServerTemplate will know which snapshot to use for launching new servers. 
• Zone - Be sure to create the volume in the same availability zone as the "ebs-db1" server.  (ex: us-east-1a)  You cannot attach an EBS volume to a server running in a different availability zone.
• Size in GB - You must also specify the appropriate size for the volume.  Remember, it needs to be large enough to hold your entire database and also accomodate for the volume's expected growth over time. 

NOTE: The time required to complete an initial backup and restore is proportional to the volume's size. If you set a large volume size, it will affect the time required to perform the first backup and any subsequent restores.

Click Create.

Step 4: Attach the EBS Volume

Now that you have created an EBS Volume, you need to attach it to a server in your deployment.

Go to the Volume tab of the "ebs-db1" server.  Click the Attach Volume link.

• Server - Select the new volume that you just created (ex: mydb).   The format for the drop down is:  DeploymentName/ServerName
• Devices - Attach the new EBS volume to the server's /dev/sdk device. 
• Attachment Options - Specify that you want to "Attach now" since you already have a running server. 

Click OK when ready.

Now go to the Volumes tab of your deployment and wait for the volume to be fully attached.  You can track the progress in the Events pane or you can occasionally refresh the screen.

Step 5: Create the Initial Database

Remember, EBS is a block level device.  Although you've created and attached the volume to a server, you still need to mount and format the device.  The next step is to prepare the volumes and create the initial database.  Click the SSH Console button to ssh into the "ebs-db1" server.

NOTE:  You can run a "df -h" command and it will show that the new EBS Volume has not been mounted or formatted.

Format the Volume

mkfs.xfs /dev/sdk

Stop the MySQL daemon

service mysqld stop   # Stop mysql on CentOS. Use "service mysql stop" for Ubuntu

Copy and Mount MySQL
Copy MySQL from the LVM disk to the EBS volume and mount the EBS over the LVM (use the /mnt/mysql device).  NOTE: MySQL was attached on the LVM before the EBS volume was attached to the server.

cd /mnt                       # Change to the /mnt directory
mv /mnt/mysql /mnt/mysql.bak  # Move the mysql directory to a "backup" version
mkdir /mnt/mysql              # Create the new mysql directory
mount /dev/sdk /mnt/mysql -o noatime       # Mount the volume, with the new mysql as the new mount point
chown mysql:mysql mysql                    # Set the ownership and group
cd /mnt/mysql.bak                          # Change to the old (backup) copy of mysql
tar cf - * | ( cd /mnt/mysql; tar xfp -)   # Tar the oldmysql, and untar it in the new mysql directory (EBS volume)

Restart the MySQL daemon

service mysqld start    # Restart mysql on CentOS. Use "service mysql start" for Ubuntu

NOTE:  You can run a "df -h" command and it will show the new EBS Volume, formatted, mounted and available for use.

wget http://s3bucket.s3.amazonaws.com/DUMPFILE.gz
gunzip DUMPFILE.gz
mysqladmin -u root create DBSCHEMA
mysql -u root DBSCHEMA < DUMPFILE

#!/bin/bash -e
# Copyright (c) 2007 by RightScale Inc., all rights reserved

#Set your Application username and password here
DBAPPLICATION_USER=user
DBAPPLICATION_PASSWORD=password

# Enable remote administration
echo 'GRANT ALL PRIVILEGES on *.* TO '\'$DBAPPLICATION_USER\''@'\''%'\'' IDENTIFIED BY '\'$DBAPPLICATION_PASSWORD\'' ;' | mysql -u root
echo 'GRANT ALL PRIVILEGES on *.* TO '\'$DBAPPLICATION_USER\''@'\''localhost'\'' IDENTIFIED BY '\'$DBAPPLICATION_PASSWORD\'' ;' | mysql -u root
echo 'REVOKE SUPER on *.* FROM '\'$DBAPPLICATION_USER\''@'\''%'\'' IDENTIFIED BY '\'$DBAPPLICATION_PASSWORD\'' ;' | mysql -u root
echo 'REVOKE SUPER on *.* FROM '\'$DBAPPLICATION_USER\''@'\''localhost'\'' IDENTIFIED BY '\'$DBAPPLICATION_PASSWORD\'' ;' | mysql -u root
echo "FLUSH PRIVILEGES;" | mysql -u root

Step 6: Create an EBS Snapshot

In order for the automatic backup script to function properly, you first need to designate the running instance as the "master" database server. 

Go the Scripts tab of the running server.  Under the Any Script section, select and run the "DB register master" script from the list of non-private RightScripts.  (If you do not see that RightScript in the list, you will need to import the "MySQL S3/EBS Toolbox" ServerTemplate from the library.)  When the Inputs confirmation window appears, check the override box for "DB_TEST_MASTER_DNSID" and enter your DNS ID and run the script.  

Next, manually create a snapshot (backup) of the EBS volume (ex: "mydb").  Under the Scripts tab of "ebs-db1," run the "DB EBS backup" operational script. 

Go to Clouds -> AWS US -> EBS Snapshots.  You must wait for the snapshot to be 100% complete before continuing the tutorial.

Note: The time required to complete the initial EBS snapshot can take a long time (over an hour) depending on the size of the EBS volume.

You now have a snapshot of the database that you can use to launch a fresh, new database server using the "MySQL EBS" ServerTemplate.  Remember, the purpose of the first EBS volume was to create your first EBS snapshot.  Now that you have a snapshot, the volume is no longer needed.

You can now safely shutdown the "ebs-db1" server.  

When you terminate the instance, the "DB Delete EBS volume on halt" decommission script will delete the EBS volume that you just created.  As a best practice, the MySQL EBS ServerTemplates are designed to delete volumes from instances for two reasons:

1. You always want to attach fresh volumes that were created from the most recent snapshot.
2. If volumes were not deleted, it would result in many "stranded" volumes.  Obviously, this could get very unmanageable and confusing. 

RightScale's MySQL EBS ServerTemplates are designed to create volumes from the most recent snapshot and attach them to new instances.  You should never have to use a stranded volume, because once you have a snapshot, you should either be restoring from a recent (backup) snapshot or promoting the existing Slave-DB to Master-DB.

NOTE:  If you do not want the EBS volume to be deleted when the instance is terminated, then you must clone the MySQL EBS ServerTemplate and remove the "DB Delete EBS volume on halt" decommission script.

Click the Terminate button to shutdown the "ebs-db1" server.  Wait for the instance's state to be "stopped" before continuing to the next step.

Step 7: Launch a Master-DB Server

You are now ready to launch your new Master-DB server.  Once it is stopped (from the previous steps terminate action), click the launch action icon to relaunch the "ebs-db1" server.  This time when it launches, there will be a snapshot to pull data from. 

Once again, be sure to set INIT_SLAVE_AT_BOOT to "False" since we still don't have a running Master-DB server.

When the server reaches the "operational" state, go to the Scripts tab and run the "DB EBS restore and become master v1" operational script.  This script will make the server a Master-DB.  

By default, when the "DB EBS restore and become master v1" script is 100% complete, another snapshot (backup) will be taken of the volume.

Go to Clouds -> AWS US -> EBS Snapshots.  You must now wait for the snapshot to be 100% complete before you launch the Slave-DB server.  Notice that this time, the snapshot will be listed as a "master" snapshot  (ex: mydb-master-yyyymmddttmm).  

Step 8: Launch a Slave-DB Server

Once the master snapshot is 100% complete, you are ready to launch your Slave-DB server.  Add a second server to the deployment.  Select 'EC2 US' cloud region and click the Add Server button.  Use the same "MySQL EBS" ServerTemplate, but provide a different nickname for the new server (ex: ebs-db2).  Click Add.

Note: Creating a Slave-DB server is optional.  Although you can create a MySQL EBS setup with only a Master-DB, it's strongly discouraged for production environments, where you will need database replication for failover and recovery.

If the snapshot is 100% complete, you are now ready to launch the Slave-DB server (ebs-db2).  Click the Launch button.

This time the template will find the most recent snapshot with the appropriate DB_EBS_PREFIX (ex: mydb).  The snapshot will have a matching prefix (ex: mydb-master-yyyymmddttmm).

Set the INIT_SLAVE_AT_BOOT Input parameter to "True" since you now have a Master-DB server running.

Congratulations!  In a few minutes you, when the server enters the "operational" state you will have a master/slave database using EBS.

Step 9: (Optional) Modify Backup Settings

Below are the default settings for the "MySQL EBS" ServerTemplate.

• DB_BACKUP_KEEP_LAST - Defines the number of snapshots that will be saved before the oldest snapshot is removed.  
  • Default: 60  (60 snapshots of the master, 60 snapshots of the slave)

If you want to change the frequency of the snapshots, you will need to clone, modify and replace the current "DB EBS continuous backups" RightScript.   Modify the following frequency inputs accordingly.   The time of the first snapshot is randomized in order to prevent every EBS snapshot that's managed by RightScale from being taken at the same time.  All subsequent snapshots will be saved at the same minute of the hour. (i.e. 4:35, 8:35, 12:35, etc.)

• DB_BACKUP_MASTER_FREQ - Defines the frequency of snapshots of the Master-DB.  The backup will be executed when these values match the current time.  The cron string format consist of 5 space-separated values.  Look up the crontab format for correct syntax.
  • minute(0-59) hour(0-23) month_day(1-31) month(1-12) week_day(0-7), where * is a wildcard
  • Default: once every 4 hours
     
• DB_BACKUP_SLAVE_FREQ - Defines the frequency of snapshots of the Slave-DB.  The backup will be executed when these values match the current time.  The cron string format consist of 5 space-separated values:
  
  • minute(0-59) hour(0-23) month_day(1-31) month(1-12) week_day(0-7), where * is a wildcard
  • Default: hourly