One of the more tedious things about WordPress is the way that it hardcodes complete fully qualified urls into the database, which makes quickly switching from a development server to a live server an irritation. It only takes a few SQL queries to sort it out, but I’m forever forgetting then exactly. The SQL below lets you define a few constants that then get feed into the UPDATE scripts. I usually develop initially on a local web server with a .dev TLD, so with this little script I just have to set 3 SQL variables, run it and job done ready to export the database to the live server. Which is nice.
START TRANSACTION; # CONFIGURE THESE VARIABLES FIRST SET @DOMAIN_NAME = "the-domain-in-question"; SET @DEV_SERVER_SUFFIX = ".dev"; SET @LIVE_SERVER_SUFFIX = ".com"; # CONSTANTS (IF YOUR DEV SERVER ISN'T MAPPED BY SUFFIX, CONFIGURE AS APPROPRIATE) SET @WEB_SERVER_SUFFIX = "http://www."; SET @DEV_SERVER = CONCAT ( @DOMAIN_NAME, @DEV_SERVER_SUFFIX ); SET @LIVE_SERVER = CONCAT ( @DOMAIN_NAME, @LIVE_SERVER_SUFFIX ); SET @WEB_DEV_SERVER = CONCAT ( @WEB_SERVER_SUFFIX, @DOMAIN_NAME, @DEV_SERVER_SUFFIX ); SET @WEB_LIVE_SERVER = CONCAT ( @WEB_SERVER_SUFFIX, @DOMAIN_NAME, @LIVE_SERVER_SUFFIX ); # UPDATE THE SERVER NAME UPDATE `wp_options` SET `option_value` = @WEB_LIVE_SERVER WHERE `option_value` = @WEB_DEV_SERVER; #UPDATE THE GUID urls UPDATE wp_posts SET guid = REPLACE(guid, @WEB_DEV_SERVER, @WEB_LIVE_SERVER); #UPDATE and urls referenced in the post content UPDATE wp_posts SET post_content = REPLACE(post_content, @DEV_SERVER, @LIVE_SERVER) WHERE post_content LIKE ( CONCAT( "%",@DEV_SERVER, "%") ); COMMIT;