Site paths broken in wordpress on new server? SQL query

Question

I am trying to migrate a wordpress website to a new server, which is on mediatemple.

In there migration guide, it suggests updating the site preview/paths to the IP of the server.

It gives a SQL query to run in phpMyAdmin which is:

UPDATE `dbname`.`wp_options` SET `option_value` = 'http://xxx.xxx.xxx.xxx' WHERE `wp_options`.`option_id` =1 AND `wp_options`.`blog_id` =0 AND CONVERT( `wp_options`.`option_name` USING utf8 ) = 'siteurl' LIMIT 1 ; 

But can't get it to work as it reports an error when I run it.

This is the updated query inputted with my details:

UPDATE `db87924`.`db87924_rockhaq` SET `option_value` = 'http://64.207.145.39' WHERE `wp_options`.`option_id` =1 AND `db87924_rockhaq`.`blog_id` =0 AND CONVERT( `wp_options`.`option_name` USING utf8 ) = 'siteurl' LIMIT 1 ; 

I'm guessing that 'option_value', 'wp_options', 'option_id' and 'option_name' need to be replaced with something but have no idea what and can't find any more info on it.

If anyone has any ideas, that would be great!

Thanks

Answer 1

wp-config.php might also has some hardcoded paths which need to be changed.

http://codex.wordpress.org/Editing_wp-config.php#WordPress_address_.28URL.29

Answer 2

Always backup the database in case something goes awry. :D

This is the query I would use to migrate the WP install and has worked for me so far (might be different for you):

UPDATE wp_options SET option_value='http://64.207.145.39' WHERE option_name='siteurl';
UPDATE wp_options SET option_value='http://64.207.145.39' WHERE option_name='home';

However, if your WP install hosts more than 1 blog, then I would update the query to:

UPDATE wp_options SET option_value='http://64.207.145.39' WHERE option_name='siteurl' AND blog_id=0;
UPDATE wp_options SET option_value='http://64.207.145.39' WHERE option_name='home' AND blog_id=0;

These change the paths/links in Wordpress (in my experience, both settings need to be changed: home and siteurl).

If this works, you would probably need to run the query again when the DNS is transferred.