Always Get Better

How to Move a WordPress Database

One of the most common requirements for web developers is the ability to switch code from development servers to live production environments. This can be tricky if you’re working with WordPress; moving the files is dead simple, but since WordPress uses canonical URLs you have to be careful if you are trying to transfer any of your database content.

Canonical URLs force your site to use the same base path (www.yourdomain.com rather than yourdomain.com). But if you are working in a development environment – e.g. the development site’s address isn’t the same as your web site, but is rather something like 127.0.0.1 – you need to be able to make the switch to WordPress without bringing your site down.

Since I end up having to look for this information so often, here are the steps I use to accomplish this amazing feat:

Download The Database

From the shell prompt of your server, dump WordPress’ MySQL database into a backup file:

mysqldump –-add-drop-table -uusername -ppassword databasename > mysqlbackup.DATE.sql

Move it over to the new server and run this command to overwrite your target:

mysql -udb##### -p -hinternal-db.s#####.gridserver.com db#####_dbname < mysqlbackup.DATE.sql

Update the Database Paths

Log into your MySQL database and issue this update command to ensure WordPress redirects to the new server:

UPDATE wp_options SET option_value = replace(option_value, ‘http://www.old-domain.com’, ‘http://www.new-domain.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

Next update the post URLs:

UPDATE wp_posts SET guid = replace(guid, ‘http://www.old-domain.com’,’http://www.new-domain.com’);

Finally, update your posts’ content to fix any internal links:

UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.old-domain.com’, ‘http://www.new-domain.com’);

That’s all! Repeat these steps when moving from production to development and vise-versa.

As I said I typically search for this information whenever I need to move WordPress sites. I find the SQL queries at: http://www.mydigitallife.info/2007/10/01/how-to-move-wordpress-blog-to-new-domain-or-location/

Tags: , , ,

Leave a Reply

Notice: Undefined variable: user_ID in /usr/share/nginx/html/wp-content/themes/getbetter/comments.php on line 65