Alexis Kestler
As an experienced WordPress developer and database analyst, few things make me more anxious than an unprotected WordPress site. Far too often, we take for granted all the hard work that goes into building a WordPress site until catastrophe strikes.
According to my research, over 4 million websites are hacked each year. And when your site gets hacked or corrupted, a WordPress database backup is your only hope for restoring what’s lost.
So friend, if you don’t already have a rock-solid WordPress database backup solution in place, please keep reading. I’m going to walk you through everything you need to know to properly back up your database manually.
Trust me, after reading this guide, you’ll rest easy knowing exactly how to backup and restore every bit of your precious WordPress data. Let’s get started!
Why WordPress Database Backups Should Be Your #1 Priority
As a beginner, you may wonder why database backups are such a big deal. After all, hosting companies take care of backups automatically, right?
Wrong.
The brutal truth is that most basic web hosting plans do NOT provide automated comprehensive backups. Some may perform bare minimum server backups, but these are often insufficient for quickly restoring a complex CMS like WordPress when disaster strikes.
Don’t take my word for it. Here are four scary real-life examples of what happens without a WordPress database backup:
-
A study by Veeam found that 93% of companies that lost their data center for 10+ days filed for bankruptcy within a year.
-
In 2017, web host ServerMania suffered major data loss. Thousands of sites went down for days before being restored from old backups.
-
In 2021, a still-unknown issue caused 1.2 million WordPress sites hosted on GoDaddy to crash with serious data issues.
-
In 2022, UK host 123 Reg deleted client websites and had no backups to restore them from.
See what I mean? Web hosts are far from bulletproof. And basic backups are designed to restore servers, not individual sites like yours.
The truth is, as a website owner, the responsibility ultimately falls on you for safeguarding your site‘s data.
The good news is that with the right approach, it doesn’t have to be difficult or time consuming. When done properly, WordPress database backups give you the power to restore your site exactly as it was in a matter of minutes.
Now let’s dive into the specifics of how the WordPress database works, and how to properly back it up…
Anatomy of the WordPress Database: Tables, wp-config.php, and More
Before you can effectively back up and restore your database, you need to understand how the WordPress database works under the hood.
At its core, WordPress is a CMS (content management system) built on a MySQL database backend. Every piece of content, user, plugin setting, and configuration on your site is stored in this database.
By default, the database name starts with wp_ followed by a random series of letters and numbers. You can find the full database name in your wp-config.php file.
Within the database there are around 11 key tables:
| Table | Contains |
|---|---|
| wp_commentmeta | comment metadata |
| wp_comments | comment content |
| wp_links | blogroll links |
| wp_options | site options/settings |
| wp_postmeta | post metadata |
| wp_posts | all post content |
| wp_terms | taxonomy terms |
| wp_term_relationships | relates terms to objects |
| wp_term_taxonomy | term taxonomy type (category, tag, etc) |
| wp_usermeta | user metadata |
| wp_users | user data |
Of these, wp_posts and wp_options are two of the most important to back up, as they contain your pages/posts and settings. But ideally you want to backup all tables to preserve every bit of your site.
In addition to the database, you also want to backup wp-config.php and any other files you’ve customized. This file contains credentials and keys that are critical for restoring the database.
Now that you know exactly how WordPress organizes all your site data, let’s go over how to properly backup the database and critical files…
3 Step-By-Step Guides To Backing Up Your WordPress Database Manually
When it comes to WordPress backups, you have 3 main options for doing it manually without plugins:
- cPanel Backups
- phpMyAdmin Export
- WP-CLI Database Export
I’ll walk you through exactly how to use each method step-by-step. Follow any one that fits your site and comfort level.
Method 1: Backing Up Your Database Using cPanel
For most hosts, the quickest way to backup your database is using cPanel’s built-in backup tools. Here’s how:
Step 1) Login to your web host cPanel and click “Backup”
Step 2) Click the “Backup Wizard” icon
Step 3) Check the box for “Create a full backup”
Step 4) Select your WordPress database under “Databases”
Step 5) Choose a backup destination under “Backup Destinations”
Step 6) Click “Start Backup”
Once complete, you‘ll have downloadable backup file containing your full WordPress database and files.
If you have SSH access, you can automate this to run on a schedule using cPanel’s cron jobs. Just be sure to save the backups externally as cPanel only keeps one.
Method 2: Manual WordPress Database Export Using phpMyAdmin
If you’re not familiar with cPanel, most managed WordPress hosts also provide phpMyAdmin access for easy database exports:
Step 1) Login to cPanel and click “phpMyAdmin”
Step 2) Select your WordPress database on the left
Step 3) Choose “Export” at the top
Step 4) Leave the default export as “Quick”
Step 5) Check “Save as file”
Step 6) Leave format as SQL
Step 7) Click “Go” to run the export
Step 8) Save the SQL file with database dump locally and externally
This creates a manual one-time database export for peace of mind. Rinse and repeat anytime you want an on demand backup.
Method 3: Backup Your Database Using WP-CLI
WP-CLI provides the most flexibility for developers comfortable on the command line:
Step 1) Login to your server via SSH or other terminal
Step 2) Navigate to your WordPress installation root
Step 3) Run wp db export backup.sql
Step 4) Compress with wp db export --add-drop-table - | gzip > backup.sql.gz
Step 5) Securely transfer the backup file elsewhere
The great thing about WP-CLI is you can schedule cron jobs or integration scripts to automate the entire process of backing up, compressing and sending your backup to an off-site location.
No matter which method you choose, the most important thing is to actually use it consistently. Let’s go over how to restore backups next.
Restoring Your WordPress Database From Backup Files
Once you’ve successfully created a WordPress database backup, restoring it is fairly simple:
Method 1) Use phpMyAdmin’s import tool to upload and restore your SQL backup file.
Method 2) Run wp db import backup.sql in WP-CLI
Just be very careful to backup any existing data you want to keep before restoring. The import will overwrite your current database!
I also recommend testing your process monthly by restoring to a staging environment. This gives me confidence my backups are valid and the process works.
4 Pro Tips For Flawless WordPress Backups
Before you go, here are a few additional pro tips for ensuring your WordPress database backups are bulletproof:
1. Store backups both locally and off-site – Keep local backups available for quick restoration, but also transfer them to an off-site location protected from any issues that could occur on your web server.
2. Automate the process – Use cron jobs or other scripting to remove the human element of remembering to manually backup regularly.
3. Encrypt your backups – For an added layer of protection, encrypt your backup files before transferring or storing them.
4. Test your backups – Perform test restorations on a staging environment to validate your backup files remain intact and restoration process works.
Give Your WordPress Site The Protection It Deserves
I hope this guide has shown you why WordPress database backups are so critically important, as well as step-by-step techniques to backup manually even without plugins.
Please take the time to implement one of these backup methods if you haven’t already. Your future self will thank you the moment disaster strikes!
Rest easy knowing you have a complete copy of your precious WordPress data. You worked too hard building your site to leave it at risk.
Let me know if you have any other questions! I‘m always happy to chat WordPress backups and help a friend protect their site.
