Note: Please go to docs.rightscale.com to access the current RightScale documentation set. Also, feel free to Chat with us!
Home > ServerTemplates > Archive > Pre-11H1 > MySQL-S3 > Create a Redundant MySQL Database Setup

Create a Redundant MySQL Database Setup

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.

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.

diag-MySQL_S3-v1.png

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

 

mysql_setup_step0_b.gif

 

Steps

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

mysql_setup_step3.gif

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

mysql_setup_step4.gifNow 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.

screen-ListBothServers-v1.png

 

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.

 

AWS Credentials

Since we want to create backups of our database in S3, the templates require that you include your Amazon keys. For the first two parameters, select cred (credential) as the input type and chose your AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY.

 AWS_ACCESS_KEY_ID  Cred  Select your AWS Access Key ID
 AWS_SECRET_ACCESS_KEY  Cred  Select your Secret Access Key

 

Filename Conventions

The next two parameters tell the Master-DB where to place the backups in S3. When backups are taken, the files will be named with the prefix that you define followed by a timestamp of when they were created so that you can easily verify the most recent backup.  You can define whatever prefix that you want to use.

 BACKUPFILE_PREFIX  Text  production
 BACKUP_S3_BUCKET  Text Enter the name of an existing S3 bucket.  This is where you want the database backups to be stored in S3.  (Recall that as a best practice we recommend using all lower case characters, perhaps including hyphens, rather than underscores.)

User Access

The following four input variables define the admin usernames and passwords for the database and replication setup.

NOTE: You need to make sure that the database users (DBADMIN_USER) are not root. 

 DBADMIN_PASSWORD  Text  Your Password
 DBADMIN_USER  Text  Your User ID(Do not use "root")
 DBREPLICATION_PASSWORD  Text  Your Password
 DBREPLICATION_USER  Text  Your User ID

 

DB Server

The DB_SERVER_USAGE parameter defines which MySQL config that you want to use. Use the dropdown menu to select your instance type (dedicated or shared).  The default is "dedicated."

 DB_SERVER_USAGE  Dropdown Menu text:dedicated

 

DNS

In order to handle the possibility of a failure, we will need to use a DNS record to refer to the Master-DB and Slave-DB instances. Therefore, the server will need to access your DNSMadeEasy username and password.

 DNSMADEEASY_PASSWORD  Cred  Your Password
 DNSMADEEASY_USER  Cred  Your User ID
 EXTERNAL_DNS_ID  Ignore  

 

Slave-DB

To make sure that the Slave-DB automatically initializes itself when it boots, set the INIT_SLAVE_AT_BOOT input parameter to True (default). Set to False if you do not want it to initialize at boot-time.  The only case that you would want to do this is if you don't already have a Master-DB instance running and you want to launch a slave.  For example, if you are starting out with a "clean" install, completely from scratch.

 INIT_SLAVE_AT_BOOT  Text  true

 

Master-DB

Input the DNS-ID and DNS-Name values that you received during your DNSMadeEasy registration.

 MASTER_DB_DNSID  Text  Your DNS ID
 MASTER_DB_DNSNAME  Text  Your DNS Name
 master1.mysite.com

 

Monitoring

At this time you can set MON_PROCESSES to Ignore. In the future, if you want to monitor a specific process. You can set this Input parameter as Text and input the name of the process.  For example, the exact process name as you would see in the Unix process table (ps -ef | more).

 MON_PROCESSES  Ignore  

 

Backup Settings: Slave-DB

OPT_BACKUP_SLAVE_ROTATION_SIZE defines the number of revolving backups for the Slave-DB to take. If you leave this parameter undefined, the default value of 6 will be used.  (Only the last 6 backups will be saved to S3.) The oldest backup is overwritten in order to write the new backup.  By default, snapshots (backups) of a slave database are taken every 10 minutes.  Setting OPT_COMPRESS_SLAVE_BACKUPS to "no" or "false" will disable the compression of these backups which will take some of the CPU load off the slave. Setting it to any other value will enable compression which will decrease the storage size on S3.  Set both of these to Ignore

NOTE: You will only have to go to S3 in the event that you have no database server running or all running databases are corrupted.

 OPT_BACKUP_SLAVE_ROTATION_SIZE  Ignore  
 OPT_BACKUP_SLAVE_BACKUPS  Ignore  

 

 

User Access Settings

OPT_MYSQL_MAX_CONNECTIONS defines the number of maximum MySQL connections that you want to allow to the database at any given time. Once your deployment is operational, you might want to define a limit.  The default value is 500.

 OPT_MYSQL_MAX_CONNECTIONS  Ignore  

 

