How to copy WordPress live database to local database?

Use case:

Let say we have a live website built with WordPress. We decided to have a copy of the production site to local development environment. Let consider our production database is MySQL. We got a copy of the production database as a SQL file. Now need to setup the local database using production database SQL file. Multiple ways we can do that. Lets try the standard way to do it first:

Create database and inject SQL file

First of all we need to create a local database. Once the databse is created inject the SQL file using the following command:

mysql -u [user] -p [pass] [dbname] < [sql-file-name].sql

When the SQL file is injected we need to changed the domain name as in the database the domain is pointing to the production site. We need to change that to local domain. If we have a production site for example [live].com and local site for example [locallive].com then we will have to change domain accordingly.

Change domain using SQL query

Assuming the production site: https://live.com
Assuming the local site: http://locallive.com

UPDATE wp_options SET option_value = replace(option_value, 'https://live.com', 'http://locallive.com') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET post_content = replace(post_content, 'https://live.com', 'http://locallive.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://live.com','http://locallive.com');

The above three queries changes all instances of production site URL with the local site URL. As soon as we update the database with the above three queries the local site URLs will all be pointing to local site otherwise it will point to the live site.

At this point we should be all set with the local database setup. Now if we need a database user we can create the database user using SQL. Let say we want a user login called: beroza with the email: beroza07@gmail.com

INSERT INTO `localdb`.`wp_users` (`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES ('beroza', MD5('h43Xdb'), 'Beroza Paul', 'beroza07@gmail.com', 'http://www.cefalo.com/', '2018-06-07 00:00:00', '', '0', 'beroza');

// This query will return a insert ID. That will become the userID for next two queries. In my case the insert ID was: 11, so the user_id = 11 for next two queries. 

INSERT INTO `localdb`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '11', 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
 
INSERT INTO `localdb`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '11', 'wp_user_level', '10');
Share