How to Backup a WordPress Database

fox-mascot

It’s always good to have current backups of your WordPress database archived at your home computer or on an external medium (USB drive, CD, DVD). Should your site be attacked or should your hosting server’s hard drive crash, you can be up and running with your site again in no time.

There are two approaches to database backups in WordPress. You can backup your database manually or automatically. I prefer the auto-backup approach simply because I tend to be forgetful.

Anyway, I’ll show you both approaches and you can decide for yourself which one to use.

Manual Backups

Via Control Panel

To create a manual backup of your database, log in to your hosting company’s control panel.

Navigate to your SQL database or list of SQL databases and click on the Backup link or button located next to the database you wish to back up.

hosting-control-panel

Hosting control panel

Once you click on Backup, your database will download to your local hard drive.

Via phpMyAdmin

If your hosting company doesn’t offer a backup solution as explained above, there is another way to backup your database.

Log in to your phpMyAdmin control panel.

In the left pane of the window, select the database you want to back up. Then, in the right pane of the window, click on the Export button.

Choose an Export Method (default is Quick) and an export Format (default is SQL) — then click Go.

phpMyAdmin exporting tables from database

phpMyAdmin exporting tables from database

Your database will then download to your local hard drive.

To make the download much faster, you can select the Custom export method and in the Compression section select gzipped.

Export database compressed with gzip

Export database compressed with gzip

A compressed SQL database file takes up about 10-15 times less space (and download bandwidth) than an uncompressed one.

Tip

If you really want to save hard drive space, though, unzip the downloaded SQL database file, then compress it again using 7-Zip‘s 7z format. It compresses the file much better and you will save 2-3 times more space than with zip or gzip.

Automatic Backups

All good hosting companies create automatic daily database backups for their clients. Some keep the backups for one day, some for a week, some even for a month.

But in case that your hosting company doesn’t auto-create backups, here’s how you can create them on your own.

WP-DBManager

Install and activate the WP-DBManager plugin by Lester Chan.

Go to WordPress Dashboard > Database > DB Options.

Database Options

Database Options

Enter the settings that will work in most cases are:

…and click on the Save Changes button.

If these settings are not working for you, contact your hosting company to give you the correct locations.

Backup Database with WP-DBManager

After the database options are all set, go to WordPress Dashboard > Database > Backup DB.

You should see no errors (listed in red), only correct settings (listed in green):

backup-database

Backup Database — Checking status

But if you encounter an error such as this:

I’m sorry, your server administrator has disabled passthru(), system() and exec(), thus you cannot use this backup script.

…contact your hosting company or server administrator to make the necessary changes.

Or make the changes yourself. In my case, all I had to do was log in to my hosting company’s control panel and enable the three functions mentioned in the error.

php-settings

PHP settings

Now you can check WordPress Dashboard > Database > Backup DB again and, if there are no more errors, proceed with the backup process.

Backup Database

Backup Database

To create a current backup of your WordPress database, scroll down and click on the Backup button.

You can also select whether the backup file should be compressed with gzip or not. Sometimes the compression won’t work. In that case disable it and start the backup process again.

The backup file will be created on your server. To download it to your local hard drive, go to WordPress Dashboard > Database > Manage Backup DB.

Manage Backup Database

Manage Backup Database

Select the backup file you wish to download and click on the Download button.

Scheduled Backups

WP-DBManager allows you to schedule automatic backups, too.

To set this feature up, go back to WordPress Dashboard > Database > DB Options and scroll a bit down.

Automatic scheduling of backup

Automatic scheduling of backup

You can then make WP-DBManager send each newly created database backup file to an email address.

Backup Email Options

Backup Email Options

When you are done with the options, click on the Save Changes button.

Now you don’t have to remember to back up your database. You can have it delivered automatically to your email inbox once a day, once a week, or once a month — depending on the amount of changes you make to the database (e.g. the number of posts you publish, the amount of comments you receive, etc.).

Other Uses of WP-DBManager

You can use the WP-DBManager plugin in other ways, too. It lets you optimize or repair your WordPress database, empty or drop tables, or run SQL queries.

However, it’s probably wiser to make changes to the database directly via phpMyAdmin and not through your WordPress Dashboard.

Liked this post?

Subscribe to our newsletter to receive early notification of new posts and deals:

Next Post »« Previous Post

Leave a Reply

Your email address will not be published. Required fields are marked *