WordPress Database Backup Guide

Running a website can be tricky. Have you ever made changes you wish you hadn’t? Or did you install an update that completely messed up your site? And worst of all, have you been hacked, losing all your hard work instantly? Well, there’s a lifeline in these situations: having a full backup of your site. But here’s the thing: your WordPress site comprises two main parts — the files and the database. Both are super important, and you must ensure they are backed up properly. That’s when a WordPress database backup comes into the game. 

Some would say you only need to back up your database, but that’s not entirely true. A backup covering only part of your site won’t cut when things go wrong. To be fully protected, you’ve got to back up everything — the files and the database.

We’ve already discussed how to complete WordPress backups in one of our previous guides. This time, we’ll focus on how to back up the WordPress database and keep your website safe and sound. Let’s dive in!

What Is WordPress Database

A WordPress database is an organized collection of data that stores, retrieves, and displays all the content created on your website. It includes posts, pages, comments, user data, site-wide settings, plugins, theme-related data, etc. The database also stores information about various options powering your website. The data within the database is commonly organized into tables, columns, and rows. You can modify the information in your database as long as you can access it. 

Why You Need to Backup Database

Before discussing the reasons to back up a WordPress database (DB), it’s important to emphasize that backing up only your website’s database is not the best solution. In emergencies or in case of malware attacks, you won’t be able to restore your website entirely. That’s why creating a full website backup is a better option. 

Now, let’s talk about why we need to back up DB. 

Regular plugin and theme updates to fit the latest WordPress version 2024 are essential for keeping your website safe and up-to-date. However, in case of an installed update incompatibility or for any other reason, keeping a fresh database backup created before applying any changes to the website ensures you can always revert and restore. 

If you realize the new design does more harm than good to your website or you accidentally deleted a whole page, a backup can make things right. 

Security plugins ensure your website is scam-free. However, they will clean your site once they notice malware on your web resource to eliminate scams. While cleaning your website from scams, security plugins like MalCare won’t restore its previous glory. This is when DB backups come in handy. 

If you migrate your website to a different web host or server, export WordPress DB and file and import them to a new host or server. A fresh backup will facilitate the migration process.

How to Backup Your WordPress Database

There are several ways to back up your WordPress database. This guide walks you through cPanl, SSH, MySQL, and plugin backup methods. Let’s discuss how to use these approaches. 

Hosting Panel/cPanel

Most hosting providers offer built-in DB backup features. Depending on the chosen hosting provider, you can back up the WordPress database with a custom panel or cPanel.

• cPanel is a popular web-based control panel that provides an interface and tools to simplify website hosting. In most cases, cPanel includes the option to create a database backup. 
• A custom panel is a control panel customized by the hosting provider. Regarding the graphical interface and provided features, it may differ from cPanel. 

Despite the mentioned differences, custom panels and cPanel share similar basic functionalities, including the possibility of creating DB backups. Depending on your web host’s specifications, you can backup WordPress files with cPabel Backup Wizard or phpMyAdmin.

Backup Database with Wizard

A cPanel Backup Wizard is an interface letting you back up DB or the entire website. To back up a WordPress database with the Wizard, you need to take the following steps: 

Step 1. Log in to your web hosting account

We’ll use Bluehost as an example web host to show you how to back up with Wizard. First, log in and select the website’s database you want to back up. Click Settings > Advanced > Manage.

Step 2. Open Backup Wizard

Next, you’ll be taken to the cPanel’s interface, where you can find the Backup Wizard. 

Step 3. Select partial backup 

The Backup Wizard provides two options: backup and restore. Click the backup button to be taken to the next screen, where you can choose between a full and partial backup. Then click the MySQL Database button. In the next window, you see the database backups available for download. Click on the needed one, and the download will begin automatically.

Backup Database with phpMyAdmin

cPanel’s phpMyAdmin is a tool intended specifically for database backups. To back up a WordPress database with phpMyAdmin, take the following steps: 

Step 1. Access the cPanel on your web host

Log in to your web host and navigate to your website’s advanced settings. Like backing up with a Wizard, use the cPanel’s interface to find phpMyAdmin. Click on it, and you’ll be redirected to phpMyAdmin itself.

Step 2. Select the database 

In the phpMyAdmin interface, you will see a list of your databases on the left side. Click on the database you want to back up.

Step 3. Export database

After selecting the database, click the Export tab in the top menu bar. A new window will open, allowing you to choose between a quick and custom database export method. Selecting a quick option should be sufficient for a simple WordPress DB backup.
Select SQL, a standard database backup option, in the Format dropdown menu. Click Export to download the database.

Backup with SSH

SSH (Secure Shell) is a secure protocol for accessing your web server and managing its files. When creating a WordPress website backup, you can use SSH to run commands directly on the server. Here’s how to back it up with SSH. 

