Home > ServerTemplates > Archive > 11H1 > Tutorials > Database Manager with MySQL 5.0/5.1 Database Setup

Database Manager with MySQL 5.0/5.1 Database Setup

Objective

To create a redundant MySQL (v5.0 or v5.1) database setup that uses EBS snapshots as backups. The Database Manager for MySQL 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.

Note: This tutorial is designed for the 11H1 Compatibility Release.

Table of Contents

Prerequisites

  • Please familiarize yourself with our compatibility naming conventions, and the significance of "11H1", as it is important for this tutorial.
  • 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!

  • This tutorial is specifically designed for users of the 11H1 Compatibility Release.
  • OS Support
    • "Database Manager with MySQL 5.0 - 11H1" - CentOS 5.4
    • "Database Manager with MySQL 5.1 - 11H1" - Ubuntu 10.04

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 a striped set of EBS volumes, 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'. If your site requires high availability, it's recommended that you launch the Master-DB and Slave-DB servers into different availability zones, so if there's ever an outage in one zone, you can failover to the running database server in the other zone.

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.

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.

diag-EBSStripe-v1.png

Steps

Create a Deployment (if needed)

If you already have a Deployment for this tutorial, skip this section.

To create a Deployment, go to Manage > Deployments and click New. Provide a title and a brief description. Later, when you add Servers to the Deployment, you can set a specific Availability Zone. The Availability Zone that is defined at the Deployment level only takes effect if you add a Server with ‘-any-‘ as its Availability Zone.

For this tutorial, ignore the Default VPC Subnet and Default Placement Group and keep the default ‘None’ settings.

When you are ready, click Save to finish creating your Deployment.

Add Servers to the Deployment

