How to automatically backup and optimize WordPress database

Summary: Learn how to to automatically optimize and backup your WordPress database, so the DB stays fast and you don't lose all your hard work if something bad happens.


Backing up the WordPress database is the smartest thing you can do and one of the first things to take care of after setting up a new WordPress blog. With the instructions in this post, you don't have to worry about losing everything you've written and done to your blog if something breaks. And best of all, the backups are automatically emailed to you, so you'll have them even if the whole server blows up.

While taking care of the backup, we'll also automatically optimize the WordPress database by removing unnecessary data, making the database cleaner and smaller, which in return makes database calls faster.

The speed gain of WordPress database optimization is very small, even non-existent, but since we get the DB-optimization as a bonus while having a backup, installing the WP-DBManager -plugin to optimize and backup your WordPress Database is first of the 4 WordPress optimization steps in the WordPress Speed Challenge.

1. Install the WP-DBManager -plugin

I have chosen and recommend a plugin called WP-DBManager for optimizing and backing up the WordPress database. First, because it works like a charm, and second, because it's automatic after setting it up once. For backup, the other awesome option is the WB-DB-Backup -plugin, but as said, I prefer the WP-DBManager because it also optimizes the database and it can be scheduled for automation.

To install the WP-DBManager, go to your WordPress Dashboard (admin area)

  1. Click 'Plugins'
  2. Click 'Add New'
  3. Type in the search field: "WP-DBManager" (without quotes)
  4. Click 'Search Plugins' -button
  5. Check the results for WP-DBManager and click install now

How to install WP-DBManager -plugin for WordPress
WP-DBManager will automatically create a folder called backup-db in the wp-content folder if that folder is writable. If it is not created, create it and CHMOD it to 777. See WP-DBManager installation instructions for more info.

The plugin will also ask you to create a .htaccess -file to the backup-db directory, which you can do by copying the htaccess.txt from the plugin dir to the backup-db dir, and rename it to .htaccess there. The .htaccess -file is needed to ensure no-one can access the backed-up files but you. Here's what should be inside the .htaccess -file placed in the backup-db dir:

<Files ~ ".*\..*">
order allow,deny
deny from all

WP-DBManager does not backup the files in your server, just the WordPress MySQL database.

2. Backup the WordPress Database

After getting the WP-DBManager -plugin installed, let's create the first backup manually...

Go to your WordPress Dashboard (admin area)

  1. Click 'Database'
  2. Click 'Backup DB'
  3. The Backup Status checks should be green ("Excellent. You Are Good To Go")
  4. Choose to GZIP (compress) the results if you want
  5. Click Backup

How to backup WordPress database using the WP-DBManager -plugin

3. Optimize the WordPress Database

Optimizing database will clean and optimize the WordPress database, removing unneeded data, making the database calls faster (because there is no unnecessary data to go through).

If you go to Database (Database), you can see the overhead on the right hand side, and at the bottom of the table, you'll see how much unneeded data you can remove right away (by doing the optimization).

Here's how to do the optimization manually...

  1. Click 'Database'
  2. Click 'Optimize Db'
  3. (Scroll down if needed) Click 'Optimize'

How to optimize WordPress database with WP-DBManager -plugin

4. Configure Automatic Backup and Optimizations of the WordPress Database

After doing backup manually once and optimizing the database by hand, we can now automate and schedule the WordPress database backup...

Go to your WordPress Dashboard (admin area)

  1. Click 'Database'
  2. Click 'DB Options'
  3. Ignore the advanced options on top, look down to 'Automatic scheduling'
  4. Set Backup frequency, e.g. once a day
  5. Enter an email address you want the backup to be sent to
  6. Choose if you want to GZIP the backup file
  7. Adjust the optimization schedule, once a week is fine
  8. Click 'Save Changes' -button

How to automatically backup and optimize WordPress database

Source and references


The first thing one must do after WordPress install and configuring the settings, is to setup automatic backups for the database. In case you have a WordPress blog, and you have not done this, you MUST DO THIS RIGHT NOW. Here are the action steps:

  1. Install the WP-DBManager -plugin
  2. Backup the WordPress Database
  3. Optimize the WordPress Database
  4. Configure Automatic Backup and Optimizations of the WordPress Database

Do this, and you have backed up your WordPress database, optimized it for faster usage, and automated the backups and the optimization for the future - ensuring both tasks are done automatically in the background.

Backing up and optimizing the WordPress database is more important for security (we have a backup to go back to), than speed, since the speed gain of the WordPress database optimization is quite minimal - although on a cluttered, old blog, optimizing the database can significantly speed up the database calls.

As mentioned, setting up the automatic backup and optimization of the WordPress database is the first thing to do after a fresh WordPress install. And it is essential to backup the database before making any changes into the database or the blog.

Posted by

Topic: WordPress Optimization
Tags: , , , , ,

If you enjoyed this post, sign up for updates (it's free)

Feedback, questions and comments are also welcome on my Facebook page