Blog

I use the following SQL queries regularly to move databases between local, staging and development environments in my current role and have found them to be invaluable. There are plugins that can do this but a simple SQL query is going to be faster and the output from the SQL console will give you clear and concise feedback when executing. The following WordPress database queries are my “Swiss army knife” of tools when migrating databases across environments.

First, to replace the Site Address (URL) and the WordPress Address (URL), use this query:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.your-old-domain.com', 'http://www.your-new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

After that, we need to replace the URL’s contained within your post content, which is stored in the “post_content” column contained in the “wp_posts” table. To do that, use this query:

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.your-old-domain.com', 'http://www.your-new-domain.com');

while we’re at it, we’ll also update instance of the old URL in the “guid” column. We’ll do that like this:

UPDATE wp_posts SET guid = replace(guid, 'https://www.your-old-domain.com', 'http://www.your-new-domain.com');

Your site will almost certainly work perfectly fine if you don’t update the “guid” column but if you rely on RSS then your feed will malfunction. Each guid is also a valid URL and each URL contained in the “guid” column will redirect you to the corresponding post so while this may not seem like a big deal if nothing is obviously broken, it is a significant piece in the overall health and proper function of your site.

It’s also important to not overlook the “wp_postmeta” table. This table stores all of the data related to each post in your site. Any hardcoded instances of your old site URL can have a negative affect on the functionality of your site. To update the post meta_value, use this query:

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://www.your-old-domain.com', 'http://www.your-new-domain.com');

Last, and this only applies if you use or have used the comments feature, is that we need to replace the URL’s in any comments made on your site. Top do that, use this query:

UPDATE wp_comments SET comment_content = replace(comment_content , 'http://www.your-old-domain.com', 'http://www.your-new-domain.com');

When the queries run successfully, you should see an output like the following:

Query OK, 2 rows affected (0.03 sec)

Rows matched: 2 Changed: 0 Warnings: 0

Depending on how many rows the query will check, this may take some time. Updating the “meta_value” on one of my sites with almost 19,000 posts returned this console output:

sitedb_wp> UPDATE wp_postmeta SET meta_value = replace(meta_value, 'https://www.my-old-url.com', 'https://www.my-new-url.com')
[2023-10-02 10:23:42] 231,918 rows affected in 1 s 27 ms

And likewise, updating the “post_content” column returned this console output:

sitedb_wp> UPDATE wp_posts SET post_content = replace(post_content, 'https://www.my-old-url.com', 'https://www.my-new-url.com')
[2023-10-02 10:26:03] 86,156 rows affected in 38 s 573 ms

Keep in mind that I’m executing these queries on an AWS RDS instance that is size db.t3.medium so take your particular database infrastructure into account when running these commands. I can personally confirm that these queries work on WordPress versions 4.8.x up to 6.3.x. I don’t see this changing in future WordPress versions but who knows. Also, these queries do not take into account any third-party plugins or data that said plugins may be storing in your site’s database.