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.

Logical Backup (db dump) Encryption Options

There are essentially three options for encrypting logical database backups:

  • When using eybackup 1.4.59 or higher (`eybackup --version`) the backup tool will automatically encrypt the backup at rest during storage to S3 using the AES256 Server Side Encryption option. This backup is automatically decrypted when downloaded by an authorized user making it a very user-friendly. The catch to this approach is that the backup is not encrypted-at-rest during backup creation or after downloading; this can be mitigated by using encrypted EBS volumes with your instances.
  • When using an eybackup version older than 1.4.59 it is possible to convert your backups bucket manually to enforce that all objects be encrypted at rest. Best practice would be to upgrade to the newer version of the stack and the tool, but if that is not possible please open a ticket with support so we can modify any buckets as needed.
  • PGP Encrypted Backups are "full" encrypted backups that go beyond "at rest" encryption. In this case you supply the platform with a public key so it can encrypt the backup while streaming it from the database to disk. These backups are a bit more complex to manage, block the restore functions of eybackup/eyrestore, and require that you keep safe a copy of the private key for unlocking the backups when needed. It is important to be aware that Engine Yard does not store this private key since that defeats the purpose of this solution; if the private key is lost the backup will not be recoverable.

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

    • 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_bucketfields (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.)

 

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.

 

Comments

0 comments

Article is closed for comments.