Create a Redundant MySQL Database Setup

Objective

The purpose of the MySQL Setup tutorial is to show you how to take an existing MySQL database and port it over onto an EC2 instance and set up a redundant master-slave database with constant backups being to S3.

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

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.

This tutorial is divided into 6 Steps:
Step 1: Add "Master" MySQL database to the deployment
Step 2: Add "Slave" MySQL database to the deployment
Step 3: Define Input parameters
Step 4: Launch "Master-DB"
Step 5: Create a backup of "Master-DB" on S3
Step 6: "What happens when the Master-DB is terminated?"

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 -> Servers -> Deployments.

Select the Production deployment that you already created.

Next, we need to add an instance on EC2 for the Master-DB. Click the Add Server button.

Select the most recent MySql Bootstrap server template from the RightScale list and name it MySQL Master. Select the production SSH key and production Security Group. Keep the default selections for Availability Zone and Elastic IP and click Add.

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 Server button.

This time select the most recent MySql Additional template, 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.

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

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 between 5 and 10 minutes for the instance to boot. You should see the instance pending in the Recent Events list 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 Recent Events.

In order to take the fastest and most efficient backups possible, backups are taken as Logical Volume Management (LVM) snapshots. 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
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 "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.
Click the Scripts tab.

Execute the "DB backup" operational action by clicking the Run button.

You will see this task pending in the Recent Events list. 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 (Manage -> Servers -> Storage).

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

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

Now you can go back to the deployment and terminate the Master-DB MySQL instance and relaunch. 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.

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 Runbook as a user guide for managing your MySQL setup.

 ----------------------

Did you find this document helpful?  Please feel free to leave us a comment below.  We're always looking for ways  to improve our documentation.  Thanks!