Back Up and Restore Databases in WordPress

WordPress Database Basics

When you first install WordPress, the database is included. Most webmasters never need to deal with the database again because all administrator actions on WordPress are handled through a graphical user interface.

If you look closer, you’ll notice that you’re telling WordPress the host of the MySQL server’s username and password so it can build the necessary tables. A prefix is also given, which means all WordPress-related tables will begin with that string (in case you want to make backups).

WordPress uses the database to store a variety of information, including the following:

• 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.

Without a doubt, maintaining a backup of your WordPress directory isn’t enough for security. The database should also be backed up on a regular basis.

1. WordPress Plugin: Udraft Plus

Udraft Plus is a plugin that allows you to backup and restore your entire website, including your database. Once you’ve installed and started the program, you’ll be prompted to make a backup. You can go back in time by restoring from any previous backups.

In the free edition, you may back up to a remote location (Dropbox, Drive, or Amazon S3), as backing up on the server exposes it to server failure and assaults. Advanced features such as site cloning and migration are included in the premium version.

2. phpMyAdmin

phpMyAdmin is another method for handling database dumps through a GUI without the need of any code.

PhpMyAdmin is pre-installed on WampServer or cPanel if you use a software management system like WAMP or cPanel. If your server is based on Linux, phpMyAdmin may be installed as well! Install phpMyAdmin by obtaining its source code and configuring it in other situations. The installation page provides instructions for finding your database server and filling out the relevant details.

The list of databases is shown on the left column when you’ve successfully logged into phpMyAdmin.

Finally, choose the database for backup by clicking on it. All of the tables in the database, by default, are exported in the form of SQL queries. You may change your selection from the drop-down menu depending on your needs and select specific items to export. If your main purpose is to back up data, however, make sure you pick all of the tables and click “Export.”

There are three additional formats that phpMyAdmin can export to, but you should stick with the ones listed above in case you want to transmit it via another tool. The dump file may be imported by going to the Import tab and uploading the file.

By default, the file size limit is set to 2MB. To work with larger database dumps, you must change it in your php.ini file and restart the server.

It’s possible that, at large database sizes (for example, a few GB), downloading the database dump through the browser and uploading it later might become difficult. If you’re having trouble with this approach, consider trying the following one.

MySQL Command Line Dumps

With the mysqldump command in MySQL, you may obtain a database dump:

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.

The command is run in the user’s home directory and placed in a file called ss-backup.sql. There’s a space before [username] but none before [password] in the syntax.

When you try to log in as user root and password test, but the database being backed up is WP, the command becomes:

mysqldump -u root -ptest wp > backup.sql

If you want certain tables to be downloaded only, include 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

The restore command doesn’t just delete specific tables; it restores whatever is in your backup to its original state.