Upload the Database Dump File

The ServerTemplate contains scripts that can retrieve a MySQL database dump file from a Remote Object Storage (ROS) location such as an Amazon S3 bucket or a Rackspace Cloud Files container. Create a new bucket/container and upload your database dump file. The file can remain a 'private' object because your cloud credentials can be used (as inputs) for authentication purposes to retrieve the file. Make sure the uploaded file maintains the .gz file extension.

Warning! The filename of the MySQL dump file cannot contain a dash (-) in its prefix name. For example, if your dump file is named, 'my-app-201205030022.gz', you must manually rename it to be 'my_app-201205030022.gz' where you use an underscore (_) to replace the dash, otherwise the script (do::do_dump_import) that imports the database dump file into the instance will fail.

• Create a New S3 Bucket
• Create a New Cloud Files Container

If you are setting up a database server for testing purposes or if you do not have your own dump file, you can use the following sample MySQL dump file to complete the tutorial. The sample is a gzip (.gz) file.

app_test-201109010029.gz

Create a Database Server

Follow these steps to add a database server to the deployment.

1. Go to the MultiCloud Marketplace (Design > MultiCloud Marketplace > ServerTemplates) and import the most recently published revision of the Database Manager for MySQL 5.1 or Database Manager for MySQL 5.5ServerTemplate into your RightScale account.
2. From the imported ServerTemplate's show page, click the Add Server button.
3. Select the cloud for which you will configure a server. 
4. Select the deployment for the new server.
5. Next, the Add Server Assistant wizard will walk you through the remaining steps that are required to create a server based on the selected cloud.
   • Server Name - Provide a nickname for your new database server (e.g., mysql-db1). Do not include "master" or "slave" in the name, because a database server's role can change in the future.
   • Select the appropriate cloud-specific resources (e.g. SSH Key, Security Group, etc.) that are required in order to launch a server into the chosen cloud. The required cloud resources may differ depending on the type of cloud infrastructure. If the cloud supports multiple datacenters/zones, select a specific zone. Later, when you create the other database server you will use a different datacenter/zone to ensure high-availability. For more information, see Add Server Assistant.
   • Important! If you are not using volumes to store the database, you must select an instance type that has disk space that's at least twice as large as your database because LVM snapshots are performed locally on the instance before they are gzipped and saved to the specified ROS location. Also, although these ServerTemplates will work with any instance size, you may experience degraded performance with small instance sizes (such as EC2 micro, Rackspace 256MB etc) due to lack of system resources. We do not recommend smaller instance types for production use.
6. Click Confirm, review the server's configuration and click Finish to create the server.

Configure Inputs

The next step is to define the properties of your database server or servers by entering values for inputs. It is simplest and best to do this at the deployment level. For a detailed explanation of how inputs are defined and used in Chef recipes and RightScripts, see Inputs and their Hierarchy.

The inputs that you need to provide values for will depend on which options you're going to use. The ServerTemplate is very flexible and supports a variety of different configurations. You will need to provide the necessary values as inputs based on which options you want to use.

• Where will the contents of the database be stored?
  • On volumes attached to the instance.
  • On the local/ephemeral drive - Ignore all "Block Device" inputs.
• If you're using volumes, are you going to use a stripe?
  • Yes - Use a stripe of multiple volumes.
  • No - Use a single volume. 
  • INPUTS: Block Device Mount Directory (1), Nickname (1), Number of Volumes in the Stripe (1), Total Volume Size (1)
• Are you building a master-slave database setup with replication?
  • Yes
  • No
  • INPUTS: Database Master FQDN, Database Master DNS Record ID, Database Slave FQDN, Database Slave DNS Record ID, Database Replication Password, Database Replication Username
• What are you going to use to take "primary" backups of the database?
  • Snapshots
  • Binary Dumps to a ROS container (e.g. S3 bucket or Cloud Files container)
  • INPUTS: Backup Primary Secret (default), Backup Primary User (default), Primary Backup Storage Cloud (default)
• Are you going take "secondary" backups of the database? If yes, which ROS provider will you use?
  • Amazon S3
  • Rackspace Cloud Files
  • INPUTS: Secondary Backup Storage Cloud (default), Secondary Backup Secret (default), Secondary Backup User (default), Secondary Backup Storage Container (1)
• Which DNS provider are you using for dynamic DNS at the database level?
  • DNS Made Easy
  • DynDNS
  • Amazon Route 53
  • Cloud DNS
  • INPUTS: DNS Service Provider, DNS Password, DNS User

Launch the Database Server

After configuring your inputs, launch your newly configured master database server.

1. Go to the deployment's Servers tab and launch the database server. When you view the input confirmation page, there should not be any required inputs with missing values.  If there are any required inputs that are missing values (highlighted in red), cancel the launch and add the missing values at the deployment level before launching the server again. Refer to the instructions in Launch a Server if you are not familiar with this process. Because there are no required inputs that are missing values for any boot scripts, you can click the Launch button at the bottom of the input confirmation page. 

