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 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 WPEngine and I can login to my control panel and create a new “check point” and name it to identify the reason for the backup. For example, “DB Optimization”.
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? 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.
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
WP-Optimize has a built-in scheduler to optimize and repair as well as schedule your backups. Over time there is data stored and sometimes fragmented in your database that you no longer need.
What's also cool is WP-Optimize works in concert with UpdraftPlus by automatically triggering a pre-optimize backup.
For starters 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 all 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 doing these additional tasks before using them. For example 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!
All you have to do is get 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,
- How to Update to WordPress 5.6.2: Maintenance Release
- 7 Tips to Surprise and Delight Your Customers
- Want your posts read? Avoid these 6 bad habits.
- 5 Tips To Have a Blog with the “WOW! Factor”
- WordPress Homepage Setup Tips
- How to Find Great WordPress Blog Post Ideas
