6

Recently I have transferred my WordPress from development server to live server. I noticed that I have to change all the "guid" with live domain url. Is there any mysql query or simple function available to change it easily.

7
  • Consult this page, at the bottom of this URL changing block you will find name of some plugins. :) – Mayeenul Islam Dec 8 '15 at 12:53
  • Thank you. But I want to change the guid only. I have done other things manually. I thought some mysql query is available to change it – Kvvaradha Dec 8 '15 at 13:11
  • 1
    I wish WordPress would not embed URLs in the GUID. They are not URLs. You shouldn't need to change them (or most of them). See: wordpress.stackexchange.com/a/90209/21376 – s_ha_dum Dec 8 '15 at 14:08
  • @s_ha_dum. So no need to change it right.? – Kvvaradha Dec 8 '15 at 14:18
  • Most likely not @Kvvaradha You can change them as it sounds as though you haven't deployed the site yet and if so it won't cause any trouble, but I doubt you actually need to change them. – s_ha_dum Dec 8 '15 at 14:21
17

It should be something like:

UPDATE wp_posts SET guid = REPLACE(guid, 'oldurl.com', 'newurl.com') WHERE guid LIKE 'http://oldurl.com/%';
  • oldurl.com - Previous URL shown in wordpress settings > general options
  • newurl.com - New URL
5

To improve the previous answers you should update all relevant tables such as (here table prefix is wp_):

UPDATE `wp_posts` SET guid = REPLACE(guid, 'oldsiteurl', 'newsiteurl') WHERE guid LIKE 'oldsiteurl%';

UPDATE `wp_postmeta` SET meta_value = REPLACE(meta_value, 'oldsiteurl', 'newsiteurl') WHERE meta_value LIKE 'oldsiteurl%';

UPDATE `wp_options` SET option_value = REPLACE(option_value, 'oldsiteurl', 'newsiteurl') WHERE option_value LIKE 'oldsiteurl%';

If you got any plugins that do redirections you should also change the permalinks there:

UPDATE `wp_redirection_404` SET url = REPLACE(url, 'oldsiteurl', 'newsiteurl') WHERE url LIKE 'oldsiteurl%';
3
  • Sounds good man, This answer will help some new peoples – Kvvaradha Mar 11 '20 at 4:15
  • 1
    take extreme care when replacing in wp_postmeta and wp_options tables .. often times there is serialized data stored in these tables, if the "oldsiteurl" and "newsiteurl" are different string-lengths, you will corrupt these fields. – farinspace Jun 11 '20 at 15:50
  • Yep @farinspace totally right, never do an automatic update on wp_postmeta and wp_options you will need to update those tables manually and in case edit the serialized strings with appropriate editor serializededitor.com – Jackie degli Innocenti Sep 20 '20 at 18:15
2

Use the WP-CLI to search and replace. The plugin MigrateDB also has a find and replace on export for the next time you transition.

2
  • Let me check it – Kvvaradha Dec 9 '15 at 0:59
  • Did you have any luck? – jgraup Dec 10 '15 at 22:05
1

WordPress documentation warns about changing guid entries in the WP database: "It is critical that you do NOT change the contents of this field."

https://wordpress.org/support/article/changing-the-site-url/#important-guid-note

2
  • 1
    But when the previous domain was localhost, the U in GUID doesn't really fit. :) – fuxia Jun 25 '20 at 19:40
  • Well, as mentioned in the wordpress documentation, when the GUID is used e.g. by feedreaders to identify unique content it still doesn't matter which domain is in the GUID. You shouldn't change it unless you want feedreaders to evaluate all changed GUIDs as 'new'. But when you move from 'localhost' to some other domain, feedreaders probably aren't your main concern... ;-) – user2186232 Jun 27 '20 at 12:29
1

I had forgotten that I wrote a SQL generator a few years back for this precise task:

https://farinspace.github.io/wp-migrate-gen/

Essentially the relevant SQL is as follows:

UPDATE `wp_options` SET `option_value` = REPLACE(`option_value`, 'a', 'b') WHERE `option_value` NOT REGEXP '^([adObis]:|N;)';
UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, 'a', 'b');
UPDATE `wp_posts` SET `post_excerpt` = REPLACE(`post_excerpt`, 'a', 'b');
UPDATE `wp_posts` SET `guid` = REPLACE(`guid`, 'a', 'b');
UPDATE `wp_comments` SET `comment_author_url` = REPLACE(`comment_author_url`, 'a', 'b');
UPDATE `wp_comments` SET `comment_content` = REPLACE(`comment_content`, 'a', 'b');
UPDATE `wp_links` SET `link_url` = REPLACE(`link_url`, 'a', 'b');
UPDATE `wp_postmeta` SET `meta_value` = REPLACE(`meta_value`, 'a', 'b') WHERE `meta_value` NOT REGEXP '^([adObis]:|N;)';
UPDATE `wp_usermeta` SET `meta_value` = REPLACE(`meta_value`, 'a', 'b') WHERE `meta_value` NOT REGEXP '^([adObis]:|N;)';
UPDATE `wp_termmeta` SET `meta_value` = REPLACE(`meta_value`, 'a', 'b') WHERE `meta_value` NOT REGEXP '^([adObis]:|N;)';
UPDATE `wp_commentmeta` SET `meta_value` = REPLACE(`meta_value`, 'a', 'b') WHERE `meta_value` NOT REGEXP '^([adObis]:|N;)';

The queries above will check for and skip PHP serialized data.

Using the generator, you can turn off "skip serialized data" if the old and new string lengths match.

WARNING: I would recommend that you always create a backup of your data before running any queries that add/remove/update content.

0

As you mentioned, there are a few variables that need to be changed in order for you to update to the new URL on your WordPress site.

  1. Go and download Interconnect IT's Database Search & Replace Script here
  2. Unzip the file and drop the folder in your live server where your WordPress is installed (the root) and rename the folder to replace (screenshot)
  3. Navigate to the new folder you created in your browser (ex: http://web.site/replace) and you will see the search/replace tool
  4. It should be pretty self-explanatory up to this point: enter your old URL in the search for… field and the new URL in the replace with… field

You can click the dry run button under actions to see what it will be replacing before you execute the script. Once you're done be sure to remove the /replace/ folder.

1
  • I routinely migrate wordpress websites and this is what I use. If anyone is interested, I do two find and replaces. First run is to replace all old domains with the new. Second is to enforce SSL. 1. Replace olddomain.com with newdomain.com 2. Enable regex, replace https?:\/\/([\w-]*\.|)newdomain\.com with https://$1newdomain.com. This regex will support subdomains. As always, dry run first and review a handful of the results. – Curtis Dec 21 '17 at 2:13

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.