Restore from a previous DB backup

Objective

Detail the procedure needed to restore an older database backup, not the default (which is the most recent snapshot).

Table of Contents

Background

By default, the most recent backup file in the backups directory will be used to restore the database.  In some instances this may not be the desired effect.  This tutorial provides the steps needed to restore an older backup (not from the default most recent snapshot).  There are two different scenarios provided.  You should align with the one that best suits your needs. 

1. Restore from a known snapshot
2. Restore to a standalone database Server first

The time required to perform a restoration of the database will vary depending on its size. 

Disclaimer

As always, proceed with caution when restoring the database to master and/or slave Servers.  If you do not know precisely what you are doing, your actions could produce incorrect results.  This could be devastating, particularly in a production environment. 

Scenario 1 - Restore from a known snapshot

In this scenario, you are absolutely certain which snapshot 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 following steps.  (Please read all of the steps prior to performing any of them to make sure this is what you need to do.)

1. Clone your existing Master DB server
   1. Rename it something descriptive so you can distinguish it from other Servers.  (e.g. Clone Master DB)
   2. Make sure that its Input, INIT_SLAVE_AT_BOOT = false
   3. You must edit the following Inputs in accord with the machine tags of the Snapshot you will restore from (Important!  Use machine tag information not Snapshot name information.)
      1. If and only if the value for DB_EBS_PREFIX (e.g. if your snapshot is named 'my-prefix-slave-201106140340' the prefix is 'my-prefix-slave') is not correct, use the DB_RESTORE_PREFIX_OVERRIDE and set it to the rs_backup:lineage tag value.  For example, if the Snapshot you want to restore has the following tag setting "rs_backup:lineage=mystripe", then set DB_RESTORE_PREFIX_OVERRIDE to "mystripe".  (If DB_EBS_PREFIX is correct, "mystripe" in our example, then leave the override setting as "ignore".  There is no need to override the lineage while restoring.)
      2. Set the OPT_DB_RESTORE_TIMESTAMP_OVERRIDE to the rs_backup:timestamp machine tag of the correct Snapshot you are restoring from.  For example, if the tag is rs_backup:timestamp=1303852171 then you would set the timestamp override to "130385217"  This setting uses Epoch time and will be unique.
      3. Note:  Leave the OPT_DB_RESTORE_VERSION_CHECKING set to "yes".  Rarely (if ever) will you need to change this setting.  (Only if you are migrating between version 5.0 and 5.1 databases.)
2. Launch the Server (new master)
   1. Once operational, run the "DB EBS Restore and become Master" operational RightScript
   2. At the end of this RightScript a backup should have been for you
      1. Check progress from the Events pane
      2. Confirm completion from :  Clouds -> CloudName -> EBS Snapshots.  The current snapshot should progress from "pending" to "completed 100%"
      3. Note:  If needed, you can take a "manual" backup.  To do so run the "DB EBS Backup" operational RightScript
   3. When the backup has successfully completed, Edit Inputs.  Remove the "DB_RESTORE_PREFIX_OVERRIDE" value you set earlier.
3. Clone the Slave
   1. Make sure that INIT_SLAVE_AT_BOOT = true
   2. Launch the Slave
   3. Make sure the Slave is operational and the Volumes mounted ok.  (Clouds -> CloudName -> EBS Volumes.  The status should eventually change from "In-use (attaching)" to "In-use (attached)".
4. Verify that the Master and Slave are up and running ok.  There are several ways you can do this.  Such as:
   1. Checking Master or Slave Database status
      
   2. Verify that manual and/or scheduled snapshots are completing ok
   3. Check that your front end Servers are up, running and serving your application ok (check from your browser)
5. Important!  Name the current Master and Slave DB Servers appropriately.  If you fail to do this, confusion will certainly occur!  For example, assuming your old master and slave names don't conflict, you could name them: Master DB and Slave DB, or Production Master DB and Production Slave DB.
6. Cleanup tasks
   1. Terminate the old master and slave Servers
   2. Delete the old master and slave from your Deployment

Important!  If, for instance, you end up performing the above steps and restore from a snapshot that is 4 hours old, when you are through, your database will be 4 hours old.  That is, any and all updates after that snapshot will have been lost. Again, it is important to understand exactly what you need and why before restoring from a previous database backup.

Scenario 2 - Restore to a standalone database Server first

In this scenario, you may not know with complete certainty which snapshot you need to restore from.  Hence, to find out you spin up a standalone database Server to restore what snapshot you think is correct and check the database to verify the database contents.  (Please read all of the steps prior to performing any of them 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 database snapshot to complete Scenario #1 with.

1. Import the latest version of the "MySQL EBS Toolbox" ServerTemplate.  (Note:  Currently this ServerTemplate is beta, but it is tested and will work fine for this restoration process.)
2. Clone your current Master DB Server. On the Clone:
   1. Verify that the Input INIT_SLAVE_AT_BOOT = false
   2. Important!  Make sure you name it clearly to avoid confusion!  For example:  TEMP CLONE Master DB
3. Launch your temporary Clone DB Server.  Once operational, go to its Scripts tab.
4. Run the "DB EBS Restore" RightScript
   1. Expand the the "Any Script" section
   2. Select the "DB EBS Restore" RightScript from the drop down menu
   3. Select the "Run Script" action button to execute
      1. Credentials should be populated correctly for you
      2. DB_RESTORE_SNAPSHOT - Set this to the Snapshot you think you will want to restore eventually to your live database
      3. OPT_DB_FORCE_RESTORE - Set this to "false" for the initial run through this process.
         1. Additional runs through this process have this set to "true"
         2. 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.  (Not the desired result.)
5. SSH into your cloned standalone Server.  Connect to the database and manually verify its contents.
   1. If correct, use this Snapshot and begin Scenario #1 above.
   2. If the restored Snapshot is not the correct one:
      1. Repeat the process (Steps 4 & 5 of this Scenario #2)
      2. This time set OPT_DB_FORCE_RESTORE = true when running the DB EBS Restore RightScript
6. Cleanup
   1. Terminate the temporary standalone database Server
   2. Delete the temporary standalone database Server