Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have a Wordpress database which was installed in a development environment... thus, all references to the site itself have a fixed IP address (say 192.168.16.2). Now, I have to migrate that database to a new Wordpress installation on a hosting. The problem is that the SQL dump contains a lot of references to the IP address, and I have to replace it with: my_domain.com.

I could use sed or some other command to change the that from the command line, the problem is that there are a lot of configuration data which uses JSON. So what? Well, as you know, JSON arrays uses things like: s:4: to know how many chars an element has, and thus, if I just replace the IP with the domain name, the configuration files will get corrupted.

I used an app for Windows some years ago that allows to change values in a database and takes care of the JSON arrays. Unfortunately, I forgot the name of the app... so the question is: do you know any app that allows me to do what I want?

share|improve this question
1  
This isn't a complete answer, but might help you out in searching for one: the data you're looking at is not JSON formatted. JSON doesn't (normally) store value types and lengths like that. What you're looking at is SERIALIZED data. A google search for "mysql replace serialized" yields this page, which might help: davidcoveney.com/… –  MathSmath Dec 21 '10 at 23:10
    
Hey, your comment is the only answer that was useful for me... move it to an answer to mark it as correct. Thanks! –  Cristian Dec 22 '10 at 14:39
    
Glad it helped! I've posted it as an answer. –  MathSmath Dec 22 '10 at 20:16

8 Answers 8

up vote 5 down vote accepted

The data you're looking at is not JSON formatted. JSON doesn't (normally) store value types and lengths like that. What you're looking at is SERIALIZED data. A google search for "mysql replace serialized" yields this page, which might help: http://davidcoveney.com/mysql-database-search-replace-with-serialized-php/

share|improve this answer
    
Thank you... I worked as PHP developer for 2 years. I can't believe I forgot the difference between something serialized and a JSON string. –  Cristian Dec 22 '10 at 20:56

This is a very old question, but since I came across it while looking for something else, I thought I would add this for future reference.

I think the simplest, most complete way to do this is using searchreplacedb2.php. It can be found here: http://interconnectit.com/products/search-and-replace-for-wordpress-databases/ along with an explanation of it's use.

It has definitely saved me a lot of time with migrating from dev to live environments on my wordpress sites.

Just make sure you delete it from a public server after you are done!

share|improve this answer

Simple SQL queries - no complicated REPLACE stuff required:

update wp_options set option_value = 'http://mynewdomain.com' where option_name = 'siteurl';

update wp_options set option_value = 'http://mynewdomain.com' where option_name = 'home';

Use those with PHPMyAdmin or any other way you prefer to access the database.

share|improve this answer

As Rarst said above there's only two settings in the database that NEEDS changing. After importing the database I log into PHPMyAdmin and edit the database direct.

I use a development server on my PC all the time to import datafeeds and they have the URL attached to the Posts along the lines of http://localhost/testsite/post-name/ and it's never caused a problem.

I used to use an SQL search and replace until I realised it didn't matter. I've migrated a few sites from one domain to another and where I've had absolute URLs in the content I'll use the SQL search and replace option.

update wp_posts set post_content = replace(post_content,'http://www.olddomain/','http://www.newdomain/');

David

share|improve this answer
    
There are some plugins, like ContactForm7, which also saves the domain name inside a serialized string, among with other configuration variables. When you modify a serialized string it can get corrupted if its length changes, so in fact, sometimes, there are more things to change than just two settings. –  Cristian Dec 23 '10 at 3:03

Hi @Cristian:

Take a look my answer to this question:

It addresses your issues with cleaning up data and can be customized for specific data migration needs by using using hooks, too.

Hope it helps.

share|improve this answer

Codex has decent guide - Changing Site URL.

Basically there are several places there URL matter or not so much (I may be missing some):

  • home and siteurl options that control where WP thinks site is;
  • post GUIDs, these look like links but really they aren't - only identifiers. I am not absolutely sure they are irrelevant, but I had URLs change on test stack a lot and never bothered to update these.
  • links in post bodies;
  • possibly links in plugin settings.
share|improve this answer

You cans et this values wiht a constant in the wp-config.php and ready; after this you can, when you will, change the entries in the database via plugin Adminer. Write this in the wp-config.php and the values in the DB is not relevant:

define('WP_HOME', 'http://doamin.com/to-wordpress');
define('WP_SITEURL', 'http://domain.com/to-blog');
share|improve this answer

This is an amazing resource i have bookmarked that I go back to time and time again http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

They state

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.