Database Disaster Recovery: A Step-by-Step Guide
So my hosting provider (Dreamhost) somehow, accidentally deleted my databases. I sure was glad to have a backup (as they did not)! Unfortunately, the most recent backup that I found wasn't as current as I would've liked- so I lost a lot of content. It's just good general practice to take database snapshots as often as possible and archive them somewhere off-site. Hosting providers, sometimes, provide tools to do this. You can also do this manually whenever you like. The best practice by far, especially if you have a lot of disk space at your disposal, is to create a simple script in cron and run it nightly. Since user tools vary from host to host and not everyone has access to tons of disk space, especially on shared hosting environments, I will be detailing option #2- manual disaster preparedness and recovery. We'll be backing up a MySQL database so you will need to have access (login and password) to said database. You can accomplish this through a command-line interface (CLI) or through a graphical user interface (GUI). We will be using the popular open source database administration tool known as phpMyAdmin. So let us get started.
Backing up your MySQL Database
1. Start phpMyAdmin. Do this by opening the URL of your MySQL database server in your browser, e.g. dbname.domain.com. A user name and password prompt appears.
2. Enter your MySQL user name and password. The phpMyAdmin user interface appears.
3. On the left pane, click the popup menu item (databases) A list of MySQL databases on this server appears.
4. Select the datbase which you wish to back up from the list. Information on that database appears in the right pane.
5. On the tabs across the top, click Export. A pane marked "View dump (schema) of database" appears.
6. Underneath the list marked "Export", click the link Select All. All the table names will be highlighted.
7. In the radio button set below, make sure SQL is selected. This selects the output format.
8. In the section marked "Structure:", check the box marked Structure:. Within this section check boxes marked Add AUTO_INCREMENT value, and Enclose table and field names with backquotes.
9. In the section marked "Data:", check the box marked Data:. Within this section check boxes marked Use hexadecimal for binary fields. If you are backing up a large database, uncheck extended inserts as when you restore the backup the server may not accept such long SQL commands. If you plan to apply the backup to a database with a different structure (e.g., you applied a mod to PhpBB) or to an updated version of a web application, check complete inserts, otherwise leave it unchecked. Leave other boxes unchanged.
10. Check the box marked Save as file.
11. In the box marked "File name template:", fill in the name you want the backup file to have. phpMyAdmin expands certain abbreviations in this string: __SERVER__ expands to the server name, __DB__ expands to the database name, and a string like %Y%m%d is expanded according to the rules of PHP's strftime function.
12. Click Go button. phpMyAdmin will download a SQL file named as you specified in Step 11. A file download dialog from the browser appears. Specify where on your local host you want to store the file.
Alternatively, you can accomplish the same result by using the mysqldump command via shell access by logging into your server via SSH or Telnet (you will have to have an already-created shell account on the remote server):
host name: servernamewhereyoursiteresides.yourhostingprovider.com login as: username (your account username)
Type the following command using your database user:
mysqldump --opt --user=username --password=password --host=yourMySQLHostname dbname > output.sql
Now that you have a backup of your database, store it somewhere off-site. Hopefully, you'll never need it but in case you do, continue on.
Restoring a MySQL Backup
1. Upload yourbackupname.sql to your server via FTP. This doesn't have to be in a web accesible place but remember where you put it.
2. If you haven't created a database yet, do that first (either via phpMyAdmin or CLI). If you are restoring your backup to an existing database, it is recommended that you empty it first (again, either via phpMyAdmin or CLI).
3. Connect to your server using SSH.
4. You will see a mysql command line for your database. Run that command with < /path/to/backup.sql appended, for instance:
mysql -u dbuser -p -h dbhost.yoursite.com dbname < /path/to/yourbackupname.sql
Tada! Your database has now been successfully restored!
Since I'm running a Drupal site, and want to avoid seeing MySQL 'Inner Join' errors, the final step is to run the 'update.php' script. You can do this by simply logging into your site with administrator privileges and going to yoursitename.com/update.php. Backing up your database, as any system administrator will tell you, is truly a best practices approach to managing your site. It should be run as often as you feel comfortable doing it- the more often, the better.