Overview

By default, the database is backed up every 24 hours, starting around 1:10 a.m. server time and the last 10 days of backups are kept. Backup frequency and number of backups kept can be configured through the UI. Start times can be configured through cron jobs and custom Chef recipes. This document describes:

Types of database backups
Setting the frequency and number of scheduled database backups
Configuring a custom database backup schedule
Making an on-demand backup of the database
Verifying backups
MySQL Locking Behavior

Information

Types of database backups

Engine Yard backs up your database in two ways:

Database backup files are dumps of the application’s database (e.g., pg_dump, mysqldump). Database backup files can be used for selective or full data restores and for downloading locally. An advantage of database backup files is that the data integrity is checked as the file is written.

Important: The database data integrity is verified; this is not the same as verifying that the backup/restore process is fully functional. Engine Yard recommends that you periodically validate your backups as a best practice. See Verify backups.
Snapshots are incremental S3 copies of the /db volume. By default, these snapshots are made every night at 1:00 a.m. server time. Snapshots are used to rebuild the instance, build a replica database instance, or create a copy (clone) of the database instance. Snapshots are fast and incremental but not a replacement for database backup files because snapshots do not check data integrity.

For information about snapshots, see Take Snapshots and Manage Snapshots.

Tip: Too-frequent backups or snapshots can cause disk I/O performance issues. If you need rapid failover, consider setting up a database replica.

Change the frequency and number of scheduled backups

When you create an environment, you set the frequency and number of backups (or accept the default). You can later change the frequency and number of database backups as described below.

Note: If you change the frequency or start times for your backups, snapshot times will also change. For example, if backups run every 12 hours, then snapshots will also run every 12 hours and start 10 minutes before the backups.

You don’t need to restart your environment to change the frequency or number of backups; just click Apply.
Please note that backups are CPU-intensive operations. They usually take 30 minutes to run. If you decide to set the frequency to every hour, the backups can use up most of the available CPU for 30 minutes and slow down normal operations. It is recommended to take backups when there is less activity in the application.

To change the number or frequency of database backups

In Engine Yard, click Tools > Dashboard.
Select an environment by clicking the environment name.
On the Environment page, click Edit Environment.
Under the Backups heading, set the number of hours between backups and the number of backups to keep.
- Frequency of backups includes both backups and snapshots.
- Number of backups to keep includes both scheduled backups and on-demand backups.
  
  Tip: You can set the number of snapshots (which includes database snapshots) under the Snapshots heading in the UI.
Click Update Environment to save the new backup settings.
Click Apply to push the policy changes to the instances.

Configure database backups using Chef

You can configure the database to back up on a non-standard schedule (a schedule that is beyond what the UI allows). For example, if you want to backup every 4 hours then store on the following schedule:

The last 6 hourly backups
The last 7 daily backups
The last 4 weekly backups

To configure the backup based on a schedule

Create a custom Chef recipe that schedules the custom backup patterns: hourly, daily, and weekly.
Specify a backup configuration file (see eybackup --help) for each of these backups by using the -c or --config option to eybackup.

The -c or --config option allows you to specify an alternate configuration file from the default /etc/.{dbtype}.backups.yml. This option allows you to customize the number of files to be maintained, the databases to be backed up, and the S3 bucket and account to use when uploading the backup. The format of the config file is:
```
:keep: 10 
:aws_secret_id: xxxxxxxx 
:aws_secret_key: xxxxxxx 
:region: us-east-1 
:backup_bucket: ey-backup-xxxxxxx 
:dbuser: deploy 
:dbpass: xxxxxxx 
:env: widget_production 
:databases:- widget
```
Use the default file for the hourly backup. The default configuration can be found at /etc/.mysql.backups.yml (/etc/.postgresql.backups.yml for postgresql).

The configuration for weekly and daily should each have their own distinct value for :keep and :backup_bucket.
Have the Chef script create copies of the default file and then edit the keep and backup_bucket fields (instead of storing this configuration file as a template with your custom Chef).

This method ensures that your custom configurations remain consistent with any changes made to the file in the future.

Note: Only backups stored in the default bucket are visible on the Engine Yard dashboard. To access the other backups, use the eybackup tool and specify the appropriate configuration file.

Back up on demand

Note: For on-demand encrypted backups please see the encrypted backups documentation.

Sometimes you might want to do an on-demand backup (also called ad-hoc backup). For example:

An on-demand backup can be done on a database replica; this allows you to back up even when your master database is under heavy load.
Before you make a significant change to your environment, you want to make sure that you have a very recent backup.

You perform on-demand backups using the eybackup tool. Each instance comes with the eybackup gem pre-installed.

How to back up a MySQL database on demand

Via SSH, connect to a database instance in the environment to be backed up.

Type:

sudo -i eybackup -e mysql --new-backup

sudo -i eybackup -e mysql -n

How to back up a PostgreSQL database on demand

Via SSH, connect to a database instance in the environment to be backed up.

Type:

sudo -i eybackup -e postgresql --new-backup

sudo -i eybackup -e postgresql -n

Verify backups

Note: For restoring encrypted backups please see the encrypted backups documentation.

As a best practice, Engine Yard recommends that you periodically verify that the backup/restore process is fully functional. To test, you can create a copy of your production database on a different (non-production) environment.

How to verify a database restore

Restore the database. See Restore or Load a database for instructions.
Verify that no error messages display during the restore.
Verify that all the tables are present in the restored database.

If the above checks out, your restore worked. (You can also run your usual tests to ensure that the data itself appears valid. However this is not the same as validating the restore.)

MySQL Locking Behavior

When non-transactional tables (MyISAM) are present in the target schema (logical database) a locking backup must be used to ensure data consistency. This locking backup will block writes to any table in the database for the duration of the backup, the length of the backup will depend on database size. Most applications need to be able to write at all times, so it is not unusual for a backup to cause application downtime when non-transactional tables are present.

You can check your database for the presence of these tables with a query like:

select table_schema, table_name from information_schema.tables where engine='MyISAM' and table_schema not in ('mysql','information_schema','performance_schema');

InnoDB is transactional and is the default engine for most modern applications. The default configuration of your Engine Yard database is also optimized for performance towards the InnoDB engine. The most common solution for these locks is to convert impacted tables to the InnoDB engine. Before proceeding with this, it is important to evaluate these changes in a testing or staging environment. It is also important to be aware that InnoDB does require 2-3x the disk space of MyISAM and the individual tables will be locked during conversion. Tables can be converted with a command like:

alter table [table_schema].[table_name] engine=innodb;

A handy way to generate all the conversion statements at once would be:

select concat('alter table ', table_schema, '.', table_name, ' engine=innodb;') from information_schema.tables where engine='MyISAM' and table_schema not in ('mysql','information_schema','performance_schema');

If converting to InnoDB is not an option some alternatives would be:

Use a custom chef cookbook to process the logical backup against a replica database instead. Replication will be delayed during the backup.
Adjust the daily backup schedule through the dashboard so that the backup runs at a more convenient time for your application.

More information

For more information about...	See...
SSHing into an instance	Connect to your instance via SSH
Viewing and downloading database backups	View and download database backups
Restoring the database	Restore or Load a Database
About snapshots	Taking Snapshots
Snapshot policies	Managing Snapshots
Database replicas	Set Up Database Replication

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.

Backup the Database