How to Make a WordPress Database Backup Manually: An Expert‘s In-Depth Guide

As a professional WordPress site owner, one of the most important things you need to do is regularly back up your database.

With over 41% of the web running on WordPress (source: W3Techs), it‘s by far the dominant CMS. But with great power comes great responsibility.

Failure to properly backup your WordPress database can lead to your site being down for hours, days, or even permanently lost. I‘ve seen this happen to many sites in my 15 years as a webmaster.

Don‘t let it happen to you!

In this comprehensive guide, you‘ll learn exactly how to manually backup your WordPress database step-by-step. While plugins can automate the process, it‘s crucial to know how to create manual database exports.

Let me teach you how it‘s done, drawing on my own extensive experience managing WordPress sites for clients.

Why Manual WordPress Backups Are Crucial

Before we dive into the technical details, let‘s talk about why manual database backups are so important for WordPress sites:

• Plugins can fail – If your backup plugin crashes or has a conflict, you‘re left with zero backups until you can fix it.

• Control panel access can be lost – Whether due to a hack or server issue, you may be locked out of WordPress completely. Manual backups give you a backup option.

• Migrations require portability – To move your site to a new host or domain, manual backup files come in handy.

• Cleaning up malware – If your site gets hacked, restoring from a clean manual backup avoids reinfecting the site.

• Duplicating environments – Manually backup and restore between staging and production sites as needed.

• Total site failures – If your host unexpectedly loses your site, a manual backup lets you rebuild.

Don‘t assume it won‘t happen to you. Approximately 1 million WordPress sites are hacked each month according to Sucuri (source).

Having your own manual database backup can mean the difference between recovering or permanently losing your site.

Manual WordPress Database Backups

Now that you know why manual database backups are so essential, let‘s dig into the steps for how to easily create them yourself:

Method #1: Backup Database with phpMyAdmin

The fastest and simplest way to manually export your WordPress database is using phpMyAdmin:

phpMyAdmin is a free, web-based MySQL management tool. Most web hosts include it in cPanel for easy database access:

Benefits:

• Don‘t need FTP or SSH access
• Very user friendly graphical interface
• Works great for smaller databases < 100MB

Downsides:

• Slower for large database exports > 500MB
• Only accessible with hosting account

Follow these steps to manually backup your WordPress database with phpMyAdmin:

1. Login to your hosting cPanel and click the phpMyAdmin icon
2. Select your WordPress database on the left sidebar
3. Click Export tab > Custom export
4. Verify all tables are selected
5. Under Output, choose ZIP compressed
6. Hit Go to start the backup process

You‘ll then be able to download the .ZIP archive containing your database export. Simple as that!

Method #2: Use cPanel Backup Feature

Many web hosting control panels include tools to generate manual database backups on demand. For example:

cPanel has a Backup area under Databases section with backup options for each database.

Benefits:

• Very fast backups since runs on host server
• Easy 1-click download of backups

Downsides:

• Backups only stored temporarily (a few days)
• Requires hosting account access

Here is how to use cPanel to manually backup WordPress:

1. Login to cPanel and click on Backup icon
2. Scroll down to Database Backups section
3. Click Backup next to your WordPress database
4. Click on the new backup file to download

This generates an on-demand backup of your database as a GZIP file. Much faster than phpMyAdmin for large databases.

Method #3: WordPress Backup via SSH

For Linux/Unix web hosting, you can utilize SSH and the mysqldump command to generate database backups from the command line.

Benefits:

• Very fast, especially for large databases
• Only need SSH access not hosting account
• Automatable with CRON jobs

Downsides:

• Requires SSH and command line knowledge

Here is an example basic mysqldump command:

mysqldump -u dbuser -p database_name > db_backup.sql

This pipes the SQL backup to db_backup.sql file.

You can compress the output by adding gzip:

mysqldump -u dbuser -p database_name | gzip > db_backup.gz

Now you have a compressed GZIP‘ed backup of your WordPress database for easy transfer.

Comparison: Manual Backup Methods

Here is a quick comparison of the main options for manually backing up your WordPress database:

Method	Speed	WordPress Access	Learning Curve
phpMyAdmin	Slow	Required	Low
cPanel	Fast	Required	Low
SSH mysqldump	Very Fast	Not Required	High

As you can see, each method has its own pros and cons. The best one depends on your specific situation.

Storing WordPress Backups Securely

Now that you know how to actually generate manual WordPress database backups, let‘s discuss best practices for storing your backups safely.

Here are the top tips for securely saving your manual backup files:

• Use cloud storage – Backup services like Dropbox give you automated offsite storage and syncing.

