How To Backup Your WordPress Database

Made changes to your site that you regret? Updated a plugin that crashed your site? Have you been hacked and lost precious content? There is one solution for all of them: restoring a backup. 

Always take complete backups of your site. 

Why a full site backup, you may ask? Well, there are two components that make up a WordPress site: files and a database. They’re both equally important for a site to function.

But you’ll find a lot of recommendations to take a WordPress database backup, suggesting that a database backup is sufficient to restore your site from a crash. That is not even close to accurate. Partial backups are useless and cause many restore failures.

With that in mind, this article is a guide on helping you backup WordPress database: one of two parts of a full site backup. 

TL;DR: There are so many things that can go wrong when managing a WordPress site that taking regular backups is the only way to go. BlogVault, the best-in-class WordPress backup plugin, takes backups automatically, as per a schedule that you customize, and with no effort from your end. 

What does it mean to backup WordPress database? Your database backup contains posts, pages, widgets, plugin settings, theme settings, and other user-generated content. This is all crucial information for your site to function. In this article, we’ll talk about the various ways to backup WP database. So, keep reading.  

1. Backup WordPress database with a plugin

Use a backup plugin like BlogVault to take a backup, and then extract the database if you require. This is the absolute easiest way to take a WordPress database backup. It’s less risky than a manual backup and easy to perform.

1. Install and activate BlogVault: As soon as you connect your site to BlogVault, it will sync your site. Within minutes, your backup is ready.
2. Download of the backup: Once a backup is done, check the Details in the Backup section of your site dashboard. Here, you’ll find your latest backup, and the option to download it.

3. Select files to download: You can either download the whole backup, or just the database tables. 

2. Backup WordPress database manually

There are a few ways to backup databases manually. We’ll go through each method carefully, however please note that none of these methods guarantees an error-free backup like a backup plugin does.

2.1 Backup database using cPanel Backup Wizard

Most hosts have a cPanel and a Backup Wizard feature that makes a WordPress database backup very easy. Here’s what you need to do:

1. Go to the Files section of your cPanel and click Backup Wizard. 
2. Click cPanel Backups in the Other cPanel Backups section.
3. In the Partial Backup Section, find Download a MySQL database and click the name of the database.
   Note: To find out which database to export, look for the database name in the wp-config.php file. 
4. Give it a minute and it should download as a .sql.gz file.

If you do have a cPanel but not the Backup Wizard, use the phpMyAdmin method as seen in the next section. 

2.2. Backup WordPress database using phpMyAdmin

If your web host doesn’t provide phpMyAdmin, they may use another database management tool like Adminer. Either way, the steps are mostly similar. You need to export the entire database into an SQL file. In our tutorial, we’re demonstrating how to take a backup using phpMyAdmin via cPanel.  

1. Access your phpMyAdmin: Go to the cPanel with your site’s web host and look for phpMyAdmin in the database section. 

2. Locate the right database: Click the Database tab. Select the database you need to export. 
   
   Note: To find out which database to export, look for the database name in the wp-config.php file. 

3. Export the files: Select all the files and click Export.

2.3 Backup WordPress database using SSH

As with using SSH to backup your site files, you can use SSH to export the database to an SQL folder as well. You will need to have WP-CLI installed to do this.

1. Connect to your site server using SSH. In most cases, the credentials will be the same as your FTP ones. 
2. Navigate to the wp-config.php file: This is most likely in your site’s root directory. Execute the following command. Replace the filename with an appropriate name for your database export.

wp db export filename.sql

2. Download the file to your local machine: Exit the server and download the database file using the following command:

scp username@serveraddress:rootfolder/filename.sql localfolder

2.4 Backing up the database using MySQL commands

phpMyAdmin can’t handle backing up large databases. For such situations, you can use MySQL commands. It’s an efficient, secure but complex way of using mysqldump utility. It requires adding custom code to the command line. If not, here are the steps to exporting your database:

1. Connect to Server: Login to your server with SSH. Replace username and ouripaddress with your username and actual IP address. Then add the password when prompted.

ssh useername @ouripaddress

