Backup the Database and Restore from the Backup?

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.

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.