Restore a WordPress Database to Localhost

Working with WordPress, I often will take a production database and restore it to my local server to verify changes and further aid in development. When doing so, I will always run WordPress under a named virtual host, with frequent production backups allowing me to mimic the production environment.

This article will discuss the steps necessary to backup and restore a production MySQL database to a local development environment, and configure it to run against a local named virtual host or URL.

NOTE: This post assumes that a localhost web server and MySQL instance has been setup, and that the web site code exists locally, and the localhost WordPress web site can be accessed.

Step 1. Backup the Production WordPress Database

Begin by backing up the production WordPress database. To backup a production MySQL database to a file from a production server, execute the following command on the command line to generate an SQL file that can be restored on your local development environment.

Replace the following variables within the command shown below, as needed for the production environment:

• {user} : A MySQL user that with appropriate privileges for the database being backed up.
• {database_name} : The MySQL database name being backed up.
• {current_date} : The current date, in YYYYMMDD format. It is recommended to post-fix the database backups with the current date in the YYYYMMDD format, allowing the storage of multiple database backup files.

mysqldump -u {user} -p {database_name} > ~/{database_name}_{current_date}.sql

2. Create the WordPress Database on Localhost

The mysqldump command contained no options to drop and create the database that will restored. This was done purposefully, so if the database already exists, or when a user is created and granted all privileges, that work doesn’t need to be redone on any subsequent restore of the WordPress database.

If you find that you need to create the database on localhost, execute the following SQL statement, replacing the necessary variables:

• {database_name} : The MySQL database name being created.

CREATE DATABASE {database_name};

3. Restore the WordPress Database on Localhost

To restore the MySQL database locally, download the MySQL backup to the local machine, and then restore it executing the command shown below, replacing the necessary variables in the command as needed for the localhost environment:

• {user} : A MySQL user that has appropriate access to create and insert data into the database.
• {database_name} : The MySQL database name just created.
• {backup_filename} : The absolute path to the MySQL backup file downloaded previously.

mysql -u {user} -p {database_name} < {backup_filename}.sql

4. Create the Local MySQL User and Grant All Privileges

If web site code was downloaded directly from the production server and to avoid having to modify the WordPress configuration file to update the connection information, create the MySQL username and password used on the production server locally and assign it the privileges necessary.

Execute the following SQL statements replacing the variables as identified below to create the new MySQL user and to grant all privileges:

• {user} : The MySQL user to be created.
• {password} : The MySQL password for the user being created.
• {database_name} : The MySQL database name the user is being granted privileges to.

CREATE USER '{user}'@'localhost' IDENTIFIED BY '{password}';

GRANT ALL PRIVILEGES ON {database_name}.* TO '{user}'@'localhost' WITH GRANT OPTION;

5. Modify the WordPress home and siteurl Options

Once the backup has been restored, execute the following SQL statement against the WordPress database to ensure that the URL values recorded in the wp_options table point to a named virtual host or localhost URL, replacing the variables as identified below within the SQL statement shown:

• {localhost_url} : The local named virtual host or localhost URL used to access the restored WordPress web site.

UPDATE wp_options SET option_value = 'http://{localhost_url}/' WHERE option_name IN ('home', 'siteurl');

6. Verify Restored Database

Verify the restored database by accessing the WordPress web site at the localhost URL specified in the wp_options. Browse around the various pages and posts, and verify that a successful login can be made, and that a redirect is not issued to the production web site, this will confirm the wp_options were successfully set, and the localhost environment is now properly restored with the WordPress database.