Backup the Database and Restore from the Backup?

Question

I am using WordPress 3 and would like to backup the database to my computer (a Mac). My webhost is using PHP safe mode, so that sometimes limits what plugins I can use. What is a good way to do a database backup? Is it possible to be automated? Are incremental backups recommended/easy? Obviously I then need to test restoring form a backup.

Answer 1

I've personally had limited success with the backup/restore plug-ins that are commonly available. Many times, the best backup plug-ins don't allow for a direct restoration from a backup file. So I do things manually. It's a bit more difficult, but far more reliable, too.

Backing up with phpMyAdmin

1. Log in to your host's control panel (it might be cPanel, it might be something else).
2. Find phpMyAdmin and go to your WordPress database
3. Click "Export"
   1. Make sure all tables are selected
   2. Click the option to save as a text file
   3. Export the database and save the exported file in a safe place.

Restoring with phpMyAdmin

1. Log in as before, go to phpMyAdmin, select your database
2. If you want a full restore (i.e. delete everything and roll back in your backup file):
   1. Empty all of your database tables
   2. Click "Import"
   3. Load your backup text file to restore all of your previous data

I've done this with 10 different sites. The only times it has problems is when the backup file is huge (>2MB). In those situations, you'll need to open your backup file in a text editor (Notepad or Wordpad) and copy-paste each set of SQL queries (I break it up by table) into the phpMyAdmin statement window. Even then, it's a fairly quick process and will work every time.

Disclaimer: If you've never used phpMyAdmin before, have difficulty reading SQL statements, or are working on someone else's site as a favor, please hire a professional with loads of experience to do this. If you accidentally delete your database or are working with a corrupt backup file there is nothing we can do to fix it.

Answer 2

I would start by reading the sections in the Codex on WordPress Backups, Backing Up Your Database, and Restoring Your Database From Backup, to get a good understanding of the issues involved.

There are a number of plugins written to automate the database backup process. Due to your webhost issue, you may have to just try a few to find one that works. I don't know of a plugin that can also restore from a backup - to do that, you'll probably have to use PHPMyAdmin or an equivalent tool to restore manually.

One thing I like about the automated backup options is that some (all?) of them have the option to send you a compressed database file (i.e. db123.sql.gz). I just create a filter in my email client to bypass my inbox and archive the message, so I can essentially access a revision history of my database. Alternatively, if you wanted to be sure the backups were still happening, you could not filter them to bypass your inbox, and manually archive/save each time.

Answer 3

There are several commercial backup options, as well. Backupify, BackupBuddy and VaultPress spring readily to mind.

Answer 4

If your webhost has cPanel, you might check there for a section on backup/restore. Two of my hosts have very easy to use tools, to do full or partial BU or restore, accessible through cPanel. Granted, these are not automated or incremental, but perhaps useful to you, nonetheless. Forgive me if you already know this, but the DB alone is not enough; you'll need the files and folders, as well.

Answer 5

Not a database backup per se, but you can export the contents of your site to a WXR file (XML format) and restore it on another installation. It's a bit simpler, and doesn't require access to your MySQL server or PhpMyAdmin. You do get options for what to export and what to import.

This functionality can be found in Import and Export under the Tools menu in the WP admin.

This has been useful to me when I want to get a dump from my live site and restore it on a local test version.

Answer 6

I would recommend you to try HotBackup plugin, which could create database backups, send it via email or upload to remote FTP, or even upload it to your Dropbox or Amazon S3 account. The plugin creates backups automatically, according to schedule settings. And additionally this plugin could restore your backup.