1. Command mysqldump/mysql

The mysqldump command is a utility provided by MySQL. It is commonly used to dump a database for backup or transfer to another SQL server. Here’s how to use it. 

Step 1. Use SSH to connect to the server. Step 2. Use the mysqldump command to create a DB backup:

mysqldump -u [username] -p [database_name] > [database_name].sql

Put your MySQL username instead of [username], the DB name you want to back up instead of [database_name] with the database you want to back up. The command creates an .sql file containing your database backup. 

2. FTP Connect & Download the File Generated in the Previous Step

FTP (File Transfer Protocol) is a standard network protocol that transfers files between a client and a server. It’s important to emphasize that FTP is not used for DB backups. Its primary purpose is to help you download the backup file to your local machine.

Here’s how it works:

1. Use an FTP client to connect to your server.
2. Navigate to the directory where the backup file is stored. 
3. Right-click and the file will be downloaded to your computer.

Backup with MySQL Admin Tool

MySQL Admin Tool is a software utility that lets you manage your MySQL DB and users. Using this tool, you can perform multiple database operations, including creating DB backups. Here’s how to back up the DB using the MySQL Admin Tool. 

1. On your local machine, log into MySQL Admin Tool. 
2. After logging in, you’ll see a list of databases. Select the one you want to back up. 
3. Export the DB in the SQL format.

Backup Database with Plugin

UpdraftPlus is a popular backup database WordPress plugin. Here’s a step-by-step guide on how to use UpdraftPlus to backup your database.

Step 1: Install and Activate UpdraftPlus

Go to your WordPress dashboard, navigate to Plugins > Add New, search for UpdraftPlus, install and activate it.

Step 2: Access UpdraftPlus Settings

Once activated, go to Settings > UpdraftPlus Backups in your WordPress dashboard.

Step 3: Start the Backup Process

Click on the Backup Now button. A pop-up window will appear.

Step 4: Choose What to Backup

You can include your database and files in the pop-up window in the backup. If you want a complete backup, make sure the boxes for ‘Include your database’ and ‘Include your files’ are checked.

Step 5: Start the Backup

Click the Backup Now button in the pop-up window to start the backup process. UpdraftPlus will now create a full backup of your WordPress site.

Step 6: Download the Backup Files

Once the backup is complete, go to the Existing Backups tab on the UpdraftPlus settings page. Here you’ll see a list of your backups. You can download the database, plugins, themes, uploads, and others to your computer.

Step 7: Set up Backup Schedule

The backup database WordPress plugin also provides convenient WordPress auto backup settings. You can choose the frequency of making backups and the place where you’d like them to be stored.

Restoring Database from Backup

WordPress backup restore is the procedure that involves recovering the database from a previously exported backup file. The reasons for restoring DB from backup are diverse. For example, you may need to restore a backup due to accidental page deletion, software errors, malware attacks, etc. Whatever the reason, there are two ways to restore a database from a backup that you should know about. 

How to restore DB using a MySQL command

This method involves executing SQL commands to import a backup file into the database. Here’s how to do this.  

Step 1. Open the terminal or command prompt to access the command line. 

Step 2. Navigate to MySQL Bin Directory. It’s typically located at C:\Program Files\MySQL\MySQL Server X.Y\bin on Windows or /usr/local/mysql/bin on Unix-based systems.

Step 3. Enter the following MySQL command:

mysql -u username -p database_name < backup_file.sql

In this command:

username is your MySQL username.
database_name is the name of the database you want to restore.
file.sql is the SQL dump file from which you are restoring.

Step 4. Wait for the import to complete and verify restoration once the process finishes. 

How to restore DB using phpMyAdmin

Take the following steps to restore your database with phpMyAdmin: 

Step 1. Access phpMyAdmin and select the database you want to restore from the left-hand sidebar. Click on the Import tab in the top menu bar.

Step 2. On your local machine, select the DB backup file to import. It should be in the .sql format.

Step 3. Initiate the restoration process. Once completed, verify the database was successfully restored. 

How Often You Need to Backup Database

The frequency of creating database backups depends on many factors, all specific to your website. The frequency ranges from hourly to weekly backups. 

• For example, scheduling weekly backups should be enough if you run a website that you rarely update with new content or functionality. 
• Blogs with an active follower base should be backed up daily. 
• Hourly backups should be the best option if you run an online store. 

Conclusion

In a nutshell, creating regular WordPress database backups is a surefire way of safeguarding your data and ensuring you can restore the latest database version in case of unexpected accidents. Every website owner must learn how to back up a WordPress database. However, it’s not necessary (read as – not recommended) to manage database backups by yourself. 

At IT Monks, we provide professional website maintenance services, including backups and security management. We ensure your website is in capable hands, with all backups created on time and updates installed according to schedule. Our maintenance services let you focus on running your business while we care for your website’s health.