Create a MySQL-EBS Database Setup

 
Objective

To create a redundant MySQL database setup that uses EBS snapshots as backups instead of saving .tar files to S3.  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. 

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.

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.

Step 1: Create a Deployment

Go to Manage -> Deployments and select the "New" button to create a new deployment and call it "MySQL EBS."
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" template, it will look for the most recent EBS snapshot to begin the boot process.  Since you are creating this 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.

Click the Add (EC2) Server button to add a server to the deployment.  

Select the RightScale: "MySQL EBS" ServerTemplate.  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.

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

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." 

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.

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 -> 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)
• 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

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

Select the new volume that you just created (ex: mydb).  Attach the new EBS volume to the server's /dev/sdk device.  Specify that you want to "Attach now" since you already have a running server.  Click OK.

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 Recent 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 -k" 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

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

NOTE:  You can run a "df -k" 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.sql

#!/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

Next, we will 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 -> 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.

Now that you have a snapshot, you can shutdown the "ebs-db1" server.  Click the Terminate button.  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 our new Master-DB server.  Once it is stopped, click the launch button to relaunch the "ebs-db1" server, except this time, there will be a snapshot. 

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 master restore" operational script.  This script will make the server a Master-DB.  

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

Go to Clouds -> AWS -> 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.  Click the Add (EC2) Server button.  Use the same "MySQL EBS" ServerTemplate, but provide a different nickname for the new server (ex: ebs-db2).  Click Add.

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

You can also customize the frequency of how often backup snapshots are taken of your master/slave databases.  Under the Inputs tab of the "MySQL EBS" ServerTemplate, you can modify the following parameters:

NOTE: In order to modify these input parameters, you must first clone the existing template.  The default settings are shown in the screenshot below.  Master (at 0 minute, every 4 hours);  Slave (at 0 minute, every hour)

• 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: 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