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: November 20th, 2013

This page describes:

By default, the database is backed up every 24 hours, starting around 1:10 am Pacific time (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.

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.

    Database backup files are discussed on this page.

  • Snapshots are incremental EBS copies of the /db volume. By default, these snapshots are made every night at 1:00 am Pacific time. Snapshots are used to rebuild the instance, build a slave 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.

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.

You can keep up to 100 backups. You can schedule backups to happen every hour, every two hours, four times a day, twice a day, or daily.

You don’t have to restart your environment to change the frequency or number of backups.

To change the number or frequency of database backups

  1. On the Environment page, click Edit Environment.

  2. Under the Backups heading, set the number of hours between backups and the number of backups to keep.

    Backup Options

     

    Note: The 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 on this page.

  3. Click Update Environment.

  4. Click Apply.

Configure Database Backups

You can configure the database to backup 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 this schedule:

    1. Create a custom Chef recipe that schedules 3 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

      Engine Yard recommends using 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

      Instead of storing this configuration file as a template with your custom Chef, have the Chef script create copies of the default file and then edit the keep and backup_bucket fields. This method will ensure that your custom configurations will 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 slave – 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 the application and database instance (for single server environment) or the master database instance (for a clustered environment).
  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 the application and database instance (for single server environment) or the master database instance (for a clustered environment).
  2. Type:
    sudo -i eybackup -e postgresql --new-backup

       or
     sudo -i eybackup -e postgresql -n

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.                        

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 05:50 PM