How to create a WordPress database backup: complete guide

WordPress stores a trove of important items in your website’s database. It’s like a filing cabinet for your site that contains all your content, including posts, pages, custom post types, comments, and revisions.

But that’s not all — a great deal of other critical information and settings are stored in your database:

*Plugin and theme settings

*Account information for all your WordPress users

*Categories and tags for posts, pages, and links

*Widget and sidebar settings

*Media files and attachment data

In fact, all of the content and settings that make your WordPress website your site is contained in your database.

So, whether you have a small blog or a large e-commerce store, WordPress database backups are essential.

You might be wondering: why take database backups when you could just run a regular site backup?

Of course, regular site backups are critical — you should always install and configure a WordPress backups plugin for your site. In a pinch, this allows you to quickly restore your site in case something goes wrong.

However, what if your site was hacked, or you got the White Screen of Death and couldn’t log-in to access the WordPress dashboard and restore your backup?

This is why having a WordPress database backup is essential. It provides an external way to restore your site via your database, for added peace of mind.

WordPress database backups can be taken in a number of different ways. In this post, we’ll take you through a few manual ways to backup your data, review the top three plugins for database backups, show you how to take a backup, and look at how to backup WordPress with your web host.

How to back up a WordPress database with phpMyAdmin

phpMyAdmin is a free and open-source software tool for MySQL and MariaDB. It’s one of the most popular MySQL administration tools, especially for web hosting services.

It also provides a convenient and easy-to-use interface for accessing your database.

tsoHost provides phpMyAdmin access as part of multiple web hosting plans. The easiest way to access your site’s phpMyAdmin panel is via your cPanel account.

Once logged into cPanel, scroll down to Databases and click on phpMyAdmin.

Once logged into phpMyAdmin, select the database you want to back up in the sidebar on the left. In the example below, we want to back up carefull_wp.

Next, select the Export option at the top of the page.

Two export methods are available:

Quick – Lets you backup all of your database’s table with minimal backup options.

Custom – Lets you choose which tables you want to export and provides options for compressing your WordPress database backup. The Quick option is selected by default and is the best method for exporting in most cases.

Leave the format set to “SQL” and click Go to back up your database. It may take a few moments, depending on the size of your database.

The file will download to your computer as an SQL file, giving you a complete backup of your WordPress database.

Restoring your WordPress database backup with phpMyAdmin

Knowing how to export your database is just one piece of the puzzle - it’s also important to know how to restore your database backup when something goes wrong.

To restore your backup, click Import at the top of the page. You’ll see options appear for importing a file. Click Choose File and select your SQL backup file.

Then click “Go” at the bottom of the page to restore your WordPress database backup. The import will replace your existing database tables.

How to backup a WordPress database via SSH

For large WordPress databases over 50 MB, it’s recommended you manage your backups via SSH and command line. This is a more technical method for backing up your site. SSH access is included in all tsoHost’s cloud hosting packages.

First things first, you’ll need two pieces of information:

*Your MySQL username

*The name of your MySQL database

*The password for your MySQL database

The easiest place to find these details is in the wp-config.php file in the root directory of your site. Scroll down the file until you find:

Once you’ve got these details, launch the SSL terminal for your site.

To export your MySQL database, you need to use the mysqldump command like so:

Don’t forget to replace “USERNAME” and “DATABASE” with your MySQL username and your MySQL database name.

You will be prompted to enter the password for your database.

Your database will be exported to your current directory and named backup.sql.

To see the backup file, enter ls to see the contents of your current directory.

Restoring your WordPress database backup via SSH

To import your database backup, you need to use the mysql command like so:

You’ll be prompted to enter your MySQL database password.

Top 3 WordPress plugins for database backups

If you would rather backup your database via your WordPress dashboard, you can do that, too. There are plenty of great WordPress database backup plugins available — including many that are free — but here are the top three in no particular order:

1. WP-DBManager

2. WP Migrate DB

3. Duplicator

1. WP-DBManager

WP-DBManager makes it really easy to access your database from your WordPress dashboard. It’s also the most feature-rich free plugin available for managing your WordPress database, with options for optimising, repairing, deleting, and restoring your database.

