15

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?

Improve this question
3
  • 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

10 Answers 10

Active Oldest Votes
8

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/

Improve this answer
1
  • 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
8

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.
Improve this answer
1
  • There's quite a few steps. After you mv olddir.com newdir.com you'll need to reconfigure your webserver, restart the webserver. Follow the codex instructions here and then you should be able to login as admin. That should get you started :-) – PJ Brunet Feb 17 '15 at 23:00
7

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

Improve this answer
1
  • 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
5

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';
Improve this answer
3

You can set these values with constants in the wp-config.php. After this you can, when you want, change the entries in the database via plugin Adminer. Write this in the wp-config.php and the values in the DB are not relevant:

define('WP_HOME', 'http://example.com/to-wordpress');
define('WP_SITEURL', 'http://example.com/to-blog');
Improve this answer
2

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.

Improve this answer
2

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.

Improve this answer
2

Make sure you have the new database selected, then run some sql updates and replacement commands on the tables notably, wp_options, wp_posts, wp_postmeta.

Use the code as below and swap in your old and new URLs, no trailing slashes. Also if necessary change the table prefix values where applicable (ie wp_ )

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');
Improve this answer
1

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!

Improve this answer
1

I see you want to move your WordPress site from the development environment to a new server or production server.

Recently, I have moved my site to the production server and I face the same situation. I need to update the site URL because WordPress stored the site URL in Database.

I found good MySQL query using that I can easily change URL directly from the Database like.

SET @search := "http://olddomain.com";
SET @replace := "http://newdomain.com";

UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "siteurl";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "home";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "product_list_url";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "shopping_cart_url";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "checkout_url";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "widget_text";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "headerImageLogo";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "cunjo_plugin_url";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "wpsr_socialbt_data";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "issuem";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "_transient_dgxdonate_t70pbg1htu_1396322372";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "dgx_donate_log";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "headerBackgroundImage";
UPDATE `wp_options` SET `option_value` = REPLACE(option_value, @search, @replace) WHERE option_name = "transact_url";
UPDATE `wp_postmeta` SET `meta_value`=REPLACE(meta_value, @search, @replace);
UPDATE `wp_usermeta` SET `meta_value`=REPLACE(meta_value, @search, @replace);
UPDATE `wp_posts` SET `guid`=REPLACE(guid, @search, @replace);
UPDATE `wp_posts` SET `post_content`=REPLACE(post_content, @search, @replace);

You just need to replace your site URL from old domain name to new domain then execute the query on the MySQL using PHPMYADMIN.

For more reference, You can read this Tutorial.

https://tryvary.com/wordpress-change-url-in-database-using-mysql-query/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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