Knowledge Base/Engine Yard Cloud Documentation/Manage your Database

Back Up the Database

Engine Yard
posted this on February 16, 2012, 10:02 AM

Updated: July 10th, 2014

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.

    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.

To change the number or frequency of database backups

  1. In Engine Yard, click Tools > Dashboard.
  2. Select an environment by clicking the environment name.
  3. On the Environment page, click Edit Environment.
  4. Under the Backups heading, set the number of hours between backups and the number of backups to keep.

    Backup Options

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

  5. Click Update Environment to save the new backup settings.
  6. 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

  1. Create a custom Chef recipe that schedules the custom backup patterns: hourly, daily, and weekly.
  2. 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
  3. 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.

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

  1. Via SSH, connect to a database instance in the environment to be backed up.
  2. Type:
    sudo -i eybackup -e mysql --new-backup

       or
    sudo -i eybackup -e mysql -n

To back up a PostgreSQL database on demand

  1. Via SSH, connect to a database instance in the environment to be backed up.
  2. 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

  1. Restore the database. See Restore or Load a database for instructions.
  2. Verify that no error messages display during the restore.
  3. 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.)

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.

 

Comments

User photo
Mark Wilden
mwilden

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.

May 14, 2012, 5:50 PM
User photo
Tim Walsh
USEED

I am also encountering an error when attempting to backup the database:

```

> sudo -i eybackup -e postgresql -n

You need to have a backup file at /etc/.postgresql.backups.yml

```

February 12, 2015, 11:39 AM
User photo
Tyler Poland
Engine Yard Inc.

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.

February 12, 2015, 12:11 PM