But what we’re interested in here is backing up your database — which this plugin lets you manage with ease. The plugin’s Backup DB page lets you quickly backup your database to a specific location on your server. You can also choose to compress your database file using GZIP.

With 100,000 plus active installations, WP-DBManager is a popular option for managing your database via your dashboard. It’s also a great option for non-technical users who want a straight-forward way to access and back up their database without feeling overwhelmed by settings.

2. WP Migrate DB

WP Migrate DB is a popular database plugin among developers. It’s well-known in the WordPress community for making pushing and pulling databases between staging and live sites quick and painless.

For simple backups, the free version of the plugin, which you can download from the WordPress plugin repository, is all you’ll need.

Once installed, the plugin will add a Migrate DB option under Tools in the dashboard sidebar. From there, you can choose to GZIP compress your database and save it as a file to your computer.

If you’re a developer, it’s worth checking out WP Migrate DB Pro, which allows you to sync the databases of two WordPress sites - useful for site migrations.

3. Duplicator

Duplicator is another site migration plugin that makes it easy to manage your WordPress database from the dashboard. It also offers a full suite of management options, including moving, migrating, or cloning a database between domains or hosts, and manually backing up your entire site, or part of it.

As far as backing up your database goes, Duplicator allows you to create “packages” and you can choose to only package your database. You can then download your WordPress database backup as an archive or together with an installer.

For more advanced options, the pro version of the plugins offers automated database backups, which you can schedule to run at chosen times. DuplicatorPro also lets you save your packages to cloud storage providers, including Amazon S3, Dropbox, and Google Drive.

How to use WP-DBManager for WordPress database backups

As far as free options go for backing up your WordPress database, WP-DBManager is one of the leading plugins out there.

It provides an intuitive interface that isn’t cluttered with too many options and it features automatic backups. That means you can schedule WordPress database backups to run in the background so you don’t have to take a manual backup.

Let’s step through how to use WP-DBManager to back up your WordPress database:

Step 1: install the WP-DBManager plugin

You can download WP-DBManager for free from WordPress.org. Alternatively, install and activate it via your WordPress dashboard.

Step 2: back up your database

Go to Database > Backup DB

The top part of the page will display a security check for your database, basically checking that your database is intact and ready to be backed up.

At the bottom of the page, you’ll see information about your last WordPress database backup and where it’s saved.

To run a new backup, select whether you want to save a GZIP file and click Backup.

The plugin will run a database backup and save the SQL file to your chosen directory. Then the page will reload to display a success message.

Step 3: change the backup directory

By default, your database backup file will be saved to the wp-content folder in the root of your WordPress site.

To change where you would like the backup file to be saved, go to Database > DB Options and update the path to where you would like your backups saved. Don’t forget to make sure the folder is writable.

Step 4: schedule automated WordPress database backups

Automatic scheduling is another useful feature of WP-DBManager. You can choose to run database backups at minute, hourly, daily, weekly, or monthly intervals.

There’s also an option for GZIP compressing your files.

Automated backups are a convenient option if you don’t want to back up your database manually. It offers peace of mind, so you know your database is constantly being backed up, and there’s always a backup file available if you ever need to restore your site.

How to backup with your web host

Many web hosts provide regular backups for WordPress sites and databases, and tsoHost is no exception.

Free daily backups are included in all tsoHost’s plans, including managed WordPress hosting plans. Backups are stored for 30 days, providing peace of mind that your site can be quickly restored in case any worst-case scenarios crop up.

Backups can be easily managed from the tsoHost control panel, which also makes it easy to restore backups. If you need help, tsoHost’s WordPress experts are on hand to help. Just submit a support ticket or open live chat.

Wrapping up

Running regular backups of your WordPress files is essential, no matter what kind of site you have, from WordPress blogs to e-commerce sites. But if you want to feel safe in the knowledge that your site can be quickly restored in an emergency, taking database backups ensures you’ll always be able to save your site when you can’t access the WordPress admin area.

There are many different ways you can take WordPress database backups, including manual backup solutions such as phpMyAdmin, and free plugins available at WordPress.org.

But if you would rather leave WordPress database backups to the experts, tsoHost can help. With hosting accounts designed for the internet’s most popular CMS, tsoHost’s packages come in three levels so you can pick the perfect option for your site.