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 v1 (No Stripe) > MySQL-EBS v1 Database Setup

MySQL-EBS v1 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.

Prerequisites

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

This tutorial assumes that you are building the setup from scratch with a MySQL dump of your database.  In MySQL, you must not define a password for 'root'@'localhost' otherwise our scripts will fail.  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. 

Warning!
This tutorial is specifically designed for users of the "MySQL EBS v1" ServerTemplate, which supports MySQL v5.0 (w/ no EBS Striping).  If you would like to use MySQL v5.1 or EBS Striping, you must use the v2 ServerTemplates (e.g. "MySQL 5.1 EBS v2" or "MySQL 5.0 EBS v2") and follow the MySQL-EBS v2 Database Setup tutorial instead.

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

diag-MySQL_ebs_nostripe-v1.png

EBS volumes and snapshots are EC2 region-specific.  You cannot use a volume/snapshot that you created in AWS US-East in a different region like AWS US-West.  This tutorial assumes you are creating an EBS setup for AWS US-East.

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.

The tutorial below uses a single ServerTemplate ("MySQL EBS v1") to build both the master and slave 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.

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

diag-Replication-v1.png

Steps

Create a Deployment

Note: Some may have already created a Deployment as part of the "Deployment Setup" prerequisite steps.  If so, please skip to the next step.

Go to Manage -> Deployments and select the "New" button to create a new deployment and call it "MySQL EBS."  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.  For this example, select "us-east-1a" and click Save.

screen-NewDeployment-v1.png

Launch a Server to create an EBS Snapshot

The "MySQL EBS v1" ServerTemplate is based on the use 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 v1" ServerTemplate, it will look for the most recent EBS snapshot to begin the boot process. 

Since you are creating this EBS 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.  You will need to create a volume, attach it to a server, populate the database, take a snapshot, and then terminate the instance.  By default, a decommission script will delete the volume when the instance is terminated.  When dealing with EBS volumes, you should always attach a fresh volume that was created from the most recent snapshot. 

Go to Design -> MultiCloud Marketplace  -> ServerTemplates and import the latest version of the "MySQL EBS v1" ServerTemplate to your 'Local' view.  You should also import the "MySQL EBS Toolbox v1" ServerTemplate.  It is a 'Toolbox' ServerTemplate that doesn't actually launch a server.  Instead it contains several useful RightScripts that you will most likely need to use in the future to perform various tasks.  You will need to import it so that you can either add its RightScripts to a ServerTemplate or execute one as an 'Any Script' operation.  Later in the tutorial, you will need to use one of its RightScripts ("DB register master").

From the "MySQL EBS" deployment's Servers tab, select the 'AWS US-East' cloud region and click the Add Server button to add a server to the deployment.  

Select either the imported version.  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.

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.

Note:  The "MySQL EBS v1" ServerTemplate is designed for m1.small instance types.  If you want to use a large (m1.large) instance type, you will need to clone the template and use a 64 bit MultiCloud Image (ex: "RightImage CentOS_5.4_x64_v4.4" or "RightImage_Ubuntu_8.04_x64_v4.3").

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

