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 that is 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? 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 setup, are on auto-pilot. The rest are a few simple tasks that you can do on a regular basis 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. By optimizing the database tables every regularly will help to keep your site running as efficient as possible. The thing is, this won’t happen by itself.
Did you know that every time you save a new post or modify your pages, WordPress creates a revision of that post or page? 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 site's 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 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 regularly download backups off the server.
Think about it… That back up file won't do you any good if the server your website, and therefore you 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
On some hosts, you may need to export your MySQL database and website files via their file manager. 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.
On some hosting accounts, you can FTP into the server and download the latest backup file to your computer. 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 to log 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 a just a click.
As we've discussed before, plugins use resources. Any time that you can accomplish a task without having to rely 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. 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 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
If you are familiar with database structure, tables, and settings, I recently discovered an excellent plugin called Advanced Database Cleaner Pro. This plugin made a huge difference on my site and here's why.
My site was optimized but has a several decade-old database. That means it most likely has a bunch of orphaned tables from plugins and themes I used and subsequently deleted over the years. Most folks don't know that when you uninstall and then delete plugins and themes, residual database tables are left behind.
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! I knew, knowing how databases hold on to data over time, that I would have to address this eventually. 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 that are no longer installed on my website. 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 of the unnecessary data, cleans up your database tables, and even recovers space lost to data fragmentation. This plugin also offers caching, compression and minification features to help improve your site's performance even further.
But… Just be sure that you don't already have plugins (or that 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 need to really be thourough.
Starty by getting a copy of that database off the server for safekeeping. 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. Don't be that guy (or gal).
At your service,