The Database Manager for MySQL ServerTemplates are based on Amazon Elastic Block Store (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 a Database Manager for MySQL ServerTemplate, the template uses the most recent EBS snapshot (or snapshots if you’re using a set of striped EBS volumes) available to begin the boot process.

Because you are creating this EBS setup from scratch, you don’t yet have a backup of the database (i.e. an existing set of EBS Snapshots). Therefore, the first step is to create your first backup of the database. When dealing with EBS Volumes, you should always attach fresh volumes that were created from the most recent snapshots.

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

  • ServerTemplates: Database Manager with MySQL 5.0 – 11H1 and/or Database Manager with MySQL 5.1 – 11H1. Import the appropriate ServerTemplate for the type MySQL dump file that you’re using (5.0 or 5.1). If you’re using the sample dump file included in this tutorial, import the Database Manager for MySQL 5.0 ServerTemplate.
  • RightScripts: DB Create MySQL EBS stripe volume – 11H1 and DNS Master DB register – 11H1. You can also download the matching “Toolbox” ServerTemplates from the MultiCloud Marketplace if you want to import all of the related MySQL scripts. At a minimum, you will need those two RightScripts to complete this tutorial.

Note: If you plan to make modifications to the ServerTemplates or RightScripts, make sure to clone the templates or scripts before you modify them.

From the Deployment’s Servers tab (Manage > Deployments > deployment name), 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 “Database Manager for MySQL 5.0/5.1” ServerTemplate. Provide a nickname (i.e. my-db1) and select your SSH Key and Security Group.

For the Availability Zone, you can either launch the Master and Slave Servers in the same or different zones. For production environments, you might want to have your database servers in separate zones. But for testing purposes, you can launch the servers in the same zones.

Select a particular Availability Zone (e.g. us-east-1a). Click Confirm, double-check your selections and click Finish.

This first server is your Master Database Server. Although a running Slave Database Server is technically not required to have a functional site, it’s strongly recommended for replication and failover purposes. Fortunately, you can use the same ServerTemplate to add a second database server that will become the “slave” for the Master Database.

While you can repeat the steps above to create a second server, an easier method is to clone the existing server. Click on the Master Database Server’s Nickname link. Then click the Clone button. Give the second server a new name (e.g. jd-db2).

For high-availability purposes, we recommend that you launch the Slave Database Server in a different availability zone than the Master Database Server. Under the Info tab for the Slave Database Server, click the Edit button. Select a different availability zone for the slave (e.g. us-east-1b).

screen-ServersInactive-v1.png


Define Inputs for the Deployment

Next, as a best practice, we will define some common Input parameters at the Deployment level. Defining the Input parameters at the Deployment level ensures that all of the Servers in the Deployment inherit the Inputs and their values. This means that you only have to define the Inputs and set the values once.

Go to the Deployment’s Inputs tab and click Edit .

Notice that most of the Inputs are inherited from the ServerTemplate. However, there are a few required Inputs that you need to define, as well as a few others whose values you might want to change.  Hover over the Info icon next to the Input name for a detailed description and a sample value. The values that you need to provide depend on the DNS solution you are using.

By default, the Inputs with missing values have “Inherit” selected as the data type. Use thepull-down menu to select the appropriate data type for the Input (e.g. Text, Cred (entail), SSH, etc.).

Database

Name Description Example Values
 DB_LINEAGE_NAME Enter the name that will be used for the backup snapshots of the database. text:mystripe
 MASTER_DB_DNSID

Enter the ID that points to the Master Database Server’s DNS Record, which maps to its private IP address.

See Domain Setup.

DNSMadeEasy: 1234567

DynDNS: db-master.dyndns-home.com

Route 53: Z3DSDFSDFX:db-master.aws.site.com

Note:If Route 53 is used, the Time-To-Live (TTL) for the A Record must be 60 seconds, otherwise the script will fail.

 MASTER_DB_DNSNAME

Enter the Fully Qualified Domain Name (FQDN) that points to the Master Database Server.

See Domain Setup.

DNSMadeEasy: master1.example-dnsname.com

DynDNS: db-master.dyndns-home.com

Route 53: db-master.aws.site.com
 SLAVE_DB_DNSID

Enter the ID that points to the Slave Database Server’s DNS Record, which maps to its private IP address.

See Domain Setup.

DNSMadeEasy: 1234567

DynDNS: db-slave.dyndns-home.com

Route 53: Z3DSDFSDFX:db-slave.aws.site.com

Note:If Route 53 is used, the Time-To-Live (TTL) for the A Record must be 60 seconds, otherwise the script will fail.

 

DNS

Name Description Example Values
 DNS_PASSWORD Enter the password for your DNS account. Use a credential if you do not want to enter the value as text.

DNSMadeEasy: password  
DynDNS: password  
Route 53: cred:AWS_SECRET_ACCESS_KEY

 DNS_PROVIDER Enter your DNS provider. If you're using a DNS provider other than DNSMadeEasy, you will need to select it. text:DNSMadeEasy (default)
text:DynDNS
text:Route53
 DNS_USER Enter the username for your DNS account. Use a  credential if you do not want to enter the value as text. DNSMadeEasy: username
DynDNS: username
Route 53: cred:AWS_ACCESS_KEY_ID

 

SSH

Name  Decription Example Values 
 PRIVATE_SSH_KEY

Select the same SSH Key that you selected when you added the server. This key is used for server-to-server communication. Make sure the private key material is not missing.

Syntax: key:<keyname>:<cloud_id>

key:production:1

 

After setting the Input values using the preceding tables, click Save.

Launch the Master Database Server

Go back to the Deployment’s Servers tab (Manage > Deployments > deployment name) and launch the first database server (e.g. my-db1). This server will become your Master Database Server. To launch the server, click the Launch action icon.

screen-ServersInactiveLaunch-v1.png

When the Input Confirmation screen opens, verify that the INIT_SLAVE_AT_BOOT Input parameter is set to "False" (default). Since this server will be your Master Database Server, it is important to make sure that the server does not initialize as a slave database server at boot time.

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

Note: Do not launch your slave database server until you back up the database of the master server. Master server backup is covered in a later section of this tutorial.

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 (about 5-10 minutes).

Note: If you receive an error message about missing Input parameters and no Input is highlighted in red, it’s most likely because some of the required RightScale Credentials do not exist. The ServerTemplates have been preconfigured to use certain Credentials and assume that you’ve already created them.

If you did not complete the Create Credentials for Common Inputs step in the Deployment Setup section properly, you may not have created the required credentials that the ServerTeplates are trying to use. Once you’ve created those credentials, you should be able to successfully launch the server without receiving any missing Input error messages.  

Set up MySQL Database

The next step is to create and attach an Amazon Elastic Block Store (EBS) volume, or stripe of EBS volumes, to the running instance where your MySQL data will be stored.  To do this, you use the DB Create MySQL EBS stripe volume – 11H1 RightScript, which creates and attaches an EBS stripe designed for a MySQL server.  By default, it creates a blank MySQL database with no striping that includes all the necessary privileges for replication, administration, and application purposes.

Go to the the Scripts tab of the running server. Under the Any Script section, select the latest revision of the DB Create MySQL EBS stripe volume – 11H1 script. If you do not see this script in the list, you can import it from the MultiCloud Marketplace (Design > MultiCloud Marketplace > RightScripts). See DB Create MySQL EBS stripe volume - 11H1.

After you select the script, click Run.

screen-RunCreateStripe-v1.png

Note: If you run revision 9 of the script, you may receive the following Audit Entry: “failed: DB Create MySQL EBS stripe volume – 11H1”. This is not an issue. The script executed successfully and imported the database into the attached volumes. There is a check that needs to be modified in this script to prevent this Audit Entry from being logged. A modified version of the script will be published soon, but in the meantime you can continue with the rest of the tutorial.

Before the script executes, you are prompted to provide values for some of the Input parameters. Use the following tables to set these values. When all of the required Inputs have values and you are ready, click Continue to execute the script.

Backup

Name Description Example Values
 EBS_STRIPE_COUNT

Set the number of EBS volumes to create in the EBS stripe (e.g. 1,2,3, or more). The script creates and mounts the EBS volumes to the instance.

The default value is 1, which means only a single volume is used (no striping).

For this tutorial, set the value to 3. This creates an EBS stripe that consists of three EBS volumes.

text:3

 EBS_TOTAL_VOLUME_GROUP_SIZE

Set the total size of the striped EBS volume set in GB. For example,

For this tutorial, set the value to 3. A value of 3 here combined with a stripe count of 3 above, creates an EBS stripe that contains 3 EBS volumes that are each 1 GB in size (total size 3 GB).

text:3

 

Cloud

Name Description Example Values
 AWS_ACCESS_KEY_ID

Select the predefined Credential for the AWS Access Key ID.

cred:AWS_ACCESS_KEY_ID

 AWS_SECRET_ACCESS_KEY

Select the predefined Credential for the AWS Secret Access Key.

cred:AWS_SECRET_ACCESS_KEY

 SERVER_EIP (Optional) If there are remote servers (located outside of the EC2 region in which the database servers are launched) that need to communicate with master database server, you can assign the instance an Elastic (public) IP address.

env:  RS_EIP

of "my-db1" server

 

Database

Name Description Example Values
 DBADMIN_PASSWORD

Select the Credential you created for the password of the admin user in the database.

Make sure the Credential has the correct value for this tutorial (admindb).
cred:DBADMIN_PASSWORD
 DBADMIN_USER

Select the Credential you created for the username of the admin user in the database.

Make sure the Credential has the correct value for this tutorial (admindb).

The admin username and password are used for tasks that require administrator access to the database.
cred:DBADMIN_USER
 DBAPPLICATION_PASSWORD

Select the Credential you created for the password of the application user in the database.

Make sure the Credential has the correct value for this tutorial (1234).
cred:DBAPPLICATION_PASSWORD
 DBAPPLICATION_USER

Select the Credential you created for the username of the application user in the database.

Make sure the Credential has the correct value for this tutorial (1234).

The application username and password allow the application to access the database in a restricted fashion.
cred:DBAPPLICATION_USER
 DBREPLICATION_PASSWORD

Select the Credential you created for the password of the replication user in the database.

Make sure the Credential has the correct value for this tutorial (dbrpl).
cred:DBREPLICATION_PASSWORD
 DBREPLICATION_USER

Select the Credential you created for the username of the replication user in the database.

Make sure the Credential has the correct value for this tutorial (dbrpl).

The replication username and password are used for replication between the Master and Slave Database Servers.
cred:DBREPLICATION_USER
 DB_LINEAGE_NAME Enter the name the script will use to locate the appropriate EBS snapshots for the EBS striped volume set. This value will also serve as the common name for each backup snapshot (e.g. mystripe). text:mystripe
DB_MYSQLDUMP_BUCKET Enter the name of the S3 bucket where the existing MySQL database dump file is stored (e.g. myproject-db). Click the ‘Override’ checkbox, select ‘Text” and enter the name of the S3 bucket.

text:mybucket

DB_MYSQLDUMP_FILENAME

Enter the full filename of the MySQL dump file. This file needs to be located in the DB_MYSQLDUMP_BUCKET you set above.  

Click the 'Override dropdown' checkbox, select 'Text'. Enter the name of the MySQL dump file. You must specify a full filename. For example: myapp_prod_dump-200804161345.gz

This is the name of the MySQL dump that file you uploaded to your S3 bucket earlier in this tutorial.

text:myapp_prod_dump-200804161345.gz

DB_SCHEMA_NAME

Enter the name of the database schema. This name is set when you import the dump file into MySQL. The name is only defined within the MySQL instance and not within the actual dump file. As a result the name is somewhat arbitrary but should be descriptive.  

Important! Be sure to record this value. The Application Servers use this value to connect to the correct database schema.

text:myschema

 

Register the Master Database Server with your DNS Provider

In order for the automatic backup RightScript to function properly, you first need to designate the running instance as the Master Database Server. Then you need to run a script that makes the server function as a Master Database Server. To do this, you use the DNS Master DB register – 11H1 RightScript. This script registers the server as the “Master-DB” and updates your DNS A Records accordingly.

Go to the Scripts tab of the running server. Under the Any Script section, select and run the DNS Master DB register – 11H1 script. Select the latest revision of the script. If you do not see this script in the list, you can import it from the MultiCloud Marketplace (Design > MultiCloud MarketPlace > RightScripts). See DNS Master DB register - 11H1.

After you select the script and the revision, click Run.

screen-RunDNSMasterRegister-v1.png

When the Inputs confirmation window appears, enter any missing Input parameter values. Be sure to use the same values that you defined earlier at the Deployment level. When you run an Any Script, these Inputs are not pre-populated from the values you defined at the Deployment level.

The followingInputs are used by the script and are pre-populated from the Deployment level: MASTER_DB_DNSID, DNS_PASSWORD, DNS_USER. You do not need to set the values for these Inputs again.

When the script executes, the running server's Private IP Address is reported to your DNS provider and the A Record that you previously defined for your Master Database Server is updated. To quickly verify this change, log into your DNS provider and check your A Records. The IP address of the "Master-DB" A Record should match the private (not public) IP address of your Master Database Server. Notice that because the Slave Database Server is not up and running yet, the A Record for the “Slave-DB” still has its placeholder IP address (1.2.3.4). Later, when you launch the Slave Database Server, this A Record will be automatically updated by one of the boot scripts.

The following screenshot shows an example for DNSMadEasy.

screen-ARecordMasterOnly-v2.png

Back Up the Master Database

The next step is to manually create a snapshot (backup) of the EBS volume(s) for the MySQL database. This step is required before you launch the Slave Database Server. When you launch the Slave Database Server, it uses the most recent database backup to restore the database. This enables the server to come up and get in-sync with the Master Database in a much shorter timeframe.

Go to the Scripts tab of the running server and run the "DB EBS backup - 11H1" operational RightScript. This script makes a backup of your MySQL database by creating a snapshot for each attached volume in the stripe.

 screen-RunEBSBackup-v1.png

Important! You must wait for all of the snapshots to be 100% complete before continuing the tutorial. Go to Clouds > your AWS Region > EBS Snapshots to check the status. If there are multiple 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.

The time it takes to complete the initial EBS snapshots varies depending on the size of the EBS volumes. 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-CompleteSnapshots-v1.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 same ServerTemplate. Notice that each snapshot has the same name and timestamp, but has a unique ID and a tag which denotes the stripe order.

Launch a Slave Database Server

When the database backup is 100% complete, you are ready to launch your Slave Database Server (e.g. my-db2). 

Before you launch your Slave Database Server, you might want to consider changing its Availability Zone. Since you created the slave server by cloning the master server, the slave server is configured to launch an instance into the same Availability Zone as the master. If you want added redundancy (and are willing to pay for the additional data transfer costs), we recommend that you launch the slave server in a different Availability Zone from the master server. This allows you to have a running and in-sync backup server that you can use for failover and recovery scenarios if there is ever a major failure in the Availability Zone of your master server. You can edit the slave server's Availability Zone under the server’s Info tab.

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.

The ServerTemplate finds the most recent database snapshots using the DB_LINEAGE_NAME Input parameter (e.g. mystripe). The snapshots have a matching prefix (e.g. mystripe-master-yyyymmddttmm). For optimization purposes, the ServerTemplate automatically uses the most recent completed set of backup snapshots regardless of whether they are "slave" or "master” backups. This way, the slave server launches and is synchronized with the master server in a shorter timeframe.

Important! Set the INIT_SLAVE_AT_BOOT Input parameter to "true" (under the "Database" category's advanced inputs section) since you have a Master Database Server that's currently running. Setting this Input to "true" instructs the instance to initialize MySQL as a "Slave-DB" server during the boot process. Click the Launch, not Save and Launch button.

Congratulations! In a few minutes, 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-v4.png

Note: In this example, we purposely did not name the first database server "master" (in the Dashboard) 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 Deployment 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). 

Because you defined this server to be a Slave Database Server at launch time, one of the scripts reported the server’s Private IP Address to your DNS provider. Verify this change by logging into your DNS provider and checking your A Records. You should see that both of the A Records for your database servers are properly pointing to the Private IP Addresses of your Master and Slave Database Servers.

The following screenshot hows an example for DNSMadeEasy.

screen-ARecordsComplete-v3.png

Important! If for any reason both servers are shut down, and you want to recreate the master/slave setup, it doesn't matter which server you launch first. Simply launch the first server with INIT_SLAVE_AT_BOOT set to false. Once the server becomes operational, run "DB EBS restore and become master" RightScript to make it the "master" server. Then you can launch the other server as the "slave" with INIT_SLAVE_AT_BOOT set to true. See the Database Manager for MySQL Stripe Runbook for other common scenarios.

Shutting down a Database Server

If you ever need to shut down a database server, be sure to terminate it by running the 'DB TERMINATE SERVER' operational script, which will automatically delete the detached EBS volumes. If you terminate an instance using the "Terminate" action button in the Dashboard, the detached EBS volumes will not be deleted. You will need to manually delete them.

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 Database Servers

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

For Master:

  • Launch the server that will become your Master-DB. Set INIT_SLAVE_AT_BOOT = false
  • Once the server becomes operational, run the "DB EBS restore and become master" Operational Script

 

For Slave:

  • Once the Master-DB has been configured, launch the server that will become your Slave-DB. This time make sure to set INIT_SLAVE_AT_BOOT = true
You must to post a comment.
Last Modified
21:09, 16 May 2013

Page Rating

Was this article helpful?

Tags

This page has no custom tags set.

Announcements

None

Glossary | 用語용어 Site Map | Site Help Community Corporate Site Get Support Dashboard Login
Doc Feedback Product Feedback Resources MultiCloud Marketplace Forums

Dashboard Status


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