screen-EditInputs-v1.png

 DB_EBS_PREFIX Text The prefix for each EBS snapshot name that will be created (e.g. mydb)
 DB_EBS_SIZE_MULTIPLIER Text Set to 'ignore' (only used when initializing a slave from a non-EBS master)
 EXTERNAL_DNS_ID Text The DNS Made Easy DNS ID of the server (e.g. 123456). This can be an additional DNS ID to update and is used only with the 'DNS dnsmadeeasy external id register v1 ' operational script. Set this to 'ignore' unless have an extra DNS record.
 MASTER_DB_DNSID Text The DNS Made Easy DNS ID of the master (e.g. 123456)
 INIT_SLAVE_AT_BOOT Text Set to 'false' (unless initializing a slave to replicate from a current master)
 MASTER_DB_DNSNAME Text The host name of the master in DNS (e.g. master1.mysite.com)
 PRIVATE_SSH_KEY Key Select the SSH Key of the server (this should be shared by both master and slave servers)
 SLAVE_DB_DNSID Text The DNS Made Easy DNS ID of the slave (e.g. 123456)
 DBADMIN_USER Text* The Administrator's username that is used to access the database
 DBADMIN_PASSWORD Text* The Administrator's password that is used to access the database
 DBREPLICATION_USER Text* The username that will be used by a slave database to connect to and subsequently replicate data from the master database
 DBREPLICATION_PASSWORD Text* The password that will be used by a slave database in order to replicate data from the master database
 DNSMADEEASY_USER Text* The username for you DNS Made Easy account
 DNSMADEEASY_PASSWORD Text* The password for your DNS Made Easy account

 * The username and password Inputs shown here are labeled as "Text".  You are of course free to label them "text" and type in the exact string required.  Alternatively, they can be set up in your Credential store.  When doing so, you simply select "Cred" and pick the appropriate set of credentials from the drop down menu provided.  Some consider setting up their Credentials first, and then referring to those credentials here when building out your Deployment to be a best practice. 

 

When ready, select Save to save your Inputs.

Go back to the Servers tab and launch the database server. 

screen-LaunchDB1-v1.png

When the input confirmation screen appears, revise your input values. 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. Ensure you have set the INIT_SLAVE_AT_BOOT input to 'false'.

 

Click the Launch action button to continue launching your database server instance.

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.  Under the CPU column, a real-time graph will be drawn of the instance's CPU usage. Click on it to view a larger graph of the thumbnail.

screen-ServerOperational-v1.png

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.

Create an EBS Volume

Go to Clouds -> AWS US -> 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 v1" 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)  You cannot attach an EBS volume to a server running in a different availability zone.
  • 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 accommodate 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.

screen-CreateEBSVolume-v1.png

 

 

Attach the EBS Volume

Now that you have created an EBS Volume, you need to attach it to a server in your deployment.

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

  • Server - Select the new volume that you just created (ex: mydb).  The format for the drop down is:  DeploymentName/ServerName
  • Devices - Attach the new EBS volume to the server's /dev/sdk device. 
  • Attachment Options - Specify that you want to "Attach now" since you already have a running server. 

Click OK when ready.

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 Events pane or you can occasionally refresh the screen.

screen-ViewAttachedVolumesl-v1.png

 

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 -h" command and it will show that the new EBS Volume has not been mounted or formatted.

Warning!  In MySQL, you must not define a password for 'root'@'localhost' otherwise our scripts will fail.

Format the Volume

mkfs.xfs /dev/sdk


Stop the MySQL daemon

service mysqld stop   # Stop mysql on CentOS. Use "service mysql stop" for Ubuntu

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 directorymv /mnt/mysql /mnt/mysql.bak  # Move the mysql directory to a "backup" versionmkdir /mnt/mysql              # Create the new mysql directorymount /dev/sdk /mnt/mysql -o noatime       # Mount the volume, with the new mysql as the new mount pointchown mysql:mysql mysql                    # Set the ownership and groupcd /mnt/mysql.bak                          # Change to the old (backup) copy of mysqltar 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 on CentOS. Use "service mysql start" for Ubuntu

NOTE:  You can run a "df -h" command and it will show the new EBS Volume, formatted, mounted and available for use.

 

Bring the data into the Server and populate the database

Use the SSH Console to SSH into the Master-DB and manually apply your *.sql dump file. See the sample code below.

  • s3bucket - the name of the S3 bucket that contains the 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 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 <DUMPFILE>
Set Application Permissions

Once you've applied your MySQL database, you should now set the application password.  The application password must NOT be root or a db-admin user.  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 the EBS snapshot.

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.

#!/bin/bash -e
# Copyright (c) 2007 by RightScale Inc., all rights reserved

#Set your Application username and password here
DBAPPLICATION_USER=userDBAPPLICATION_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

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.

Create an EBS Snapshot

In order for the automatic backup script to function properly, you first need to designate the running instance as the "master" database server. 

