3 Ways to Back Up and Restore Databases in WordPress

WordPress is still relevant today, even fifteen years after its first release. It’s often the first choice that comes to one’s mind when starting a new blog.

Although intended to be a content management system, WordPress has been used in a variety of domains in addition to blogging — such as music and ecommerce. As of 2018, WordPress websites account for 14.7% of the top 100 websites.

If you manage a WordPress site, WP Admin should mostly be sufficient. However, to protect yourself against situations like corrupted databases or hacks, you should keep backups. As a standard guide, you could go through the advice in the Backing Up Your Database page in the WordPress Codex, which will help you in creating backups of your database.

WordPress supports only MySQL and MariaDB as databases, although it’s not impossible to sync other databases like PostgreSQL. This post will assume that you use the default WordPress database option — MySQL — although the steps for MariaDB are very similar. We’ll first cover the basics of the WordPress database, and then move on to backup and restore operations.

WordPress Database Basics

The WordPress database is provided to you when you’re installing WordPress for the first time. Most webmasters never encounter the database again — as every admin-related action on WordPress is performed through a GUI.

If you notice closely, you’re providing WordPress the host of the MySQL server, its username and password to create the required tables. Notice that a prefix is also supplied, which means that all WordPress-related tables would start with the prefix (in case you want to create backups).

WordPress uses the database to broadly store the following data:

• the settings of your website
• the details of users registered on your website
• the details of published posts and drafts
• tag information related to your posts
• comments on your posts (assuming you use the comment system of WordPress and not a third-party comment manager like Disqus).

Note: If you’re interested in knowing about details of various tables in the WordPress database and their function, you can refer to this guide on SitePoint.

Needless to say, keeping a backup of your WordPress directory isn’t sufficient for security purposes. The database needs to be backed up at regular intervals too.

#1 WordPress Plugin: Udraft Plus

Udraft Plus is a plugin that enables you to back up and restore your entire site, including your database. After installing and activating the plugin, you’re asked to create a backup. Once a backup has been created, you can restore from any backup points in the past.

In the free tier, you can create backups to a remote location (Dropbox, Drive or Amazon S3), as backing up within the server makes it vulnerable to server failure and attacks. The premium version comes with advanced options like cloning and migration of sites, scheduling backups and reporting.

#2 phpMyAdmin

The next way of managing database dumps through a GUI without the use of any code is through phpMyAdmin.

If your server management is through a software tool like WAMP or cPanel, PhpMyAdmin comes pre-installed. If your server runs on a Linux-based operating system, phpMyAdmin may even come pre-installed too! In other cases, you can install phpMyAdmin by downloading its source code and configuring it. The installation page has instructions to check which directory phpMyAdmin might be pre-installed in.

When you have successfully logged into phpMyAdmin, the list of databases is shown on the left column.

Next, select the database which you want to back up. In this case, the WordPress database was named wp. After selecting the database, select the “Export” tab.

By default, all the tables in the database are exported in the form of SQL queries. You can select a different export option depending on your needs from the drop down list and also select specific tables for the export. However, if your primary reason to export is for backup, make sure you select only from these formats: SQL, CSV or XML. phpMyAdmin also supports three other formats, but you should stick with these three in case you want to transfer it through some other tool. You can import a database by going to the Import tab and uploading the dump file.

At this point, it must be mentioned that by default the file size limit is set to 2MB. You’ll have to modify it in your php.ini file and restart the server to work with larger database dumps.

At very large database sizes (say a few gigabytes), it could potentially become difficult to download the database dump through the browser and upload it later. If you’re facing such an issue, you could try the next option.

MySQL Command Line Dumps

In MySQL, the dump of a database can be downloaded with the mysqldump command:

mysqldump -u [username] -p[password] [db_name] > backup.sql

In this code:

• username is the MySQL username
• password is the MySQL password
• db_name is the database to be backed up
• backup.sql is the name of the file where the backup would be stored.

backup.sql is stored in the same directory where the command is run. Notice in the syntax that there’s a space before [username] but none before [password].

If your username is root, password is test and the database to be backed up is wp, the command translates to the following:

mysqldump -u root -ptest wp > backup.sql

In case you want specific tables to be downloaded only, you need to supply the table names after the database:

mysqldump -u root -ptest wp wp_posts wp_postmeta > backup.sql

To restore the database, you replace the > with a < in the command:

mysqldump -u [username] -p[password] [db_name] < backup.sql

There’s no modification to restore only specific tables, as the command restores whatever is present your backup.

Final Thoughts

In this post, we looked at the basic functionality of the WordPress database and the importance of backing up your database. Further, we explored three ways of doing this — via a plugin that helps you in backup, via phpMyAdmin, and through the command line.

Frequently Asked Questions (FAQs) about Backing Up and Restoring WordPress Databases