How to Maintenance Your WordPress Database
WordPress is database-driven. Hence, the additional security and resource concerns you see me mention on a pretty regular basis. To put it simply, your database is the heart of your WordPress website.
So we want to make sure that we keep the database running your website humming along and error-free. This helps with site performance and SEO as well.
For non-techies, the thought of doing anything with a database can be pretty scary, right? So with that in mind, I'll make it super easy for you to get a process in place without having to be fearful.
Today we'll cover the very basics you need to do. And I'll expose you to some options for your consideration. Most once set up, are on auto-pilot. The rest are a few simple tasks that you can do regularly to avoid any headaches.
Why Maintenance Your Database?
The tables in your MySQL database that WordPress uses will, over time, become sluggish as data is added, removed, and moved around. Regularly optimizing the database tables will help keep your site running as efficiently as possible. The thing is, this won’t happen by itself.
Did you know that WordPress creates a revision of that post or page every time you save a new post or modify your pages? So subsequently, if you edit a post 5 times, you might have 4 copies of that post as revisions.
There might also be tons of spam and un-approved comments in your comments table. This quickly adds lots of rarely-used data to your database tables, making them unnecessarily bloated and slower to access.
Over time this dings your site's performance. With Google stating sites should load in 2 seconds — every little bit helps.
Backing Up Your Database — Including Off-line
Did you know that in 2020 approximately 30,000 sites are hacked every day? The other benefit of having a database maintenance (aff) system in place is that maintenance includes additional backups.
Backups that will come in handy if you do get hacked. We're talking backups above and beyond those daily or monthly backups provided by your web host.
If you follow my articles, you know I'm big on security, and if you are a smartie, you most likely have already hardened your website. This includes archiving backups you can rely on if something goes haywire.
PRO TIP: Google also urges website operators to sign up for its security notifications in the Google console. Doing so allows you to get notified if anything happens that you may not be aware of.
Your webhost should offer daily and on-demand backups. Some believe that is sufficient. Not so — we also want to download backups off the server regularly.
Think about it… That backup file won't do you any good if the server your website, and therefore your backup copy is also on, gets compromised or crashes. You'll have no files to restore your website.
Before you do any maintenance on your database, you'll want to do a backup just in case. I use WPMU, and I can login to my control panel and create a backup with one click.
Your Mileage May Vary Depending on Hosting
You may need to export your MySQL database and website files via their file manager on some hosts. As with anything, some hosts make certain processes easier than others.
What I do is every Friday, I log into my hosting accounts and download copies of the most recent backup (or files). That way, I have a copy on my side that has everything added or modified for that week.
PRO TIP: Don't want to worry about off-site backups (aff)? Check out my White Glove Support, where I'll take care of that for you — and more.
You can FTP into the server and download the latest backup file to your computer on some hosting accounts. The next step is to put a copy of that download on a flash drive or file storage service (such as DropBox) just in case your computer crashes.
- Server crashes => You have a copy on your hard drive.
- Hard drive crashes => You have a copy on your flash drive.
- Your site gets hacked => You have a clean copy available to restore.
You can't be too careful when it comes to backups.
Don't know how to FTP?
FTP stands for File Transfer Protocol. FTP software is used for logging directly into your web hosting server. I use File Zilla — which is free.
FTP is a direct way to get at the files for your website — without having to go through your cPanel or web hosting dashboard, which may be confusing or limiting. Check your web hosting provider for your specific “FTP” settings.
If you don't want to deal with FTP, check out UpdraftPlus. Updraft gives you the options to not only schedule backups and how many to keep on hand, but you can download and restore with just a click.
As we've discussed before, plugins use resources. Therefore, any time you can accomplish a task without relying on an additional plugin is recommended. So if you already have a ton of plugins, learn how to use FTP.
Using UpdraftPlus, you don't have to login to your hosting account to download your backups or use FTP. Instead, you can download the files you want (and even restore backups) from within your WordPress dashboard.
PRO TIP: On an aside, understanding how to use FTP is a handy little added skill to have if you need it. In particular, if you have to troubleshoot your WordPress website because it is not working properly. That's why I'm mentioning it here instead of just relying on plugins. 😉
Okay, we now have our database backed up and have a copy off-server and off-line. Next, we want to clean things up a bit by repairing and optimizing.
Optimizing and Repairing your Database
I recently discovered an excellent plugin called Advanced Database Cleaner (aff) Pro if you are familiar with database structure, tables, and settings. This plugin made a huge difference on my site, and here's why.
My site was optimized but had a decade-old database. That means it most likely has many orphaned tables from plugins and themes I used and subsequently deleted over the years. Most folks don't know that residual database tables are left behind when you uninstall and delete plugins and themes.
Advanced Database Cleaner Pro
When my site was producing some 520 Server errors, I contacted my host to bring it to their attention. That's a server-side error that indicates “your server returned an empty, unknown, or unexpected response.” At that time, my host pointed out:
The current autoloaded data on your site is 2,807,154 bytes. This is ~2,007,154 bytes larger than 800k. We want that total to be under 800k or so for good performance.
Wow! Knowing how databases hold on to data over time, I knew that I would eventually address this. However, now was the time to eliminate this as a performance issue. I had tried the free plugins in the WordPress repository, and they could only do so much.
Advanced Database Cleaner Pro was the solution I needed to get the job done. It was easy and allowed me to minimize my database's autoloaded data down to 172843.
Since then, I've pruned other tables that were not autoloaded but were there for plugins and themes no longer installed on my website. I wish I had ADCP sooner!
For those less experienced…
WP-Optimize will:
- Remove all unnecessary data (e.g., trashed/unapproved/spam comments, stale data) plus pingbacks, trackbacks, and expired transient options.
- Compact/de-fragment MySQL tables.
- You'll have control of which optimizations you want to apply.
- Schedule automatic weekly clean-ups.
- Retain a set number of weeks of data during clean-ups.
- Show database statistics and potential savings.
WP-Optimize clears out most unnecessary data, cleans up your database tables, and even recovers space lost to data fragmentation. This plugin also offers caching, compression, and minification feature to help improve your site's performance even further.
But… Just be sure that you don't already have plugins (or your hosting) doing these additional tasks (caching, compression and minification) before using them. Having multiple caching plugins or tools does not mean more caching — it could cause conflicts.
Backup, Repair, Optimize
Backing up, repairing, and optimizing is now on auto-pilot. Sweet! But keep in mind a more detailed manual approach may be needed to really be thorough.
Start by getting a copy of that database off the server for safekeeping. Then, do what I do and add a note to your calendar to remind you to download the latest backup every Friday.
Now you have the basics to keep your database running smoothly and backed up. Get the above in place and take the time to embrace FTP.
Take my word for it — you don't want to think back and wish you did because of a crash or compromise. So don't be that guy (or gal).
At your service,