Do you know how to optimize and backup your WordPress database? Most people don’t – until it’s too late! I’ll show you how to protect yourself and keep your blog running smooth!

This is the next installment of 30 WordPress Hacks in 30 Days, and though we may be just a day or two behind in the deployment – we are definitely not short on content! Be sure to view all 30 days to make the most of your WordPress blog!

Today I’m going to write about one of the most important and most overlooked things you need to know as a WordPress site owner – your database. I would say the greater percentage of bloggers, even if they are bit geeky or techie, aren’t “database savvy”. It’s no different than driving a car but not knowing much about the mechanics or maintenance of it. You don’t know what nobody ever told you – so consider today your lesson in “how to maintain and backup your wordpress database”. Maintenance in necessary to ensure the backend of your site runs smooth, backup is necessary in case you have a disaster, move hosts, get corrupted files, or (god forbid) you screw it up hacking it yourself!

It’s not hard, in fact it’s about as painless as changing your oil in your car. What I’m going to show you is how to do “preventative maintenance” for your WordPress DB (database). People who are hackers, or programmers, or uber-geeks either login to the command line in telnet (very geeky!) to maintain their telnet database. More popular is to login to your web host control panel for your site and use myPHPAdmin to graphically administer and maintain your WordPress mySQL database.

There are two WordPress plugins that I use that help me with WordPress maintenance. The first is the Diagnosis plugin. This plugin doesn’t “do” anything except give you information about your site and the web host you are on. The first reason this plugin is invaluable is because it gives you a reference for information you might not ordinarily know. Do you know what version of PHP you have? Do you know your mySQL encoding type? Do you know your mySQL IP address and port number? These are all things you might need to know for installing script or troubleshooting your site in the future. The main reason I have Diagnosis installed is because it gives me 3 pieces of information about my database load that I check out either once a week, or if my WordPress blog starts acting funny (with errors or database connection).

diagnosis mysql load example

In my WP dashboard I go to “Dashboard -> Diagnosis” and look at the mySQL load section to briefly check out the load. The more traffic you have, and the more database intense your pages are, the higher the load will be. The amount of beating a mySQL database server can take is much different on a “shared host” vs. a VPS (virtual private server) or dedicated box. Shoemoney recently wrote about the fact that his WordPress blog recently had to go to it’s own decidated server box, but it probably gets 1,000 times the traffic I do daily. The most important figure here to me is the “connection success rate”. If it’s not 99.9% – you have a problem. Either you have more traffic than your server can handle, too many connections, or something corrupted with the database.

I think the most common problem that happens is your WordPress site loses connection with the DB and you get some kind of WordPress error from your site (in a browser) that says “error connecting to database). One thing you may not realize is that when you get “shared web hosting” for $3.99 – $24.99 per month the “shared” part of the hosting is the web server and the database server. In other word, www.yoursite.com is physically on a server sharing that computers resources with hundreds of other web sites. Your mySQL database is more than likely on a different server, and all it does is host databases. Even if your web server resources are underutilized, your database server might be maxed out by a very busy (or inefficient) site. If you get database errors or have connection problems the first thing I would do would be to see what your connection success rate is, and if it’s bad call your host and ask about the “health” of the mySQL server you’re using. You might find (like I have on some occasions) that another customer (on that DB server) is killing your site.

–~~~~~~~~~~~~–

Page 2: How to Maintain a WordPress Database

Lester Chan comes through yet again with the “WP-DBManager WordPress plugin to manage your WordPress database. Once you install and enable the plugin you’ll find that in your dashboard you now have Write, Manage, Design, Comments, and “Database”. The first thing I look for is excessive “overhead”. Just past your database information is a section labeled “Tables Infomation”. You’re looking for any tables that (in the rightmost column) have “overhead”.

database tables information

What is database overhead? First I’ll give you the technical answer, then I’ll explain: “OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.”

Basically a table in a database is kind of like a spreadsheet that stores information. That infomation is stored in “rows”, and over time rows are edited and deleted. When that happens “overhead” occurs, and the table isn’t as efficient. You know how you have to defragment a hard drive? Well, “optimizing” a database table is like that. So if you have some db table “overhead” it’s time to optimize the table. Even if you have no overhead, you should do this once or twice per month.

How do you “optimize” a wordpress database? Just click on the “Optimize” tab at top of the “Database” screen in your WordPress dashboard (that you have since installing the WP-DBManager plugin), and then at the bottom of the page click “optimize” again. You should get a page that looks something like this after optimizing with a success message at the top:

Optimizing wordpress database

If for some reason when you optimize your database there is an error of any kind, then you can click on “Repair database” to see if it can be fixed. It works the exact same way, and when complete will tell you whether it was successful or not. Again – you should optimize your wordpress database once per month at minimum, and more often if your blog is very busy. In the next step, you’ll learn how to automatically do this.

–~~~~~~~~~~~~–

Page 3: How to Backup a WordPress Database

Backing up your wordpress database could be the single most important thing that you do. The WP-DBManager will allow you to “set it and forget it”, so there’s no excuse since all you have to do is set it up the initial time. You may need that DB backup someday is you choose to move hosts, or your database server dies, or even gets hacked or corrupted. All things beyond you and your web hosts control.

Go to “DB Options” and change the “path to backup” or “maximum backup files” if you need too. By default your database backups will be stored in your /wp-content/backup-db directory. You may choose to store them somewhere else. The default number of backups is 10, and you can make that lesser or greater to your liking as well.

database backup options

Probably the best feature of this plugin is the fact that you can automatically schedule your backups and you can have them sent to you in email – so you always have a copy on your local PC. In addition – the same section has an option for automatically optimizing your WordPress database tables, which you can schedule by the day, hour, week, or month.

auto backup database options wordpress

There are many, many plugins available that you can use to optimize and backup your wordpress database. I prefer the two I showed you today, and the WP-DBManager not only is great for the auto-scheduling options, but Lester Chen is very active in updating his plugins to work with the latest versions of WordPress. I had no problems using either of these plugins in either WordPress 2.3.3 or 2.5.

If you have anything to add to make this article better, or a question – please comment now!