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-EBS Stripe > MySQL-EBS v2 Database Setup

MySQL-EBS v2 Database Setup

IMPORTANT NOTE: The ServerTemplate documentation listed on this page has been deprecated. Version v13.x and earlier ServerTemplates have reached End of Support. Please visit our Version 14 ServerTemplates page for the most recent ServerTemplate documentation.

Objective

To create a redundant MySQL (v5.0 or v5.1) database setup that uses EBS snapshots on S3 as backups.  The v2 ServerTemplates support the option of using a striped set of EBS volumes for the database.  You can either use this tutorial to set up a blank MySQL database or use a dump of an existing MySQL database.

Table of Contents

Prerequisites

Completion of all steps in the Deployment Setup section.

This tutorial also assumes that you are building the setup from scratch with a MySQL dump of your database.  You will also have the option of creating a blank MySQL database if a MySQL dump file is not available.  In MySQL, you must not define a password for 'root'@'localhost' otherwise our RightScripts will fail.

Warning!

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 being saved to S3 for persistence.

EBS volumes and snapshots are AWS region-specific.  You cannot use a volume/snapshot that you created in 'us-east' in a different region like 'us-west'.  This tutorial assumes you are creating an EBS setup for 'us-east'.

To learn more about Elastic Block Store (EBS) 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 to build both the master and slave MySQL 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.

Warning: 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.  Be sure to set up the TTL value, for the mysql hostname to be less than or equal to 120 seconds. Otherwise, the server will strand at the first script ("DB check master DNS TTL").

diag-EBSStripe-v1.png

Steps

Create a Deployment (if needed)

Note: Some may have already created a Deployment.  If so, please skip to the next step.

Go to Manage -> Deployments and select the "New" action button to create a new Deployment and call it "MySQL EBS v2".  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.  Make a selection and click Save.

screen-NewDeployment-v2.png

Add Servers to the Deployment and Define Inputs

