Board index Linux General Stuffs

Moderator: chandranjoy

Updating WordPress mysql DB after Moving to new hosting URL

Postby chandranjoy » Fri Nov 18, 2011 12:31 am

Updating WordPress mysql Database content After Moving to a new WebHost and URL

After moving a WordPress site to a new URL either live or to a development server, the new URL strings need to be updated in the various mysql tables.

Create the new database, and import the data either in phpmyadmin or mysql directly in the command line. Make sure you have the right database selected, then run some sql updates and replacement commands on the tables notably, wp_options, wp_posts, wp_postmeta.

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');


Image

or via command line:

username@[~/Desktop]: mysql -u root -p databasename
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 892
Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');
Query OK, 0 rows affected (0.02 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');
Query OK, 0 rows affected (0.05 sec)
Rows matched: 964 Changed: 0 Warnings: 0

mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');\g
Query OK, 0 rows affected (0.01 sec)
Rows matched: 686 Changed: 0 Warnings: 0


Finally update your WordPress config file to reflect the new database, “wp-config.php” should be in your web document root – change, databasename, username, password and host values:
define('DB_NAME', 'databasename');

/** MySQL database username */
define('DB_USER', 'username');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', 'localhost');


Now everything should link up.

References:
http://codex.wordpress.org/Changing_The_Site_URL
http://www.coolestguyplanet.net/updatin ... a-new-url/
http://sakinshrestha.com/wordpress/how- ... -location/

Good Luck..
chandranjoy
Site Admin
 
Posts: 283
Joined: Fri Oct 23, 2009 11:19 pm

Return to General Stuffs

Who is online

Users browsing this forum: No registered users and 1 guest


cron