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 the 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 set up the local database using the production database SQL file. Multiple ways we can do that. Let’s 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

There are other ways to import database to WordPress.

When the SQL file is injected we need to change the domain name as in the database the domain is pointing to the production site. We need to change that to the local domain.

If we have a production site for example [live].com and a local site for example [locallive].com then we will have to change the 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 change all instances of the 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 the local site otherwise it will point to the live site.

Create a new user in WordPress database

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: joesmith with the email: joesmith@example.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('foobar'), 'Joe Smith', 'joesmith@example.com', 'http://www.example.com/', '2018-06-07 00:00:00', '', '0', 'joesmith');

# This query will return an insert ID. That will become the userID for the next two queries. In my case, the insert ID was: 11, so the user_id = 11 for the 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');