The "MySQL EBS" ServerTemplates are based off of EBS Snapshots.  Snapshots are highly efficient backups representing an EBS volume at a specific 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 (or snapshots if you're using a set of striped EBS volumes) that's available to begin the boot process. 

Since you are creating this EBS setup from scratch, you will not have a snapshot of the database.  Therefore, the first step is to create your first EBS snapshot of the database.  When dealing with EBS volumes, you should always attach a fresh volume that was created from the most recent snapshot. 

Go to the MultiCloud Marketplace (Design -> MultiCloud Marketplace) and import the following components:

  • ServerTemplates: "MySQL 5.1 EBS v2"  or "MySQL 5.0 EBS v2"
    • Note: The 3 Tier Deployment tutorial has been tested with "MySQL 5.0 EBS v2".  Please select MySQL 5.0 if completing this tutorial as part of an instructor led training course.
  • RightScripts: "DB Create MySQL EBS stripe volume v2" and "DB register master"  (You can also simply import the "MySQL EBS Stripe Toolbox v2" ServerTemplate to gain access to all of these scripts.

Note:  If you plan to make modifications to the ServerTemplate or RightScripts, you should clone them to create editable copies.

From the Deployment's Servers tab, select the cloud region where you want to create your database setup (e.g. 'AWS US-East') and click the Add Server button.  

Select either the imported or cloned version of the EBS v2 ServerTemplate.  Provide a nickname (ex: ebs-db1) and select your SSH Key and Security Group. 

Note: Since the v1 and v2 versions of the MySQL ServerTemplates use v4 RightImages, the selected EC2 Security Group must have port 22 open to 'any' so that operational RightScripts can be executed successfully.

For more information on the Add Server assistant, see Add Server Assistant.

Keep the default Availability Zone (ex: us-east-1a).  Click Add.

Add another Server the same way and call it "ebs-db2" so that you now have two database Servers in your Deployment.  Be sure to add both Servers to the same cloud or AWS region.

Note: Creating a second (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.

screen-TwoInactiveServers-v1.png

Next, as a best practice we will define some common Input parameters at the Deployment level.  Go to the Deployment's Inputs tab.  By defining Input parameters at the Deployment level, all Servers in the Deployment will inherit these Inputs and their values, so we will only have to define them once.  Notice that most of the Input parameters can be inherited from the ServerTemplate.  However, there are a few required inputs that need to be defined, as well as a few other Inputs whose values you might want to change. 

Select the Inputs tab and click the Edit action button to configure the following Input parameters. 


Required

Name Input Type Description
 DB_LINEAGE_NAME Text The DB_LINEAGE_NAME will be used to locate the appropriate EBS Snapshots in order to created the striped volume set.  It will also be used create a common name for each backup snapshot.  (ex: mystripe)
 EXTERNAL_DNS_ID Text The public DNS ID of the server.  (Optional)  This value is used by an operational script when you want to associate the public IP address of the server with a public DNS ID.
If you do not have an additional DNS record to update for the server, you can set this to 'Ignore'.  See also Domain Setup with DNSMadeEasy.
 MASTER_DB_DNSID Text The Master's DDNS ID  (ex: 1234567)  See Domain Setup with DNSMadeEasy.
 MASTER_DB_DNSNAME Text Your DNS Name (ex: master1.mysite.com)  See Domain Setup with DNSMadeEasy.
 PRIVATE_SSH_KEY Key Select the same SSH Key that you selected when you added the server.  This key will be used for server-to-server communication.  Make sure the selected key has key material.
 SERVER_UUID* Dropdown The server's universal unique identifier, which is assigned by RightScale to ensure that each server is unique across all clouds.  Always select "env:RS_INSTANCE_UUID"
 SLAVE_DB_DNSID Text The Slave's DDNS ID  (ex: 1234512346)  See Domain Setup with DNSMadeEasy.

 * Only applies to v4 ServerTemplates.

When ready, select Save to save your Inputs.

Launch Database Server

Go back to the Deployment's Servers tab and launch the first database Server (e.g. ebs-db1).  This server will soon become your Master-DB Server.  Click the launch action icon.

screen-LaunchMaster-v1.png

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

 INIT_SLAVE_AT_BOOT Text Set to "False"

 

Click the Launch action button to continue launching your database Server.

In a few minutes, you will have a new Server instance with MySQL installed, but it has an empty database.  Be sure to wait for the Server to boot-up and enter the "operational" state before continuing to the next step (~5-10 min). 

Set up MySQL Database

The next step is to create and attach an EBS volume or stripe of EBS volumes to the running instance where your MySQL data will be stored.  Click on the running Server's Scripts tab.  Under the 'Any Script' option, run the "DB Create MySQL EBS stripe volume v2" RightScript, which will create and attach an EBS stripe designed for a MySQL Server.  Note: If you do not see this RightScript in the list, you will need to import it from the MultiCloud Marketplace (Design -> MultiCloud Marketplace -> RightScripts).

By default, this RightScript will create a blank MySQL database with no striping (EBS _STRIPE_COUNT = 1) that includes all the necessary privileges for replication, administration, and application purposes.  However, if you have an existing MySQL database that you want to use instead, you will need to define the "optional" parameters below.

Be sure to select the latest revision of the script that's available.

screen-RunAnyScriptCreateStripe-v2.png

You will need to provide the following Input parameters before you can run the RightScript.

Required Inputs

Name Input Type
Description
 AWS_ACCESS_KEY_ID Text/Cred The AWS Access Key ID
 AWS_SECRET_ACCESS_KEY Text/Cred The AWS Secret Access Key
 DBADMIN_PASSWORD Text/Cred The password that 'admin' users use to access the MySQL database.
 DBADMIN_USER Text/Cred The username that 'admin' users use to access the MySQL database.
 DBAPPLICATION_PASSWORD Text/Cred Password that application servers will use for restricted access to the MySQL database.
 DBAPPLICATION_USER Text/Cred Username that application servers will use for restricted access to the MySQL database.
 DBREPLICATION_PASSWORD Text/Cred The password that's used for replication between the master and slave database.
 DBREPLICATION_USER Text/Cred The username that's used for replication between the master and slave database.
 DB_LINEAGE_NAME Text The DB_LINEAGE_NAME will be used to locate the appropriate EBS Snapshots in order to create the striped volume set.  It will also be used to create a common name for each backup snapshot.  (ex: mystripe)
DB_MYSQLDUMP_BUCKET Text

The S3 bucket where an existing MySQL database dump file is stored.

Click the 'Override' checkbox, select 'Text' and enter the name of the S3 bucket.

If you are using an existing MySQL database, see the "Optional Inputs" table below for specific instructions.  Steps will vary depending on the size of the database.

If you do not have a mysql dump file and want to create a blank MySQL volume stripe, set this input to 'ignore'.

DB_MYSQLDUMP_FILENAME Text

The filename of an existing MySQL database (located in the DB_MYSQLDUMP_BUCKET) that will be used when the EBS stripe is created.

Click the 'Override' checkbox, select 'Text' and enter the name of the mysql dump file.  You will need to specify a full filename. Ex: mydb-200910302212.gz

If you are using an existing MySQL database, see the "Optional Inputs" table below for specific instructions.  Steps will vary depending on the size of the database.

If you do not have a mysql dump file and want to create a blank MySQL volume stripe, set this input to 'ignore'.

DB_SCHEMA_NAME Text

The name of the database schema is set when you import the dump file into MySQL.  Example: my_db_schema

(Note:  It's only defined within the MySQL instance and not within the actual dumpfile.  Hence, the name is somewhat arbitrary but should be descriptive.)

Important!  If you've set this input at the Deployment level, be sure to use the same value.

 EBS_MOUNT_POINT Text The path where the EBS volume will be mounted.  Since you are creating a new MySQL database from scratch, you should select the 'text: /mnt/mysql' option otherwise the default value of '/mnt/ebs' will be used. 
 EBS_STRIPE_COUNT Text Defines the total number of EBS volumes that will be used by the database.  EBS volumes will be created and mounted to the instance.  By default, a value of '1' will be used, which means only a single volume will be used (no striping).

Select the number of EBS volumes to create in the EBS stripe.  (1,2,3, etc.)
For example, for a value of '3', three EBS Volumes will be created and attached to the server instance.
 EBS_TOTAL_VOLUME_GROUP_SIZE_GB Text

Defines the total size of the striped EBS volume set (in GB).  For example, a value of '3' and a stripe count of '3' will create an EBS stripe that contains 3 EBS volumes that are each 1GB in size.

 

Optional Inputs - (For using an existing MySQL database)

If you already have a MySQL database, create a dump of the database and compress it with a gzip.  Upload it to one of your S3 buckets.  Simply provide the name of the S3 bucket where the compressed dump file is stored and its filename. 

MySQL Databases less than 4GB

If you are migrating a database that's less than 4GB in size, specify the optional inputs below.

Name Input Type Description
 AWS_ACCESS_KEY_ID Text/Cred The AWS Access Key ID.  Used to download the dump file from S3.
 AWS_SECRET_ACCESS_KEY Text/Cred The AWS Secret Access Key.  Used to download the dump file from S3.
 DB_MYSQLDUMP_BUCKET Text The S3 bucket where an existing MySQL database dump file is stored.  For example:  jd3tierdeployment
 DB_MYSQLDUMP_FILENAME Text The filename of an existing gzip'ed MySQL dumpfile (located in the DB_MYSQLDUMP_BUCKET) that will be used when the EBS stripe is created. You will need to specify a full filename.  For example, for the 3 tiered Deployment tutorial use:  myapp_prod_dump-200804161345.gz
 DB_SCHEMA_NAME Text

The name of the database schema is set when you import the dump file into MySQL.  Example: my_db_schema  (Note:  Only defined within the MySQL instance, not within the dumpfile.  Hence the name is somewhat arbitrary but should be descriptive.)

 

MySQL Databases more than 4GB

If you are migrating a larger database (larger than 4GB), you will not be able to perform the migration by simply running a RightScript.  Since RightScripts only have 15 minutes to complete execution, an attempt to migrate a large database will result in a timeout error.  Therefore, you will need to manually bring the data into the Server and populate the database.  Be sure to set the above optional inputs to 'ignore' before running the script.

Use the Server's SSH Console to SSH into the instance and manually apply your MySQL dump file. See the sample commands below to retrieve your database dumpfile.

  • S3BUCKET- the name of the S3 bucket that contains the compressed MySQL dump file  (*.gz)
  • DUMPFILE - the MySQL database filename (*.sql)
  • DBSCHEMA - the name of the MySQL database
wget http://S3BUCKET.s3.amazonaws.com/DUMPFILE.gz

gunzip DUMPFILE.gz

mysqladmin -u root create DBSCHEMA

mysql -u root DBSCHEMA < DUMPFILE

Tips: 

  • Your dump file must be publicly readable in order to download it to your EBS volume via the wget command above.  If you get "Permission denied" messages, browse to your S3 bucket and change the permissions to "pub read" for the download.  You can change them back to "private" once downloaded if you are concerned about the permission state of your backup in your S3 bucket.
  • If you don't recall your DBSCHEMA name, try this simple command:  grep -i database <FULL PATH TO DUMPFILE>

 

Note: Data replication between the Master and Slave database Servers cannot occur if the replication user is not defined in your database.  See Create Credentials for Common Inputs.

diag-EBSStripeMaster-v1.png (EBS_STRIPE_COUNT = 3)

Register Server as the Master-DB and Create a Backup

In order for the automatic backup RightScript to function properly, you first need to designate the running instance as the "master" database Server.  The next step is to run a script that will make the server function as a "Master-DB" server.

Go the Scripts tab of the running Server.  Under the Any Script section, select and run the "DB register master" RightScript to register this server as the "Master-DB" and update your DNS A records accordingly.  Note: If you do not see this RightScript in the list, you will need to import it from the MultiCloud Marketplace (Design -> MultiCloud Marketplace -> RightScripts).

Select the script and click Run.

screen-AnyScriptRegisterMaster-v1.png

When the Inputs confirmation window appears, check the override box for "DB_TEST_MASTER_DNSID" and enter the DDNS ID for the Master-DB and run the RightScript.  You can retrieve the DDNS ID for the Master-DB Server from your DNS Provider (e.g. DNSMadeEasy).

Required

Name Input Type Description
 DB_TEST_MASTER_DNSID Text/Cred Check the override box and enter the DDNS ID for the Master-DB (ex: 2345678)
 DNSMADEEASY_PASSWORD Text/Cred The password for your DNSMadeEasy account
 DNSMADEEASY_USER Text/Cred The username for your DNSMadeEasy account

 

When the script is run, the running server's Private IP Address will be reported to DNS Made Easy so that the A Record that you previously defined for your master database server can be properly updated.  For a quick verification check, you can log into your DNS provider and check your A Records.  The IP address of the "Master-DB" A Record and the private (not public) IP address of the server you just defined as the "Master-DB" should match.  Notice that since we don't have a slave up and running, the A Record for the Slave-DB still has its placeholder IP address (1.2.3.4).  Later, when we launch the Slave-DB, this A Record will also be updated.

screen-ARecordMatch-v1.png

The next step is to manually create a snapshot (backup) of the EBS volume(s), which is required before you launch the second (slave) Server.  Go to the Scripts tab of the running Server and run the "DB EBS backup" operational RightScript.  This script will take a backup of your MySQL database by creating a snapshot for each attached volume.

 screen-RunBackupScript-v1.png

Important

  • You must wait for all of the snapshots to be 100% complete before continuing the tutorial.  Go to Clouds -> AWS Region -> EBS Snapshots to check the status.  If there are pages of Snapshots, use the Filter by Nickname option and search for the value that you set for the DB_LINEAGE_NAME input to quickly find your related snapshots.  (e.g. mystripe)  You should find a snapshot for each volume in the stripe. 
  • Note: The time required to complete the initial EBS snapshot will vary depending on the size of the EBS volume(s).  For example, it can take a long time (over an hour) for large volumes, where smaller volumes might complete in just 5-10 minutes.

screen-BackupSnapshot-v2.png

You now have a backup of your MySQL database in the form of a set of EBS snapshots that you can use to launch a fresh, new database Server using the ServerTemplate.

Launch a Slave-DB Server

Once the master snapshots are 100% complete, you are ready to launch your Slave-DB Server. (e.g. ebs-db2)  Click the Launch action button of the second Server in your Deployment.  This new Server will become a replicating slave Server of the existing master Server.

This time the ServerTemplate will find the most recent snapshots with the appropriate DB_LINEAGE_NAME (e.g. mystripe).  The snapshot(s) will have a matching prefix (e.g. mystripe-master-yyyymmddttmm).

Note:  For optimization purposes, the ServerTemplate will automatically use the most recent completed set of backup snapshots regardless of whether it's a "slave" or "master' backup.  This way, the slave will be able to "get up to speed" faster and be in-sync with the Master-DB server in a shorter timeframe.

Important!  Set the INIT_SLAVE_AT_BOOT Input parameter to "True" since you have a Master-DB Server that's currently running.  Setting this Input to "true" will instruct the instance to initialize MySQL as a "Slave-DB" server during the boot process.

Congratulations!  In a few minutes you, when the Server enters the "operational" state you will have a MySQL master/slave database setup with data replication and automatic backups saved as EBS snapshots.

screen-MasterSlave-v2.png

Since we defined this server to be a Slave-DB server at launch time, one of the scripts reported its Private IP Address back to DNS Made Easy.  So, if you checked your A Records again you should see that both database A Records are properly pointing to the Private IP Addresses of your master and slave database servers.

screen-ARecordsComplete-v1.png

Note:  In this example that we purposely did not name the first database server "master" and the other one "slave" because we do not want the Server names to become a source of confusion.  Remember, over the lifecycle of your setup you'll most likely have to promote a "slave" to become the new "master" database Server for troubleshooting purposes or for performing various upgrades (e.g. vertical scaling from a 'small' to 'large' instance type). 

Important!  If for any reason both Servers are shutdown and you want to recreate the master/slave setup, it technically doesn't matter which Server you launch first.  Simply launch the first Server with INIT_SLAVE_AT_BOOT = False.  Once the Server becomes operational, simply run "DB EBS restore and become master" RightScript to make it become the "master" Server.  Then you can launch the other Server as the "slave" with INIT_SLAVE_AT_BOOT = True.

Post Tutorial Steps (optional)

Troubleshooting

Be sure to check out the Manager for MySQL Stripe Runbook to learn how to properly resolve different problem scenarios.

Backup Frequency

The frequency of the backups are defined in the ServerTemplate.  You can modify any of the Inputs to change the frequency and number of stored backup snapshots. 

  • DB_BACKUP_KEEP_DAILY - Defines the number of daily snapshots to keep.
    • Default: 14
  • 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)
  • DB_BACKUP_KEEP_MONTHLY - Defines the number of monthly snapshots to keep.
    • Default: 12
  • DB_BACKUP_KEEP_WEEKLY - Defines the number of weekly snapshots to keep.
    • Default: 6
  • DB_BACKUP_KEEP_YEARLY - Defines the number of yearly snapshots to keep.
    • Default: 2

Subsequent Launches of the Servers

Since this lab only covers the initial launch of the server, here is how to produce subsequent launches:

For Master:

  • Launch server
  • Ignore all the existing 'ANY SCRIPT' instructions from the first launch.
  • Run Operation script "DB EBS restore and become master v2 [rev 16]"

 

For Slave:

  • Make sure to set INIT_SLAVE_AT_BOOT = true
You must to post a comment.
Last modified
21:10, 16 May 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.