3 Ways to Back Up and Restore Databases in WordPress

Share this article

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.

WordPress database defined during installationWordPress database defined during installation

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.

Udraft Plus creating a backupUdraft Plus creating a backup

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.

phpMyAdmin dashboardphpMyAdmin dashboard

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.

Export options of phpMyAdmin

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

What are the Risks of Not Backing Up My WordPress Database?

Not backing up your WordPress database can lead to significant issues. If your website experiences a technical glitch, gets hacked, or if an update goes wrong, you could lose all your data. This includes posts, comments, user information, and more. Without a backup, restoring this data can be nearly impossible. Regular backups ensure that you have a safety net in case of any unforeseen circumstances.

How Often Should I Back Up My WordPress Database?

The frequency of backups depends on how often you update your website. If you post new content or make changes daily, it’s advisable to back up your database daily. If your website content doesn’t change frequently, weekly or monthly backups may suffice. However, it’s always better to err on the side of caution and back up more frequently.

Can I Use WordPress Plugins for Database Backup and Restoration?

Yes, there are several WordPress plugins available that can automate the process of database backup and restoration. Some popular ones include UpdraftPlus, BackupBuddy, and VaultPress. These plugins can schedule automatic backups, store them in secure locations, and restore your database with just a few clicks.

How Can I Manually Back Up My WordPress Database?

You can manually back up your WordPress database using phpMyAdmin, a tool available in most web hosting control panels. After logging into phpMyAdmin, select your WordPress database, click on the ‘Export’ tab, and then click ‘Go’. This will download a .sql file of your database to your computer.

How Do I Restore My WordPress Database from a Backup?

To restore your WordPress database from a backup, you can use phpMyAdmin. Log in to phpMyAdmin, select your WordPress database, click on the ‘Import’ tab, and upload your backup .sql file. Click ‘Go’ to start the import process. Once completed, your database will be restored to the state it was in when the backup was made.

What Should I Do If My Database Backup Is Corrupted?

If your database backup is corrupted, you may not be able to restore your website properly. It’s crucial to verify your backups regularly to ensure they’re not corrupted. If you find a corrupted backup, try restoring from an earlier backup. If all backups are corrupted, you may need to seek professional help.

Can I Back Up and Restore My WordPress Database Without Using phpMyAdmin?

Yes, you can use command-line tools like mysqldump and mysql to back up and restore your WordPress database. However, these tools require a certain level of technical knowledge and are typically used by advanced users or developers.

How Can I Secure My WordPress Database Backups?

You can secure your WordPress database backups by storing them in a secure location, such as a cloud storage service with strong encryption. It’s also advisable to keep multiple copies of your backups in different locations. Additionally, you should protect your backups with strong passwords and update them regularly.

What Should I Include in My WordPress Database Backup?

Your WordPress database backup should include all the tables that store your website’s data. This includes posts, comments, users, site settings, and more. Some backup plugins also allow you to back up your themes, plugins, and uploads.

Can I Migrate My WordPress Site Using a Database Backup?

Yes, you can migrate your WordPress site to a new server or domain using a database backup. However, this process can be complex and requires careful attention to detail. It involves backing up your database, transferring it to the new server, and updating your site’s URL in the database. It’s recommended to use a migration plugin or seek professional help for this task.

Shaumik DaityariShaumik Daityari
View Author

Shaumik is a data analyst by day, and a comic book enthusiast by night (or maybe, he's Batman?) Shaumik has been writing tutorials and creating screencasts for over five years. When not working, he's busy automating mundane daily tasks through meticulously written scripts!

backuplearn-WordPressWordPressWordPress backupsWordPress-hub
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week
Loading form