How to change the WordPress site URL using MySQL command line

The following steps may be helpful for a site migration where you need to find and replace all the old URLs with the new URLs.

Problem: site references old URL (http://www.domain1.com). New URL is http://www.domain2.com

Solution: Use the command line to make short work of the task.

user@server:~$ mysql -u root -p
Enter password:

Use the database named “store-db”

mysql> use store-db;

Reading table information for completion of table and column names

Show the tables

mysql> show tables;
+-------------------------------------+
| Tables_in_store-db                  |
+-------------------------------------+
| wp_commentmeta                      |
| wp_comments                         |
| wp_links                            |
| wp_options                          |
| wp_postmeta                         |
| wp_posts                            |
| wp_term_relationships               |
| wp_term_taxonomy                    |
| wp_terms                            |
| wp_usermeta                         |
| wp_users                            |
+-------------------------------------+
16 rows in set (0.00 sec)

mysql> select * from wp_options where option_value = 'http://www.domain1.com/wordpress';
+-----------+---------+-------------+---------------------------------+----------+
| option_id | blog_id | option_name | option_value                    | autoload |
+-----------+---------+-------------+---------------------------------+----------+
|         1 |       0 | siteurl     | http://www.domain1.com/wordpress | yes      |
+-----------+---------+-------------+---------------------------------+----------+
1 row in set (0.00 sec)

mysql> update wp_options set option_value = 'http://www.domain2.com' where option_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from wp_options where option_value = 'http://www.domain1.com/wordpress';
Empty set (0.00 sec)

mysql> select * from wp_options where option_value = 'http://www.domain2.com.com';
+-----------+---------+-------------+------------------------+----------+
| option_id | blog_id | option_name | option_value           | autoload |
+-----------+---------+-------------+------------------------+----------+
|         1 |       0 | siteurl     | http://www.domain2.com | yes      |
+-----------+---------+-------------+------------------------+----------+
1 row in set (0.00 sec)

mysql>exit
Bye
user@server:~$