2. Navigate to the right location: First, navigate to the correct directory using the following command. Replace the text after cd with the actual path.

cd /hp/hhh/MYSQLDB.com/tables 

3. Exporting the database: Use the mysqldump utility. The command is as follows. Replace username with the correct username, database name, file name and the format you’d like it to export as.

$ mysqldump -u USER_NAME -p DB_NAME >file_name.format

Why backup your WordPress Database

Once again, we don’t recommend that you only backup your database. A full site backup is always the way to go. Now, let’s talk about why you’d want to backup your database (and files) in the first place:

1. Restoring a crashed site: Plugins and themes updates are notorious for potentially crashing your site. You always need a backup that you can restore in case your update is incompatible. WE recommend having a backup before and after any changes you make to the site. We also have an article on how to restore a WordPress site if you’d like to learn more.
2. Reversing a wrong: Maybe you’ve realised that the new site design is bringing in less conversions than the previous one. Maybe, you’ve accidentally deleted a whole page. Whatever your wrong, a backup restore can make it right.
3. Malware infections: Some malware hacks can deface pages on your site. Security plugins like MalCare can definitely clean your site and get rid of the malware. But it cannot restore your site to it’s previous glory. That’s where backup restores come in.
4. Migration: If you’ve managed a site before, you know that there are many reasons why you’d need to migrate to a different host or server. May your server can’t handle the increased traffic that you have. Maybe, you’ve noticed that a different host has better features or support. Either way, to migrate a site, you need to export your entire site and import it to your new host or server. We have an article that walks you through the whole migration process.

Whatever your reason for a backup, we recommend using BlogVault. You can automate, schedule and have real time backups for your site so you always have the newest version to restore. The restore process only requires a few clicks. With BlogVault, backing up a site has never been easier, safer and more assured.

WooCommerce database backups are a special case, and should be treated as such.

Potential issues

1. Incomplete site recovery: If you’ve only taken a WP database backup, you’ll be unable to restore your full site because a site restore requires both files and a database. A partial database backup is completely useless when you really need it and it is why we recommend you backup your full site. 
2. Failed backups: Something as simple as losing connectivity to your server can cause a partial or failed backup. This is especially probable when you’re using a manual backup method for your database. The worst part? You may never know it was a failed backup unless you test the restore. To do so, restore your backup on a staging site.
3. Server load: Backing up your site database is a resource-intensive process on your server. Timeouts and downtimes are common. This is why we recommend either you backup WordPress database when traffic is low to your site or use a backup plugin like BlogVault that takes incremental backups. 
4. Time-consuming: This is especially true for larger sites. It takes a long time for your entire database to be backed up and downloaded to your computer. 
5. Storage: A database backup can be a few GBs and that can take up valuable real estate on your local computer’s storage. With BlogVault, your backups are stored on Amazon S3 servers. So there is no impact on your computer. 

Final thoughts

While there are so many reasons to backup a database, it is essential to take a full backup of your full site. The best way to go about doing so it to take a backup with BlogVault. With BlogVault, backups are automatic, can have a custom schedule and taken in real time. In other words, with BlogVault, you never have to worry about backups again.

FAQs

How to backup WordPress database?

The most efficient way to take WordPress database backup is to use BlogVault, a trusted and easy-to-use backup plugin. Install the plugin and sign up. It will automatically take a full backup but you can download just the database if you want. Here are the steps to do so. 

How to take a full backup of your site?

Use a WordPress backup plugin like BlogVault to back up your full site. Just install the plugin and sign up. It will automatically take a full backup. 

How often should you take a backup?

We recommend taking a backup daily, and before updating anything on your site: WordPress versions, plugins, themes, etc. If you’re a WooCommerce site, take real-time backups. BlogVault takes real-time ones and automatically takes a backup before you update a plugin or theme (if you’re using the BlogVault dashboard to update them).

How can you schedule backups?

You can schedule backups using BlogVault. Once you install the plugin, you should be able to access the site dashboard on BlogVault. On the left, you see a site overview. Select the hamburger icon below the site name. Next, click Sync Time and select what time of the day you’d like to schedule to sync your site.