Backup and Storage

The Database Manager for MySQL 5.1 ServerTemplate supports the use of a primary and secondary storage location when backing up databases.

Security

The methods you will use to secure access to your MySQL database server depend on your cloud provider. For Amazon EC2, CloudStack, and other clouds that support security groups, you must have a security group defined with TCP port 22 open for SSH access, along with any other ports required by the server (for example, TCP port 3306 for MySQL client access).

Iptables is also enabled by default on all servers, with the default SSH port (22) and web-access ports (80 and 443) open. For each database server, the default MySQL client port, TCP port 3306, is automatically opened to one or more other servers in your architecture depending on the server's replication role:

• For the master, port 3306 is opened to application servers in the same deployment that are based on the PHP App Server ServerTemplate. For more information, see the PHP Application Server Setup tutorial. 
• For the slave, port 3306 is opened to the master database server when the db::do_primary_init_slave operational recipe is run, as described under Initialize the Slave Server.
   

If you need to open other ports on a database server besides the default TCP ports (22, 80, 443, and 3306), you must create the appropriate rules using the "Firewall" inputs, as described in the Base ServerTemplate for Linux (Chef) documentation.

Note: For more information on iptables, refer to the Linux supporting documentation for this tool.

Upload the Database Dump File

The ServerTemplate contains scripts that can retrieve a MySQL database dump file from either 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.

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

If you are setting up a database server for testing purposes, you may use the following sample MySQL dump file to complete the tutorial.

• 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 following ServerTemplate into the RightScale account.
   • Database Manager for MySQL 5.1 or Database Manager for MySQL 5.5
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 into which the new server will be placed.
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.
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
  • INPUTS: DNS Service Provider, DNS Password, DNS User

Set Inputs at the Deployment Level
Go to the deployment's Inputs tab and click Edit.

Although you can enter values for missing inputs as text values, it's strongly recommended that you set up credentials for passing sensitive information to scripts such as passwords or any other sensitive data.

Rackspace only
If you use Rackspace for your database servers and backup storage (i.e., Cloud Files) the storage-related Chef recipes will use Rackspace Service Net (SNET) by default. SNET is Rackspace's internal private networking service for optimized communication between Rackspace Cloud Servers and Cloud Files. If SNET is not supported in your Rackspace environment, you must set the "Rackspace SNET Enabled for Backup" input to false; otherwise, all backup and restore operations that rely on Cloud Files will fail.

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 should page, there should not be any required inputs with missing values.  If there are any required inputs that are missing values (highlighted in red) at the input confirmation page, cancel the launch and add values for those inputs 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.

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.

The script will perform the following actions:

• Register 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.
• Create a backup.

• Schedule 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.

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 on 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. To 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 database server. There should 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 will perform the following actions:

• Assign the "slave" role to the server.
• Use 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.
• Send a request to the master server to allow connections from the slave server's private IP address, and open the default MySQL client port, TCP port 3306, on the master server's firewall (i.e., iptables) for this purpose.
• Schedule a cron job to run primary backups of the database. 
• If you set a value for the 'Database Slave DNS Record ID' input (optional), it will update the DNS record for the "slave" database server with its private IP address. 

Test Database Setup (optional)

If you want to test the status of the "master" and "slave" database servers, see Checking master or slave database status.