3 Ways to Back Up and Restore Databases in WordPress

Key Points

There are three main methods for backup and recovery of WordPress databases: use the Udraft Plus plug-in, phpMyAdmin, and MySQL command line dump. Each method has its advantages and disadvantages, and the best choice depends on the specific needs and technical skills of the user.

Regular backup of WordPress databases is essential for website security and data protection. The backup should contain all forms that store all the data of the website, including articles, comments, users, site settings, etc.

Restore databases from backups can be done using the phpMyAdmin or MySQL command line. If the backup is corrupt, you may need to restore from an earlier backup or seek professional help.

WordPress still holds a major position to this day, even fifteen years after its release. For new blogs, it is usually the first choice to think of.

While WordPress itself is a content management system, in addition to blogs, it is widely used in many fields such as music and e-commerce. As of 2018, WordPress websites accounted for 14.7% of the top 100 sites.

WP Admin is usually enough if you manage a WordPress website. However, to protect against database corruption or hacking, you should make regular backups. As a standard guide, you can refer to the suggestions in the Backup Your Database page in WordPress Codex, which will help you create database backups.

WordPress only supports MySQL and MariaDB as databases, although synchronizing other databases such as PostgreSQL is not impossible. This article assumes that you use the default WordPress database option - MySQL, although the steps for MariaDB are very similar. We will first introduce the basics of a WordPress database, and then proceed with the backup and restore operations.

Basics of WordPress Database

When you first install WordPress, you will be provided with a WordPress database. Most webmasters will not be exposed to the database in the future - because all administrator-related operations on WordPress are performed through the GUI.

Database defined during WordPress installation If you look closely, you will find that you provide WordPress with the host, username, and password for the MySQL server to create the required table. Note that a prefix is ​​also provided, which means that all WordPress-related tables will begin with this prefix (if you want to create a backup).

WordPress uses databases to store the following data widely:

• Website settings
• Details of users who register on your website
• Details of published articles and drafts
• Tag information related to your article
• Comments on your post (assuming you are using WordPress’s comment system, not a third-party comment manager like Disqus).

Note: If you are interested in learning more about the various tables in your WordPress database and their features, you can refer to this guide on SitePoint.

Needless to say, it is not enough to just backup WordPress directories for security reasons. The database also needs to be backed up regularly.

Method 1: WordPress plug-in: Udraft Plus

Udraft Plus is a plug-in that allows you to back up and restore your entire site, including your database. After you install and activate the plugin, you will be asked to create a backup. After you create a backup, you can restore from any backup point in the past.

Udraft Plus Create Backup In the free version, you can create backups to remote locations (Dropbox, Drive, or Amazon S3), because backing up on the server makes it vulnerable to server failures and attacks. The premium version has more advanced options such as cloning and migrating sites, scheduling backups, and reporting.

Method 2: phpMyAdmin

Another way to manage database dumps through the GUI without using any code is through phpMyAdmin.

If your server management is done through software tools such as WAMP or cPanel, PhpMyAdmin is pre-installed. If your server is running on a Linux-based operating system, phpMyAdmin may also be preinstalled! 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 may be pre-installed.

After successfully logging in to phpMyAdmin, the database list will be displayed in the left column.

phpMyAdmin Dashboard Next, select the database you want to back up. In this case, the WordPress database is named wp. After selecting the database, select the Export tab.

Export options for phpMyAdmin By default, all tables in the database are exported as SQL queries. You can select different export options from the drop-down list as you like, or you can select the specific table to export. However, if your main reason for export is for backup, make sure to select only from the following formats: SQL, CSV, or XML. phpMyAdmin also supports three other formats, but you should stick with them if you want to transfer it through other tools. You can import the database by going to the Import tab and uploading the dump file.

It must be mentioned here that by default, the file size limit is set to 2MB. You must modify it in the php.ini file and restart the server to use a larger database dump.

For very large database sizes (such as a few GB), downloading the database dump through the browser and uploading it later can become difficult. If you have this type of problem, you can try the next option.

Method 3: MySQL command line dump

In MySQL, you can use the mysqldump command to download the database dump:

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

In this code:

• username is MySQL username
• password is MySQL password
• db_name is the database to be backed up
• backup.sql is the file name that stores the backup.

backup.sql is stored in the same directory where the command is running. Note that there is a space before [username] in the syntax, but there is no space before [password].

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

<code>mysqldump -u root -ptest wp > backup.sql</code>

If you only want to download a specific table, you need to provide the table name after the database:

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

To restore the database, you need to replace > with <

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

No modifications are made to restore only specific tables, as the command restores anything that exists in the backup.

Summary

In this article, we understand the basic functions of WordPress databases and the importance of backing up databases. Additionally, we explore three ways to do this – through plugins that help you back up, via phpMyAdmin, and via the command line.

(The FAQ section should be included here, the content is the same as the original text, but the format and wording can be adjusted as needed)

The above is the detailed content of 3 Ways to Back Up and Restore Databases in WordPress. For more information, please follow other related articles on the PHP Chinese website!