SSH Key

The Slave-DB will need access to the Master-DB in order to take backups, so it will need the Master-DB's SSH key.

 PRIVATE_SSH_KEY  Dropdown Menu  key: (EC2 US) production

 

Slave DNS-ID

Enter the Slave-DB DNS ID for the SLAVE_DB_DNSID.

 SLAVE_DB_DNSID  Text  The Slave-DB's DNS ID

 

Logging Server

Set the SYSLOG_SERVER variable to syslog.rightscale.com.

 SYSLOG_SERVER  Text  syslog.rightscale.com

 

Be sure to save all of the new Input parameters that we just defined and click Save.

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.

screen-Stranded_Master-v1.png

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.

 

SSH into your Server apply the MySQL-DB and set application permissions

Apply MySQL-DB

Once the instance comes up as "stranded in booting," use the SSH Console to SSH into the Master-DB and manually apply your *.sql dump file. See the sample code below, taking into account the following variable substitutions:

  • S3BUCKET - the name of the S3 bucket that contains the MySQL dump file*
  • DUMPFILE - the MySQL database filename (*.sql)  If you do not have a dump file of your own, you can use our sample_mysql_dump.gz file.
  • DBSCHEMA - the name of the MySQL database
wget http://<S3BUCKET>.s3.amazonaws.com/<DUMPFILE>.gz
gunzip DUMPFILE.gz
mysqladmin -u root create <DBSCHEMA>
nohup mysql -u root <DBSCHEMA> < <DUMPFILE> &

 Hints: 

  • If your database dumpfile isn't large (such as our example DUMPFILE), you don't need to nohup and put the last command in the background (that is, you can omit the "nohup" and the "&").
  • If you are not sure about your bucket name, you can check this in either of the following ways:
    • Look for it in the Dashboard (Manage > Storage > S3 Browser)
    • You can try curl (some are more familiar with using curl than wget).  For example, if your S3 bucket name is "gregdoe", you can verify if the bucket at least exists with the following command (even if you don't have access permissions):
% 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:~]

Note:  In the last example above, the bucket "gregdoe" did not exist.  This is often a typo, and you should confirm that you have the correct bucket name in your curl/wget command line.  You can double check your DUMPFILE URL from the S3 Browser in the Dashboard.  Right click on your DUMPFILE in the S3 Browser and select "Properties".

If you are not sure of the DBSCHEMA name, you can look at your DUMPFILE after getting and unzipping it:

% head <DUMPFILE>

You should see the database name.  For example:  Database: phptestdb

Troubleshooting

* The wget command used above uses a virtual hosted-style request.  This is fine, but if your S3 Bucket name contains upper case letters, you might have difficulties.  This is because of a backwards compatibility issue:  S3 has always been case sensitive, but DNS is not, so there is ambiguity is mapping bucket names to host names.  If you run into this error, you will establish a connection ok, but not find the file.  You will see something similar to this:

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.

There are two easy ways to get around this:

  1. Use lower case letters in your S3 bucket names (no upper case letters)
  2. Change the format of the URL you use with wget to something like this:
wget http://s3.amazonaws.com/<S3BUCKET>/<DUMPFILE>.gz
Set Application Permissions

Once you've applied your MySQL database, you should now set the application password.  This can be done from the SSH console window you already have open for your Master DB instance. You will need to set the application user and password before you take the initial database backup. You only need to do this once because the values will be stored in the database and will be available to the next Master-DB that pulls from S3.

The following script shows how you can permit full read/write permissions to all databases for a particular user. You may want to customize this script depending on your specific needs. Be sure to replace the default "user" and "password" values with your own information.  You will need to change the script permissions in order to execute the script.  For example, if your script name is "setapplperms" you will need to perform steps like the following:

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

Note:  If you are not comfortable with creating and editing a script from a Unix secure shell (using the vi editor for example), you can simply type in or cut/paste the commands below and run them from your SSH command prompt.  In this case, ignore all lines beginning with the comment character "#" and remember to substitute in your "user" and "password" values.  Also note the window below has a horizontal scroll bar, when copying text from this window be sure to get the entire line.

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

screen-RunBackup-v1.png

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.

screen-S3BackupBucket.png

 

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.

screen-S3BackupFile.png

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.

screen-StopMaster-v1.png

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:

mysql_setup_final.gif

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.

 

You must to post a comment.
Last modified
09:03, 31 Jul 2013

Tags

Classifications

This page has no classifications.

Announcements

None


© 2006-2014 RightScale, Inc. All rights reserved.
RightScale is a registered trademark of RightScale, Inc. All other products and services may be trademarks or servicemarks of their respective owners.