Objective

Take an existing MySQL database and port it over onto an EC2 instance and set up a redundant master-slave database with constant backups to S3.

Prerequisite

This tutorial only applies to Premium accounts.  If you have a Developer account and would like to upgrade, please contact sales@rightscale.com.

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

Overview

The RightScale Dashboard makes it easy to create a redundant MySQL database on EC2 with complete failover and recovery.  Learn more about our MySQL redundancy from our blog post on our RightScale Blog.

To see how easy it is to build a redundant MySQL database on the cloud, follow the tutorial below.

Steps

Step 1 - Add "Master" MySQL database to the deployment

The first step is to add the Master and Slave databases as components to the Production deployment.

Go to Manage -> Deployments.

Select the Production deployment that you already created earlier in the Create a Production Deployment tutorial.

Under the Production deployment's Servers tab, add a server for the Master-DB. Select the 'EC2-US' cloud region and click the Add Server button.

Select the most recent MySQL Bootstrap ServerTemplate from the RightScale list and name it MySQL Master.

Select the production SSH key and production Security Group.  You can assign additional security groups, if necessary. 

Keep the default selections for Availability Zone and Elastic IP and click Add.

WARNING!  You should use the same availability zone for all servers in your deployment, otherwise you will pay additional cross-zone traffic costs and see a decrease in your site's performance.   

Step 2 - Add "Slave" MySQL database to the deployment

Now that the Master-DB has been added to the Production deployment, add the Slave-DB.

Click the Add EC2 Server action button.

This time select the most recent MySql Additional ServerTemplate, and name it MySQL Slave.  Be sure to select the production SSH key and production Security Group.  Keep the default selections for Availability Zone and Elastic IP and click Add. 

The Master-DB and Slave-DB are now components of the Production deployment.

Step 3 - Define Input parameters

Now we need to configure the Input parameters for the Production deployment.  Note this is not a difficult process, but must be completed with an eye for detail, particularly for newer users.

Select the Production deployment's Inputs tab. We will now configure the Input parameters for the Master-DB and Slave-DB. Click Edit.

There are five different types of inputs:

• Ignore – ignore this variable
• Env – environment variable
• Text – plain text
• Cred – credential
• Key – SSH key

"Warning! Input contains leading/trailing whitespace."
As a best practice, no Input value should have any extra spaces before or after the value.  If one exists, you might see a warning message in red suggesting that you delete any extra spaces.

Step 4 - Launch "Master-DB"

Now we are ready to launch the Master-DB. Click the Servers tab.  Press the green play button that corresponds to the MySQL Bootstrap instance.  Verify that the input values are correct, and click Launch. (If you forgot to provide information for any required Inputs, they will be highlighted on the next screen in red. Before you can launch the instance, all required Inputs must have acceptable values.)

It should take approximately 5 minutes for the instance to boot. You should see the instance pending in the Events pane on the left hand side of the browser.

We expect the server to come up in a "stranded in booting" state.  You can track the status under the Events pane.

In order to take the fastest and most efficient backups possible, backups are taken as Logical Volume Management (LVM) snapshot. The Bootstrap template is designed to get the most recent backup from S3 upon launch. Since this is the first time we are launching the Master-DB, the template will not find such a backup in S3, so the startup process will fail. But this is expected and OK.  In the next step we will add a backup of the Master-DB to S3.

wget http://<S3BUCKET>.s3.amazonaws.com/<DUMPFILE>.gz
gunzip DUMPFILE.gz
mysqladmin -u root create <DBSCHEMA>
nohup mysql -u root <DBSCHEMA> < <DUMPFILE> &

% curl http://s3.amazonaws.com/gregdoe
<?xml version="1.0" encoding="UTF-8"?>
<Error><Code>AccessDenied</Code><Message>Access Denied</Message><RequestId> (more output here...)

# Or in the example of a typo, or the bucket really does not exist...
% curl http://s3.amazonaws.com/gregdoe
<?xml version="1.0" encoding="UTF-8"?>
<Error><Code>NoSuchBucket</Code><Message>The specified bucket does not exist</Message><BucketName>gregdoe</BucketName><RequestId>CC5CE96B2A7B47A5</RequestId><HostId>qsm8vEL7/14LavaSDKjeX2Gj0affV7gmr/hWAqWxwAFigu9EKnWHmlaHNK8p15dS</HostId></Error>[root@domU-12-31-39-00-7D-F4:~]

% head <DUMPFILE>

wget http://GregDoe-Bups.s3.amazonaws.com/sample_mysql_dump.gz
--15:52:01-- http://gregdoe-bups.s3.amazonaws.com/sample_mysql_dump.gz
Resolving gregdoe-bups.s3.amazonaws.com... 72.21.211.247
Connecting to gregdoe-bups.s3.amazonaws.com|72.21.211.247|:80... connected.
HTTP request sent, awaiting response... 404 Not Found 15:52:01 ERROR 404: Not Found.

wget http://s3.amazonaws.com/<S3BUCKET>/<DUMPFILE>.gz

vi setapplperms
# type or cut/paste the bash script contents from the window below, and save your changes.
chmod 755 setapplperms
# when ready, run the script...
setapplperms

#!/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 5 - Create a backup of "Master-DB" on S3

The next step is to create a backup of the Master-DB to S3 for the first time.

Go to the running instance page (Shortcut: Click the RightScale logo).
Click on MySQL Master under the Production deployment. Under the server's Scripts tab execute the "DB backup" RightScript by clicking the Run action icon.

You will see this task pending in the Events pane. It will take a few minutes to complete depending on the size of the database.

When it is "done," navigate to the S3 bucket where you put the backups and verify that a backup was saved. Go to the S3 Browser (Clouds -> AWS Global -> S3 Browser).

Click on the bucket that you created in the Create an S3 Bucket step.

You should now see a backup file of your Master-DB. Notice that the filename has the "production" prefix that you specified in the Filename Conventions inputs section along with a timestamp.

Step 6 - "What happens when the Master-DB is terminated?"

Your setup is almost complete. Now that there is a backup of the Master-DB in your S3 bucket, let's relaunch the Master-DB instance. This time it will no longer become stuck in the "stranded in booting" state because the template will now find a backup of the Master-DB database in your S3 bucket. But, since our Master-DB instance is currently running, we'll want to stop/terminate it. Click the Stop button for the MySQL Master instance.

Once the instance has been terminated, launch the Master again (You can also use the Relaunch button from the server's home page).  If everything is properly configured, it will reach the "operational" state. 

After the Master is up, click the Launch button for the MySQL Slave instance.

Once the MySQL Slave instance is operational it will begin taking regular backups of the Master-DB and save these backups to your S3 bucket in 10 minute intervals.  Your Production Deployment now looks similar to the following:

Congratulations! You just set up a redundant MySQL database on EC2 that is saving regular backups on S3. Now that your servers are up, use the  Manager for MySQL-S3 Runbook as a user guide for managing your MySQL setup.