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

    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

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.

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

    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

    sudo -i eybackup -e postgresql -n

Verify backups

Note: For restoring of 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.

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


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.


  • Avatar
    Mark Wilden

    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.

    Comment actions Permalink
  • Avatar
    Tim Walsh

    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
    Comment actions Permalink
  • Avatar
    Tyler Poland

    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.

    Comment actions Permalink
  • Avatar

    It seems like the eybackup command keeps a list of the tables before the actual backup takes place. While backing up, if a table that's on the list goes missing for various external reasons, the backup process just hangs and takes down the MySQL server, consequently resulting in downtime. Is there anyway to tell the backup command to just skip the missing tables?

    Edited by developers
    Comment actions Permalink
  • Avatar
    Pasan Chamikara

    Hello developers,

    For a backup process, a temporary record is created and is stored in the /tmp directory. In case of incomplete backups it won't be taken into consideration under eybackup commands for restore.

    If there is a schema change, it would only hinder a single backup which follows the deployment, and it would recover automatically after that deployment.

    So it would automatically be aligned with the new db schema in the backups afterwards.


    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk