Last Updated: October 07, 2020

SQL to Change WordPress Domain URL

I found this a couple of years ago and it has saved me plenty of time and headache. Thanks to Chuck Reynolds for posting. I now have it on my gist for quick reference.

The Goods

/* Begin Here */
UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');
UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_url = replace(link_url, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_image = replace(link_image, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_usermeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
/*UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl' OR option_name = 'widget_text' OR option_name = 'dashboard_widget_options';*/
UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com');

The Cite

http://forrst.com/posts/SQL_to_Change_WordPress_Domain_URL-DLR

Written by Matthew Hauth

Say Thanks

Respond

Related protips

Splitting Strings with MySQL

316.5K

The //* // /// comment toggle trick

125.2K

How to convert string to integer (PHP & MySQL)

108.5K

2 Responses

Add your response

gxela

I do the same thing, only I also do extra run for escaped urls, and include any plugin tables too.

LOCAL_DOMAIN="example.local";
SITE_DOMAIN="www.example.com";

WP_DIR="wp";

LOCAL_URL="http://$LOCAL_DOMAIN";
LOCAL_URL_="http:\/\/$LOCAL_DOMAIN";
SITE_URL="http://$SITE_DOMAIN";
SITE_URL="http:\/\/$SITE_DOMAIN";
LOCAL_WP_URL="$LOCAL_URL/$WP_DIR";
LOCAL_WP_URL_="$LOCAL_URL\/$WP_DIR";
SITE_WP_URL="$SITE_URL/$WP_DIR"
SITE_WP_URL_="$SITE_URL\/$WP_DIR"

mysql -u"$DB_USER" -p"$DB_PASSWORD" -h"$DB_HOST" "$DB_NAME" << EOFMYSQL
UPDATE $DB_NAME.wp_options SET option_value = replace(option_value, '$LOCAL_WP_URL', '$SITE_WP_URL') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE $DB_NAME.wp_options SET option_value = replace(option_value, '$LOCAL_URL', '$SITE_URL') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE $DB_NAME.wp_posts SET guid = replace(guid, '$LOCAL_WP_URL','$SITE_WP_URL');
UPDATE $DB_NAME.wp_posts SET guid = replace(guid, '$LOCAL_URL','$SITE_URL');
UPDATE $DB_NAME.wp_posts SET post_content = replace(post_content, '$LOCAL_WP_URL', '$SITE_WP_URL');
UPDATE $DB_NAME.wp_posts SET post_content = replace(post_content, '$LOCAL_URL', '$SITE_URL');
UPDATE $DB_NAME.wp_postmeta SET meta_value = replace(meta_value,'$LOCAL_WP_URL','$SITE_WP_URL');
UPDATE $DB_NAME.wp_postmeta SET meta_value = replace(meta_value,'$LOCAL_URL','$SITE_URL');

UPDATE $DB_NAME.wp_links SET link_target = replace(link_target,'$LOCAL_WP_URL_','$SITE_WP_URL_');
UPDATE $DB_NAME.wp_links SET link_target = replace(link_target,'$LOCAL_URL_','$SITE_URL_');
UPDATE $DB_NAME.wp_usermeta SET meta_value = replace(meta_value,'$LOCAL_WP_URL_','$SITE_WP_URL_');
UPDATE $DB_NAME.wp_usermeta SET meta_value = replace(meta_value,'$LOCAL_URL_','$SITE_URL_');
EOFMYSQL

over 1 year ago ·

dimasmagadan

I do a manual search&replace in a downloaded SQL dump prior to uploading it :)
It is fast, simple and reliable.

The only trouble is if you have a premium theme with its own settings dashboard, those settings are stored as serialized data, and they include your site path, you may broke something.

over 1 year ago ·