Change WordPress site URL using MySQL CLI

[Vinil]

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. This is indeed helpful if you are also not able to access Admin (wp-admin) area. To do this,

Login to mysql and select your DB.

mysql> use your_db_name;
Database changed

Show the tables. For example:

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                            |
+-------------------------------------+
11 rows in set (0.00 sec)

Now check for your old urls

Code: [Select]

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

Update the table with your new urls

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

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

Check if your new url's are updated

Code: [Select]

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

mysql>exit
Bye
user@server:~$

That's it.