How to update Wordpress URLs in MySQL

howto-update-wordpress-urls-in-mysql.md

How to update URL's for wordpress in a MySQL database

URL Info

Old URL: https://old.domain.com New URL: https://new.domain.com

See where we need to update

mysql> select count(*) from wp_options where option_name = 'siteurl' or option_name = 'home';
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select * from wp_options where option_name = 'siteurl' or option_name = 'home';
+-----------+-------------+------------------------+----------+
| option_id | option_name | option_value           | autoload |
+-----------+-------------+------------------------+----------+
|         2 | home        | https://old.domain.com | yes      |
|         1 | siteurl     | https://old.domain.com | yes      |
+-----------+-------------+------------------------+----------+
2 rows in set (0.01 sec)

mysql> select count(*) from wp_posts where guid like '%old.domain.com%';
+----------+
| count(*) |
+----------+
|      238 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from wp_posts where guid like '%old.domain.com%';
+----------+
| count(*) |
+----------+
|      151 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from wp_postmeta where meta_value like '%old.domain.com%';
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.02 sec)

Update the Tables

mysql> use my_wordpress_website_db;
mysql> UPDATE wp_options SET option_value = replace(option_value, 'https://old.domain.com', 'https://new.domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, 'https://old.domain.com','https://new.domain.com');
Query OK, 151 rows affected (0.04 sec)
Rows matched: 1260  Changed: 151  Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'https://old.domain.com', 'https://new.domain.com');
Query OK, 528 rows affected (2.45 sec)
Rows matched: 1260  Changed: 528  Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://old.domain.com','https://new.domain.com');
Query OK, 12 rows affected (0.04 sec)
Rows matched: 3122  Changed: 12  Warnings: 0

View that the data is updated:

mysql> select * from wp_options where option_name = 'siteurl' or option_name = 'home';
+-----------+-------------+------------------------+----------+
| option_id | option_name | option_value           | autoload |
+-----------+-------------+------------------------+----------+
|         2 | home        | https://new.domain.com | yes      |
|         1 | siteurl     | https://new.domain.com | yes      |
+-----------+-------------+------------------------+----------+
2 rows in set (0.00 sec)