A Case for Regular Database Backups

If you’re running a WordPress blog – – when was the last time you backed up your WordPress database? Ever?

As a hosting provider, we do full daily, weekly and monthly back ups of our clients account. We do this as a value-added service for our clients, and your host may very well do the same thing. Keep something in mind, however… no matter how awesome your host is – all web hosting providers experience ‘burps’ in the system. Also remember that your website/blog is hosted on a machine that sits in a building somewhere in the world. Machines are not infallable..neither are webhosts. While 99.999999999999999% of the time – we have no issues at all with providing our clients with their account backups when they need them… and I have yet to run across a time in the past 5 years where we were not able to provide a back up to a client – – however, even I keep my own backups.

Yep – I don’t rely 100% on my web host…and I AM my own web host! Mostly because I don’t fully trust machines 100%. That’s just the way my life usually goes. The minute I put my trust in the back up system willl be the day I lose everything for some reason — and I’d go grab a backup from our server and find that all my backups were corrupt for some reason. I don’t know – maybe you don’t have my luck.. call me unlucky, but that is how things in my life usually go. So, with everything in my life, I always have a Plan B. I like knowing that I have my own backups….and if my host has backups – then even better!

We don’t run into many time when the restoration of a full back up is required. Except for the odd time when a client manually deletes their entire /public_html folder because they “didn’t know what it was so I deleted it..”. However, we do run into occassions where we provide database backup restorations for our clients. (PS – don’t delete your /public_html folder … EVER. You need it.)

One of the top reasons? Comment and trackback spam. Once, we had a client who didn’t use any spam plugins to prevent spam at all. This client was tired of plugins, like Akismet or Spam Karma, throwing ‘legitimate’ comments into the spam bin – so they decided to just keep all comments moderated and approve those legitimate comments as they came through.

The unfortunate thing is that they ignored the comment spam and left those spam comments in their moderation queue.

Would you believe me if I told you we deleted 1/2 a million comment spams from just one account database at that time? It’s true – – it was 1/2 a million. It was then that Chris wrote a script that gets run on a monthly basis that searches through our servers looking for comments in database tables flagged as spam and over 45 days old and it wipes them out. However, comment and trackback spam can pile up real fast – – so every 30 days sometimes allows comments to pile up over the 10,000’s … but we do what we can. Wiping them out more often doesn’t always give our clients time to go through and weed out the legitimate comments.

Recently, we had three accounts that experienced the following error on their blog:

WordPress database error: [Can't open file: 'wp_comments.MYI' ]

The wp_comments table in the database became corrupt. In two cases, a repair of that table via phpMyAdmin fixed the problem quick and easy. In the third case – the repair did not fix it – and we had to restore a back up of the wp_comments table from their weekly backup.

Still not completely sure what caused this – except that each of these accounts had a few things in common:
1.) They each had over 20,000 comments flagged as spam in the wp_comments table
2.) Neither of the accounts was using Akismet – rather, they were either using Spam Karma or Bad Behaviour

Is the wp_comments table becoming so clogged with spam crap that it eats itself? Is the spam hitting their database so fast and furious that it implodes? We’re still investigating it. In the meantime, I can make a few recommendations for WordPress users:

• Use Akismet for spam protection. Use it in conjunction with other spam protection plugins if you don’t feel like putting all your comment spam fighting eggs in one basket. Akismet has proven to be a phenomenal tool at killing spam….dead
• Manage your spam. If you’re not using Akismet (Akismet automatically deletes spam comments every 15 days) – then make sure you’re not ignoring those comment/trackback spams that are sitting in your moderation queue or whatever queue you may be using for comment spam control. Don’t let them build up and fester in your database. Make it a habit to get rid of them on a regular basis to avoid database bloat
• Make it regular habit to make a backup of your WordPress database. Doing this couldn’t be easier – especially if you use The WordPress Database Backup Plugin – – for which you can find the support forums for right here. If you have CPanel, or whatever website admin. tool your web host gives you, you can create database, and full account, backups there. You can also create a backup if your web host gives you acces to phpMyAdmin – – some good instructions on how to do that can be found in the WordPress Codex.

Really, most web hosts keep regular backups of all the accounts they provide services to. As mentioned 9999 times out of 10,000 – your host will be able to provide you with a back up with no problem. I’m just a content control freak that way – and I like to have triple back ups of my back ups of my content.

Finally, if anyone has any insight into the wp_comments.MVI error that I mentioned – – I’m all ears.