To restore a MySQL database from a previous backup (set of snapshots).
Table of Contents
By default, the most recent backup (set of snapshots) of an EBS stripe will be used to restore the database. However, in some scenarios, this may not be the desired effect. For example, if you just found out that your database has been corrupted, even if you restore from the most recent backup, that data might be corrupted as well. In such cases, you may need to restore the database using a specific backup that you know is clean and not corrupted or you may need to do some testing of your backups to determine which backup you need to use for a database restoration.
There are several different ways to perform a database restoration:
- Restore from a known (specific) backup
- Restore from an unknown backup
This tutorial assumes will explain the necessary steps to perform a desired database restoration.
Note: The time required to perform a restoration of the database will vary depending on its size. As always, proceed with caution when restoring the database to master and/or slave Servers, especially in live production environments.
Scenario 1 - Restore from a known (specific) backup
In this scenario, you are absolutely certain which backup (set of snapshots) you want to restore from. There is no need to verify the contents of the snapshot prior to performing the restore. If this is the case, you can follow the steps below. (Please read all of the steps prior to performing any of them to make sure this is what you need to do.)
- Clone your existing Master-DB server
- Rename it something descriptive so you can distinguish it from other Servers. (e.g. Clone Old Master DB)
- Make sure that its Input, INIT_SLAVE_AT_BOOT = False (You do not want the server to become another Slave-DB.)
- Set the lineage and timestamp inputs to specify which backup will be used to restore the database. For example, let's assume you want to restore a database using the following snapshot, mystripe-master-2010122212345. Under the Server's Inputs tab, edit the following inputs,
- You should only provide a value for this input if you want to use a snapshot that's of a different lineage. For example, if you're setting up a Staging Deployment, you'll want to use a different lineage name than your production environment, but for setting up your staging database for the first time, you'll probably want to use a recent snapshot of your production database. You will need to specify the timestamp that's defined by the snapshot's tag (not name). For example, if the snapshot's tag is rs_backup:lineage=otherlineagename, you would specify otherlineagename for this input.
- If you run the database restoration script and this input is set to ignore, the most recently completed backups will be used. However, if your database became corrupted, you'll need to revert your database back to a specific (uncorrupted) snapshot. Set this input to specify a specific timestamp. You will need to specify the timestamp that's defined by the snapshot's tag (not name). For example, if the snapshot's tag is rs_backup:timestamp=1303613371, you would specify 1303613371 for this input.
- There will be one snapshot per volume in the stripe where each one will have the same name (e.g. mystripe-master-2010122212345) but each snapshot will have a unique aws snapshot id (e.g. snap-32bed55a)
- Launch the Server
- Once the sever is operational, run the "DB EBS Restore and become Master" operational script.
- By default, a backup will be initiated when you run the restore script. Before you launch a new slave, you must wait for the current snapshot or set of snapshots to be 100% complete.
- You can track the progress under the Events pane or go to Clouds -> AWS Region -> EBS Snapshots. All snapshots should progress from "pending" to "completed 100%".
- Once the backup snapshots have been successfully completed, edit the Server's inputs (Inputs tab) again. Remove the override inputs that you previously defined.
- Clone the Server
- Except this time, make sure that INIT_SLAVE_AT_BOOT = True because you now have a set of EBS Snapshots that you can restore from.
- Launch the server. When the server becomes operational, it will be your new Slave-DB.
- Verify that the Master-DB and Slave-DB are up and running OK. There are several ways you can do this. Such as:
- Checking master or slave database status
- Verify that manual and/or scheduled snapshots are completing OK
- Check that your front end Servers are up, running and serving your application OK (check from your browser)
Important! Name the current Master and Slave DB Servers appropriately. If you fail to do this, confusion will certainly occur!
- Cleanup tasks -- Warning! Be sure to use discretion!
- Terminate the old master and slave Servers
- Delete the old master and slave from your Deployment
Note: If you perform the above steps and restore from a set of snapshots that are 4 hours old, your database will be 4 hours old. (i.e. Any updates to the database since the backup will have been lost.
Scenario 2 - Restore from an unknown backup to a standalone database Server first
In this scenario, you may not know with complete certainty which backup (set of snapshots) you need to restore from. Perhaps you're trying to find the most recent backup that is not corrupted. Hence, in order to find out which backup you need to use, you launch a standalone database Server to restore the set of snapshots that you think are correct and check the database to verify the database contents. (Please read all of the steps prior to performing any steps in order to make sure this is what you need to do.) When you have successfully completed all of these steps, you should have located the appropriate set of backup snapshots that you need in order to complete Scenario #1.
- Import the latest version of the 'Toolbox' ServerTemplate that matches the ServerTemplate that you used to initially set up your redundant MySQL database.
- MySQL EBS v2 => MySQL EBS Toolbox v2
- Database Manager for MySQL 5.x => Database Manager for MySQL 5.x Toolbox
- Clone your current Master-DB Server. On the cloned server:
- Verify that the Input, INIT_SLAVE_AT_BOOT = False
Important! Make sure you change the name of the cloned server to clearly avoid confusion! (e.g. TEMP CLONE Master DB)
- Launch your temporary Clone DB Server. Once operational, go to its Scripts tab.
- Run the database restoration RightScript.
- Expand the the "Any Script" section
- Select the appropriate restore script.
- For MySQL EBS v2, select DB EBS Restore
- For Database Manager for MySQL 5.x, select DB EBS restore stripe volume
- Select the "Run Script" action button to execute the script. You will need to provide values for missing inputs. Credentials should be populated correctly for you. Use the override inputs to select a specific backup.
OPT_DB_RESTORE_LINEAGE_OVERRIDE - You will need to specify the timestamp that's defined by the snapshot's tag (not name). (e.g. rs_backup:lineage=otherlineagename)
OPT_DB_RESTORE_TIMESTAMP_OVERRIDE - You will need to specify the timestamp that's defined by the snapshot's tag (not name). (e.g. rs_backup:timestamp=1303613371)
OPT_DB_FORCE_RESTORE - Set this input to "False" the first time you run this script. For additional runs of this script on the same Server, set this input to "True" because you'll want to overwrite the existing database. Note: If you set this to "True", make extra sure you are on the cloned standalone Server! Otherwise, you could change your database on a live production Server, which is probably not the desired result.
- SSH into your cloned standalone Server. Connect to the database and manually verify its contents.
- If correct, use these snapshots and begin Scenario #1 above.
- If the restored set of snapshots are not correct:
- Repeat the process (Steps 4 & 5 of this Scenario #2)
- Except this time, set OPT_DB_FORCE_RESTORE = True when running the database restore script.
- Cleanup-- Warning! Be sure to use discretion!
- Terminate the temporary standalone database Server
- Delete the temporary standalone database Server