Wednesday, August 28, 2013

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.



Introduction


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 How to automatically backup and optimize WordPress database

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

</Files>


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 How to automatically backup and optimize WordPress database


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 How to automatically backup and optimize WordPress database


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 How to automatically backup and optimize WordPress database


Source and references



Summary


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.




Original post from Zemalf's Website optimization blog:

How to automatically backup and optimize WordPress database








via Make Your Own Website Tools Tips Tricks http://feedproxy.google.com/~r/Zemalf/~3/yF14qEqoHko/ Best WordPress Hosting

No comments:

Post a Comment