Go the Scripts tab of the running server.  Under the Any Script section, select and run the "DB register master" script from the list of imported RightScripts.  (If you do not see that RightScript in the list, you will need to import the "MySQL EBS Toolbox v1" ServerTemplate from the MultiCloud Marketplace.)  When the Inputs confirmation window appears, check the override box for "DB_TEST_MASTER_DNSID" and enter your DNS ID and run the script.  

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

screen-RunBackupScript-v1.png

 

Go to Clouds -> AWS US -> 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.

screen-SnapshotComplete-v2.png

You now have a snapshot of the database that you can use to launch a fresh, new database server using the "MySQL EBS v1" ServerTemplate.  Remember, the purpose of the first EBS volume was to create your first EBS snapshot.  Now that you have a snapshot, the volume is no longer needed.

You can now safely shutdown the "ebs-db1" server.  

When you terminate the instance, the "DB Delete EBS volume on halt" decommission script will delete the EBS volume that you just created.  As a best practice, the MySQL EBS ServerTemplates are designed to delete volumes from instances for two reasons:

  1. You always want to attach fresh volumes that were created from the most recent snapshot.
  2. If volumes were not deleted, it would result in many "stranded" volumes.  Obviously, this could get very unmanageable and confusing. 

RightScale's MySQL EBS ServerTemplates are designed to create volumes from the most recent snapshot and attach them to new instances.  You should never have to use a stranded volume, because once you have a snapshot, you should either be restoring from a recent (backup) snapshot or promoting the existing Slave-DB to Master-DB.

NOTE:  If you do not want the EBS volume to be deleted when the instance is terminated, then you must clone the ServerTemplate and remove the "DB Delete EBS volume on halt" decommission script.

Click the Terminate button to shutdown the "ebs-db1" server.  Wait for the instance's state to be "stopped" before continuing to the next step.

screen-TerminateServer-v1.png

Launch a Master-DB Server

You are now ready to launch your new Master-DB server.  Once it is stopped (from the previous steps terminate action), click the launch action icon to relaunch the "ebs-db1" server.  This time when it launches, there will be a snapshot to pull data from. 

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

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

Go to Clouds -> AWS US -> 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).  

screen-SnapshotCompleteMaster-v1.png

 

 

Launch a Slave-DB Server

Once the master snapshot is 100% complete, you are ready to launch your Slave-DB server.  Add a second server to the deployment.  Select 'EC2 US' cloud region and click the Add Server button.  Use the same "MySQL EBS v1" ServerTemplate, but provide a different nickname for the new server (ex: ebs-db2).  Click Add.

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

If the snapshot is 100% complete, you are now ready to launch the Slave-DB server (ebs-db2).  Click the Launch button.

screen-MasterOperational-v1.png

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.

screen-MasterSlaveOperational-v1.png

 

Notice 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 servers' names to become a source of confusion.  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 performing various upgrades (e.g. vertical scaling from a 'small' to 'large' instance type). 

Also, if both servers are shutdown and you want to recreate the master/slave setup, it technically doesn't matter which server you launch first.  Launch the first server with INIT_SLAVE_AT_BOOT = False.  Once the server becomes operational, simply run "DB EBS restore and become master" script to make it become the "master" server.  Then you can launch the other server as the "slave" with INIT_SLAVE_AT_BOOT = True.

Troubleshooting

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

Modify Backup Settings (optional)

Below are the default settings for the "MySQL EBS v1" ServerTemplate.

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


If you want to change the frequency of the snapshots, you will need to clone, modify and replace the current "DB EBS continuous backups" RightScript.  Modify the following frequency inputs accordingly.  The time of the first snapshot is randomized in order to prevent every EBS snapshot that's managed by RightScale from being taken at the same time.  All subsequent snapshots will be saved at the same minute of the hour. (i.e. 4:35, 8:35, 12:35, etc.)

  • 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: once 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
You must to post a comment.
Last modified
11:05, 7 Oct 2014

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.