Back Up the Database

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

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.

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.

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:

To configure the backup based on a schedule

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:

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

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.

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

or

sudo -i eybackup -e mysql -n

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

or

sudo -i eybackup -e postgresql -n

Verify backups

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.

To verify a database restore

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:

Comments

Mark Wilden

May 15, 2012 00:50

When I run 'sudo -i eybackup -e mysql –new-backup' I get the error message, "You need to have a backup file at /etc/.w-backup.backups.yml", which I guess no one has ever seen before. What should I do about it in order to backup my database? There used to be a button.

The errors mentioned in the preceding comments most commonly occur when trying to use the eybackup tool from a non-database instance. I've requested a documentation update to make the wording above a bit more clear about this. If you do encounter this issue while connected to a database instance please file a ticket with our Support team so we can assist further.