• Store backups off server – Don‘t leave backup files on the same server as your live site.

• Rotate external drives – Use physical drives you can disconnect from computer when not swapping out.

• Encrypt before uploading – Encrypt backup files locally before transferring to remote storage.

• Limit cloud backup versions – Delete older cloud backups to avoid storage limits.

• Consider cold storage – Write backups to DVDs kept safely offsite.

The key criteria for secure WordPress backup storage is being disconnected and physically separate from your live server. This protects against data loss due to server failures, hosts shutting down, hackers, etc.

Real-World Horror Stories

Don‘t think backups are just an academic exercise.

Let me tell you some real-world horror stories from my 15 years of managing WordPress sites:

Case 1: Client Loses Years of Blog Posts

A doctor client had been faithfully blogging for over 9 years on their WordPress site. They used a free backup plugin that stored backups on the same server.

One day their site got hacked using an exploit in an outdated theme. The hacker deleted all database tables and content.

Unfortunately, the automated backups failed to restore properly. The client lost every blog post over 9 years – over 1,800 articles.

Because the backups were stored on the same server, the hacker deleted those too. If they had a recent manual backup stored offsite, they could have avoided this.

Case 2: Ecommerce Owner Restores Hack Site From Backup

An online store owner hired me after their site was hacked. They used a paid automated backup service that retained 30 days of backups.

The owner was able to easily restore from a backup made 1 week prior to the hack attack. This meant losing only 7 days of new orders.

For ecommerce sites, cloud-based automated backups are essential. But it still helped that they had manual access and control over backup files without relying 100% on a third-party automated service.

Recommended Backup Schedule

So how often should you be making manual WordPress database backups? Here is my recommended minimum backup frequency:

Low Traffic Site

For blogs, small business sites, and other low traffic sites, I suggest bi-weekly manual backups.

• Generate and download a manual backup every other week.
• Verify it exported properly.
• Transfer it to secure cloud or offsite storage.

This gives you a worst case scenario of losing 2 weeks of data in case of failure. Combine with daily/weekly automated backups.

Medium Traffic Site

For membership sites, online stores, and medium traffic sites (5k-50k visitors/month), I recommend weekly manual backups.

• Every Sunday, create a new manual database backup.
• Store at least 2 previous backups (or 1 month) in your rotation.
• Keep extra backups for important events like Black Friday, new product launches, etc.

For ecommerce stores, you may want to supplement with daily automated backups as well. But maintain at least 1 recent manual backup.

High Traffic Site

For sites with very high traffic volumes or revenue dependence, I suggest making incremental manual backups even more frequently:

• Generate manual backups on Monday, Wednesday, Friday.
• Keep at least 1 weeks worth of backups locally and in cloud storage.
• Make extra backups around major events or changes.
• Use automated backups in between for redundancy.

The more site traffic and revenue depends on your site, the more critical it is to have multiple redundant backup schemes in place.

Automated Backups vs Manual Backups

While manual database dumps play an important role, you should still be using an automated backup plugin as your primary backup solution.

Here is a comparison between using automated WordPress backups vs relying on only manual methods:

	Automated Backups	Manual Backups
Effort	Hands free after initial setup	Requires manual work each time
File backups	Includes all files	Database only
Backup locations	Stores remotely and locally	Must transfer manually
Scheduling	Run on a schedule	As needed
Security	Encryption options	Must encrypt manually
Scalability	Works for any site size	Not feasible for huge sites
Reporting	Logs each backup result	No reporting
Restores	One click restore process	More hands on

As you can see, automated WordPress backup plugins are superior in many ways like hands-free convenience and including file backups.

Yet they lack the portability, direct download access, and restoration guarantees of manual database dumps.

That‘s why I recommend using both a premium automated plugin like BlogVault in combination with periodic manual exports for a belt + suspenders approach.

Conclusion

I hope this guide has impressed upon you the importance of learning how to manually backup your WordPress database.

Here‘s a quick recap of what we covered:

• Why manual backups are still essential as a failsafe – plugins can break, accounts inaccessible

• How to easily generate manual database exports with phpMyAdmin, cPanel, and SSH

• Storing backups securely using cloud storage, external drives, etc.

• Real-world horror stories of sites getting hacked without usable backups

• Suggested backup schedules depending on your site traffic

• Pros and cons of automated vs manual backups

Taking a few minutes each week or month to manually export your database can really save your butt when things go wrong.

Don‘t wait until it‘s too late and you‘ve lost all your site‘s data! Follow these expert tips to make sure your hard work is properly backed up.

Let me know if you have any other questions!