MySQL-EBS v4 Database Setup

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 v4".  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. 

Launch a Server to create an EBS Snapshot

The "MySQL EBS" ServerTemplate is based off 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" ServerTemplate, it will look for the most recent EBS snapshot (or snapshots depending on if you're using a set of striped EBS volumes) 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.  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 following ServerTemplates:

• MySQL 5.1 EBS v4  or MySQL 5.0 EBS v4
• EBS Toolbox v2

The 'Toolbox' ServerTemplate doesn't actually launch a server.  Instead it contains several useful RightScripts that you will use in the future to perform various tasks. To initially set up your database you'll specifically need to use the "EBS Stripe create" RightScript. Later in the tutorial, you will also need to use the "DB register master" RightScript.

Note:  If you plan to make modifications to the ServerTemplate, you should clone the imported template to create an editable copy.

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.  Keep the default availability zone (ex: us-east-1a).  Click Add.

Note:  The "MySQL EBS" 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 Ubuntu_9.10_x64_v4.5" or "RightImage CentOS_5.4_x64_v4.4")

Next, we will define some common Input parameters.  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.  At the Deployment level, click the Inputs tab.  Most of the input parameters can be inherited from the ServerTemplate.  However, there are a few inputs that should be defined at the deployment level. Click Edit and configure the following Input parameters. 

When ready, select Save to save your Inputs.

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

When the input confirmation screen appears, provide the following parameters.  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.  You must set the value for the INIT_SLAVE_AT_BOOT input parameter 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.

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 the Initial Database

The next step is to create the volume (set) and the initial database.  Once the server becomes operational, click the SSH Console button to ssh into the "ebs-db1" server.

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

Stop the MySQL daemon

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

Set up MySQL

cd /mnt                           # Change to the /mnt directory
mv /mnt/mysql /mnt/mysql-backup   # Move the mysql directory to a "backup" version
mkdir /mnt/mysql                  # Create the new mysql directory

Create and Mount an EBS Volume
Under the server's Scripts tab, use the Any Script option and run the "EBS Stripe create" imported RightScript.  If you do not see this script, you will either need to import the "EBS Toolbox v2" ServerTemplate or the script itself from the MultiCloud Marketplace.

You will be asked to specify the following input parameters:

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

df -h         # Shows an appropriately sized volume mounted on /mnt/mysql

Restart the MySQL daemon

Copy MySQL from the LVM disk to the EBS volume and restart the MySQL daemon.

mv /mnt/mysql-backup/* /mnt/mysql/       # Move the mysql directory to a "backup" version
chown -R mysql:mysql /mnt/mysql          # Set the ownership and group
service mysqld start                     # Restart mysql on CentOS. Use "service mysql start" for Ubuntu

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=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

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 "EBS Toolbox v2" ServerTemplate or the script itself 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.

To manually create a snapshot (backup) of the EBS volume, go the Scripts tab of the running server and run the "DB EBS backup" operational script.

Go to Clouds -> AWS Region -> EBS Snapshots.  You must wait for the snapshot(s) 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.

You now have a snapshot of the database that you can use to launch a fresh, new database server using the ServerTemplate.

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

When you terminate the instance, the "DB Delete EBS volume on halt v2" 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, you must clone the MySQL EBS 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.

Launch a Master-DB Server

You are now ready to launch your new Master-DB server.  Once it is stopped (from the previous step's 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 v2" operational script.  This script will make the server a Master-DB.  

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

Go to Clouds -> AWS Region -> 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 was created of a volume of the master server instance (AWS id = 873596ec).

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.  Be sure to add the Slave-DB in the same region as your Master-DB.  Select the 'AWS US-East' cloud region and click the Add Server button.  Use the same "MySQL EBS v2" 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 v2 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.

This time the template will find the most recent snapshot with the appropriate DB_LINEAGE_NAME (ex: mystripe).  The snapshot will have a matching prefix (ex: mystripe-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.

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)

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