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.