Short of a config file or two, and a couple data folders, the WordPress database contains pretty much all of the settings and content for a WordPress web site.
Everything from user data, categories, taxonomies, comments, settings, posts and more are stored in the database. It is being read from and written to regularly during normal use of the web site, and therefore is updated constantly.
It is Important to Have a Backup of the Database
Anytime WordPress is updated, or anything else significant takes place, a manual database backup should be performed. In addition, regular backups to guard against database corruption, hacking, and other causes of data loss, should be done.
Scheduled backups of the WordPress database, as well as manual backups, can be done using the All In One WP Security plugin.
Instructions to Backup and Restore the WordPress Database
Quick Steps for Performing a Manual Database Backup
First, if it isn’t already installed, install the All In One WP Security plugin like you would any other plugin.
Step 1) Click WP Security >> Database Security from the left side menu in the WordPress dashboard.
Step 2) Now from the top of the page, click the tab labelled DB Backup.
Step 3) You can now enable automated scheduled backups if you like. You can set the time interval between backups and the number of database backups to keep before overwriting them. Plus, you have the option to receive the backup by email.
Step 4) While you are in there you can run a DB backup manually. To do so, click the Create DB Backup Now button.
After clicking the button you will get a comfirmation message saying: “DB Backup was successfully completed!” The backups are stored in a “backups” folder inside the plugin’s folder.
Now you can download the database backup to your computer from the provided link. Just right click and click “Save link as…” (or a similar command) and save the .zip file to your computer. The .zip file will contain one file which is copy of the SQL in your database.
Restoring the Database from the Backup File
In the event that something goes wrong and you need to restore a previously backed up SQL file for your MySQL database, use the steps below. These steps will be performed outside of the WordPress environment so access to your web host’s Control Panel is necessary.
- Login to the Control Panel (CPanel) of your web host.
- Look for the box called Databases and click phpMyAdmin.
- From the left side, locate the database you need to restore and click on it.
- Be sure that you are on the Structure tab and scroll to the bottom of the page.
- Click the Check All option to highlight all of the tables.
- From the With Selected: drop down box choose Drop.
- Click the Yes button.
- Click the Import tab.
- Click the Choose File button.
- Locate the backup file on your computer and double click it.
- Click the Go button.
Now exit out of phpMyAdmin, you have recreated your database from the backed up file! Test your WordPress site to if it behaves as expected.
Recreating/Restoring the Database from Scratch
Many people like to destroy the database completely and then restore the backup SQL. If you want to go that route, be sure to have a look at the wp-config.php file in the root of the web site to check the database settings. Make note of the database name, database user name, and database password. Then use that information when recreating the database and user.
If your database was compromised however, this may be a good opportunity to use a new name for the database, a new password, and a new database user. Be sure your changes are reflected in the wp-config.php. Also, if the “MySQL hostname” in the wp-config.php file is anything other than “localhost,” you may have to reset that value after the database is created.
Once the new database is created, follow the “restore” procedure above short of dropping the tables (because the database will already be empty).