Initialize the Master Database Server

Wait for the server to reach the "operational" state before you run a script to initialize the database server.

1. Go to the "current" server's Scripts tab and run the db::do_init_and_become_master operational script to initialize it as the "Master" database server.
   
2. You can go to the "current" server's Audit Entries tab to track the status of the operation.

The script performs the following actions:

• Registers it as the "master" database server and assign appropriate replication privileges.
• For cloud providers with volume support, create and mount either a single volume or group of striped volumes for data storage, based on the inputs configured for your primary database backups.
• Creates a backup.

• Schedules a cron job to run backups to primary storage once every four hours on the server. (For information on modifying the default backup schedule, see the Database Manager for MySQL 5.1/5.5 Runbook.)
• Updates the dynamic DNS record for the "Master" database with your DNS provider. The DNS record will be updated with the server's private IP address. 
  Warning! If using Amazon Route 53 as your DNS provider, ensure that the TTL (time to live) is set to 60 seconds for each DNS record. Using a different TTL value will cause the db::do_init_and_become_master script to fail. If you are using Rackspace's Cloud DNS service, the DNS record's TTL and the value specified for the 'Database DNS TTL Limit' input must match. (e.g. 300)

Disable Scheduled Primary Backups

Since you have not loaded an actual database onto the server there is no reason to create a primary backup of the database.

Go to the "current" server's Scripts tab and run the db::do_primary_backup_schedule_disable operational script to disable your scheduled backups (cron jobs). 

Later, once you have imported your database you will reverse this action and enable continuous backups.

Set Up the Database

After initializing the master database server and disabling scheduled backups, you will need to add your database (or databases) and records to it.

1. Go to the "current" server's Scripts tab and run the db::do_dump_import operational script to import a MySQL dump file from an ROS location.

Note: If you use a previous backup snapshot instead of a MySQL dump file or initialize a blank MySQL database, refer to the Database Manager for MySQL 5.1/5.5 Runbook for instructions.

Create a Primary Backup

You are now ready to create the first primary backup of the database. You will need a completed backup in order to initialize a slave database server.

1. Go to the "current" server's Scripts tab and run the db::do_primary_backup operational script to manually generate a primary backup of your database server.

Enable Scheduled Primary Backups

It is now safe to enable continuous backups of the database server.

1. Go to the "current" server's Scripts tab and run the db::do_primary_backup_schedule_enable operational script.

For more information about configuring and modifying your scheduled backup policy, see the Database Manager for MySQL 5.1/5.5 Runbook.

Add a Slave Database Server

Although you can run MySQL in single-server mode and having a separate slave server for replication purposes is not required, this is strongly recommended for failover purposes. Create a slave server in your deployment.

1. Clone the Master-DB server. See Clone a Server.
2. Rename the server accordingly. (e.g. mysql-db2) Remember, you do not want to include the word "slave" in the nickname because this server may become the "master" server during a failover scenario. You don't want the server's nickname to potentially cause any confusion.
3. Under the server's Info tab, click Edit and change the server's availability zone. In order to ensure high availability, it's strongly recommended that you launch the Slave-DB server in a different availability zone as the Master-DB.  Note: Cross-zone data transfer costs may apply.

Launch the Slave Database Server

Make sure the following conditions are true before you launch the second database server.

• The master database server state is "operational."
• The initial primary backup of the master database server is 100% complete. If you are using a cloud that supports snapshots for backups, you can track the status in the dashboard (Clouds > region > Snapshots). The time required to complete the initial primary backup will vary based on factors such as storage type, volume size, etc.

You are now ready to launch a "slave" database server for failover and redundancy purposes. 

1. Go to the deployment's Servers tab and launch the server that will be the slave database server. There should be no missing inputs. 

Initialize the Slave Database Server

Wait for the server to reach the "operational" state before initializing it as a "slave" of the master database server.

1. Go to the "current" server's Scripts tab and run the db::do_primary_init_slave operational script.

The script performs the following actions:

• Assigns the "slave" role to the server.
• Uses the most recently completed database backup (default) to initially populate the database in order to reduce the time needed for the slave to become in-sync with the master.
• Sends a request to the master server to allow connections from the slave server's private IP address and opens the default MySQL client port (TCP port 3306) on the master server's firewall (i.e., iptables) for this purpose.
• Schedules a cron job to run primary backups of the database. 

(Optional) Update the DNS Record for the Slave Database Server

If you created a DNS record for the slave database server, you can set a value for the Database Slave DNS Record ID input and run the db::do_set_dns_slave_private_ip operational script.

Test Database Setup (optional)

If you want to test the status of the "master" and "slave" database servers, see Check Database Status of Master or Slave.

Important!
When you first SSH into a server (using a RightImage v5.8 and above) you are an 'anonymous' user. Therefore, you must first become the 'root' user before you can see all of database schemas. See the 2012-06-29 Release Notes for more information. At the command prompt, run the following command to become the 'root' user.

# sudo -i