WordPress Change URL – MySQL – Update String Within Column

Recently I changed a domain alias for a WordPress site and needed to change the domain in all the pages/posts and this little script came in handy.

Typical disclaimer stands, do a full backup before running this and verify you have everything setup correctly.

UPDATE wp_options SET option_value = REPLACE(option_value, 'wp.domain.com', 'www.domain.com') WHERE option_value LIKE ('%wp.domain.com%');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'wp.domain.com', 'www.domain.com') WHERE meta_value LIKE ('%wp.domain.com%');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'wp.domain.com', 'www.domain.com') WHERE post_content LIKE ('%wp.domain.com%');
UPDATE wp_posts SET guid= REPLACE(guid, 'wp.domain.com', 'www.domain.com') WHERE guid LIKE ('%wp.domain.com%');

If you have a network site first update your wp-config.php

sed -i 's|wp.domain.com|www.domain.com|g' wp-config.php

Secondly update all database tables, note this may be different depending on how many sites you have.

Update wp_site SET domain = 'www.domain.com' WHERE domain = 'wp.domain.com';
Update wp_sitemeta SET meta_value = 'http://www.domain.com/' WHERE meta_key = 'siteurl';
Update wp_sitemeta SET meta_value = 'www.domain.com' WHERE meta_key = 'dm_ipaddress';
UPDATE wp_blogs SET domain = 'www.domain.com' WHERE domain = 'wp.domain.com' ;

UPDATE wp_options SET option_value = REPLACE(option_value, 'wp.domain.com', 'www.domain.com') WHERE option_value LIKE ('%wp.domain.com%');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'wp.domain.com', 'www.domain.com') WHERE meta_value LIKE ('%wp.domain.com%');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'wp.domain.com', 'www.domain.com') WHERE post_content LIKE ('%wp.domain.com%');
UPDATE wp_posts SET guid= REPLACE(guid, 'wp.domain.com', 'www.domain.com') WHERE guid LIKE ('%wp.domain.com%');
Update wp_usermeta SET meta_value = 'www.domain.com' WHERE meta_key = 'source_domain';

UPDATE wp_2_options SET option_value = REPLACE(option_value, 'wp.domain.com', 'www.domain.com') WHERE option_value LIKE ('%wp.domain.com%');
UPDATE wp_3_options SET option_value = REPLACE(option_value, 'wp.domain.com', 'www.domain.com') WHERE option_value LIKE ('%wp.domain.com%');
UPDATE wp_4_options SET option_value = REPLACE(option_value, 'wp.domain.com', 'www.domain.com') WHERE option_value LIKE ('%wp.domain.com%');
UPDATE wp_5_options SET option_value = REPLACE(option_value, 'wp.domain.com', 'www.domain.com') WHERE option_value LIKE ('%wp.domain.com%');
UPDATE wp_6_options SET option_value = REPLACE(option_value, 'wp.domain.com', 'www.domain.com') WHERE option_value LIKE ('%wp.domain.com%');

UPDATE wp_2_postmeta SET meta_value = REPLACE(meta_value, 'wp.domain.com', 'www.domain.com') WHERE meta_value LIKE ('%wp.domain.com%');
UPDATE wp_3_postmeta SET meta_value = REPLACE(meta_value, 'wp.domain.com', 'www.domain.com') WHERE meta_value LIKE ('%wp.domain.com%');
UPDATE wp_4_postmeta SET meta_value = REPLACE(meta_value, 'wp.domain.com', 'www.domain.com') WHERE meta_value LIKE ('%wp.domain.com%');
UPDATE wp_5_postmeta SET meta_value = REPLACE(meta_value, 'wp.domain.com', 'www.domain.com') WHERE meta_value LIKE ('%wp.domain.com%');
UPDATE wp_6_postmeta SET meta_value = REPLACE(meta_value, 'wp.domain.com', 'www.domain.com') WHERE meta_value LIKE ('%wp.domain.com%');

UPDATE wp_2_posts SET post_content = REPLACE(post_content, 'wp.domain.com', 'www.domain.com') WHERE post_content LIKE ('%wp.domain.com%');
UPDATE wp_3_posts SET post_content = REPLACE(post_content, 'wp.domain.com', 'www.domain.com') WHERE post_content LIKE ('%wp.domain.com%');
UPDATE wp_4_posts SET post_content = REPLACE(post_content, 'wp.domain.com', 'www.domain.com') WHERE post_content LIKE ('%wp.domain.com%');
UPDATE wp_5_posts SET post_content = REPLACE(post_content, 'wp.domain.com', 'www.domain.com') WHERE post_content LIKE ('%wp.domain.com%');
UPDATE wp_6_posts SET post_content = REPLACE(post_content, 'wp.domain.com', 'www.domain.com') WHERE post_content LIKE ('%wp.domain.com%');

UPDATE wp_2_posts SET guid= REPLACE(guid, 'wp.domain.com', 'www.domain.com') WHERE guid LIKE ('%wp.domain.com%');
UPDATE wp_3_posts SET guid= REPLACE(guid, 'wp.domain.com', 'www.domain.com') WHERE guid LIKE ('%wp.domain.com%');
UPDATE wp_4_posts SET guid= REPLACE(guid, 'wp.domain.com', 'www.domain.com') WHERE guid LIKE ('%wp.domain.com%');
UPDATE wp_5_posts SET guid= REPLACE(guid, 'wp.domain.com', 'www.domain.com') WHERE guid LIKE ('%wp.domain.com%');
UPDATE wp_6_posts SET guid= REPLACE(guid, 'wp.domain.com', 'www.domain.com') WHERE guid LIKE ('%wp.domain.com%');

Remember sometimes the domain is not in the database, if needed you can do this command to search code files

grep -R 'wp.domain.com' /var/www/html