WordPress Hack #8: How to Optimize and Backup Your WordPress Database

Posted by jtpratt |24 Apr 08 | 11 comments

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.


11 Responses

  • Acupuncture/ 25 Apr 08 @ 10:16 AM

    Very useful post JT.

    Q
    Is DB-manager compatible with 2.5? Shows only 2.3 on the site, are you using it now with your 2.5?

    The diagnosis part is great! Noobs like me to WorPress don’t think about stuff like that. Very important! And a good plugin.

    Do you really see an advantage over backing up the database vs just exporting the information out of WordPress?

    I just zip my site every so often and export from WordPress. I also keep a DB backup because I’m paranoid =)] but it does seem a little redundant. So far I’ve only used the export/import of wordpress to set things back up. When I’ve toasted the DB. . . yes I have done it already. . . gotta learn some way. Anyway, I’ve just imported again and I’m right back to where I was.

    Just an idea for us noobs =

    Thanks for the DB education!
    C

    Acupunctures last blog post..The Science of Getting Rich by Wallace Wattles

  • Paul/ 28 Apr 08 @ 12:03 PM

    I really enjoy this wordpress hacks series. I’m no programmer and even I can understand and use this.

    On a related note (kind of =[] I Made My First BANS Sale!!!

    Just wanted everyone to know I made my first BANS sale today!

    I did the following to make it happen.

    1. Used Rochelle’s “The Niche Store Checklist has been Revised” to create the BANS site. http://www.nichestorestrategies.com
    2. Used Marks’s “More Free Build a Niche Store templates – Come and Get Em!” to build the site. http://www.thenichestorebuilder.com
    3. Used JTPratt’s “Watch Me Build a BANS Niche Site from Scratch!” to fine tune the site. http://www.jtpratt.com
    4 Used JTPratt’s “Best Practices for Setting Up a New WordPress blog in 60 Minutes or less” to set up a blog correctly. http://www.jtpratt.com

    Thanks to all the forum posts at BANS and all the people who commented on the sites listed above.

    Come take a look http://www.toolboxhero.com and make relevant comments at http://www.toolboxhero.com/blog. The blog is dofollow so go ahead and build some backlinks.

    Next, more backlinks, more BANS stores, and implement more ideas from Rochelle. Mark, and JTPratt!!

    Thanks again, Paul

    Pauls last blog post..Sears Normal Tool Warranty

  • GenuineBlogger » Blog Archive » Wordpress and Database Integrity/ 05 Jun 08 @ 9:04 AM

    [...] a nice article by JTPratt, his next step was installing WP-DBManager which will provide a new screen in your dashboard with [...]

  • Sid Software/ 25 Aug 08 @ 3:31 AM

    I have to agree that backing up your database is a pain, but very important. I read a post on a forum, that I frequent, today. From someone who got their blog hacked. It’s just much easier to recover from that if things are backed up.

    Sid Softwares last blog post..Best Windows backup software

  • Lighten The Load Of Your WordPress Blog - Part Two | Philaahzophy/ 27 Aug 08 @ 12:02 AM

    [...] don’t feel bad.  It didn’t mean anything to me either when I came across it over at J.T. Pratt’s Blogging Mistakes (currently my favorite blog about blogging).  You can follow the link to his excellent explanatory [...]

  • Darlene/ 10 Sep 08 @ 4:29 PM

    first off i want to thank you for the marvelous article…it is very clear and even i could “get it”….however, i have done everything you suggested and i am still loading slow….

    i have also taken all the text off of you widgets, deleted all on active plug ins…

    i have done everything i have read about other than the cashe and i am so unfamiliar with that it sort of scares me…

    could you please please help me….i have bugged my hosting company many times….and they said there is nothing else they can do….

    so any imput would be very appreciated.

    thanks
    darlene

  • admin/ 10 Sep 08 @ 10:03 PM

    @Darlene –

    Looks like you have multiple issues. I have had this exact same problem and fixed it. I think you have a plugin conflict and also you have massive code issues.

    This is what I would do…

    1. Disable all plugins
    2. Set the theme to default
    3. Take your July 15th post and edit it. Copy the text into Windows notepad. Use Ctrl-A to “select all” and copy, then go back to the post in your WordPress dashboard. Delete everything. Paste the text you copied from notepad. The problem is you’ve been writing your entries in Microsoft Word, copying the text and pasting it into your posts using the WordPress visual editor. You have to stop doing that – it’s bloating the bejeezus out of your code.
    4. Save the post, reload your home page

    At this point everything should be better. If it’s not, download all your plugins to your desktop in folder, then in FTP on your web server delete all the plugins in /wp-content/plugins. WordPress still looks in your plugin directory – even if they aren’t enabled.

    So now, what happens? Your blog should be light years faster. If it’s STILL not you may have a web host problem. There may be too many people on your web server or the MySQL (database server) might be overused. I mean, if you have the default them with no plugins your blog still takes 30 seconds to load, unless you have serious database corruption your web host is to blame. You already optimized your database – so it should be ok.

    At this point, I would dump your web host for someone like Hostgator. I use them for about everything, they work great, and you can have unlimited sites and databases for $15/mo or less. The servers are fast, tech support is great, and I’ve never had any issues with anything – EVER.

    Post again on your success (or failure) once you’ve tried these steps…

  • Darlene/ 10 Sep 08 @ 11:23 PM

    hi there…
    thanks for your comments…i have done the plugin thingy…i am afraid to do the default as i didn’t build that part of my website….and don’t know if i could get it back without a lot of tears….and i have really spent them all on this issue….

    i do want you to tell me more in detail about my posting behavior….and you are right in what i am doing….and i know for you you probably felt it was in detail…but i need it sort of step by step….how do i write my articles….in notebook and then copy them into wordpress??? how do i get the various styles i like in my writing….do i have to give that up???…

    also i finally got a senior service rep and he went to the top and found out that one of data servers (the second one or something) was/is the problem and my issue is suppose to resolved by morning…i made 6 calls to them and asked about the server thingy each time and each time they just repeated that the server load was checked and it was ok….evidiently there are two servers and they didin’t take it that far….

    so will lst you know what happens in the morning…and i really, really appreciate your answer and thoroughness in helping me…you are great!!!!!

    namaste,
    darlene

  • Charles/ 11 Sep 08 @ 7:50 AM

    Darlene,
    I checked your site also. I don’t think it has anything to do with the plugins or themes.

    If you watch your browser working you will see that it is waiting to connect to the domain and once it does it loads the page rather fast. Therefore, the problem. . . in my opinion is your host.

    Hostgator is a good host I also like Hawk Host – very good and low cost.

    One of my sites responds like yours at times. . . thus it isn’t my plugins or theme. It’s whatever is going on with your host at that time.

    Good luck

  • admin/ 11 Sep 08 @ 8:46 AM

    @Darlene: What you need to do is never write posts in anything other than WordPress from now on. Make sure you are using the “visual” editor (the one with bold, italic, font size, bullets, etc). If you aren’t – edit your profile under “Users” and enable it.

    If there are things in Word you want to do that WordPress visual editor won’t – you should be doing those in web pages anyway. Microsoft Word adds in 50X more code (most of it useless) for every page you write – making the load times slower. Not a ton slower, but slower.

    To fix the existing posts you just open up Windows Notepad by going to start->Accessories ->Notepad. Just copy your posts there. Select all, and then copy again, and paste back to your post and save.

  • eddai/ 23 Mar 09 @ 10:09 PM

    Hi, maybe long time after post created but i have High Resource Usage for my site. and my site moved to Cloud Server…
    Browsing phpmyadmin and seeing DB for my main site, though i use wordpress i have encountered that there are also joomla fileds in the same db. is this normal ?
    may this cause High Resource Usage ?
    Does anything happen if i delete all joomla related fileds ?
    will be happy if you could guide me

    eddais last blog post..Monday Links Roundup #1

You must be logged in to post a comment.