Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
UPDATE: Use WP-CLI find-replace command to edit URLs in your database. https://developer.wordpress.org/cli/commands/search-replace/ Use this SQL script when changing domains on a WordPress site. Whether you’re moving from an old domain to a new domain or you’re changing from a development domain to a production domain this will work. __STEP1: al…
/* Use WP-CLI instead https://developer.wordpress.org/cli/commands/search-replace/ */
SET @oldsite='http://oldsite.com';
SET @newsite='http://newsite.com';
UPDATE wp_options SET option_value = replace(option_value, @oldsite, @newsite) WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = replace(post_content, @oldsite, @newsite);
UPDATE wp_links SET link_url = replace(link_url, @oldsite, @newsite);
UPDATE wp_postmeta SET meta_value = replace(meta_value, @oldsite, @newsite);
/* only uncomment next line if you want all your current posts to post to RSS again as new */
#UPDATE wp_posts SET guid = replace(guid, @oldsite, @newsite);
@arupgsh

This comment has been minimized.

Copy link

arupgsh commented Dec 31, 2015

Thanks for the code.

@rickyok

This comment has been minimized.

Copy link

rickyok commented Mar 7, 2016

Thanks for the code :P

@hshhhhh

This comment has been minimized.

Copy link

hshhhhh commented Feb 27, 2017

Thanks :)

@denis-cto

This comment has been minimized.

Copy link

denis-cto commented Nov 27, 2017

Sometimes error with collation occurs. (Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT))
To solve it, run this before
`alter table wp_options CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

alter table wp_posts CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

alter table wp_links CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

alter table wp_postmeta CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';`

@Artistan

This comment has been minimized.

Copy link

Artistan commented Nov 27, 2017

I used this to find all the relevant databases. in case an extension has urls...

File -- dump.sh

#!/bin/bash

mysqldump "$1" > dump.sql
cat dump.sql | grep http://dnrstar.com | grep -io "INTO [^ ]*" | grep "INTO \`" | sort --unique | grep -o "\`.*\`"

execute ./dump.sh

and the results should look like this...

`wp_options`
`wp_postmeta`
`wp_posts`
`wp_rg_lead`
`wp_usermeta`
`wp_yoast_seo_links`

then it is just a matter of checking the columns for each table ...

@weishenaustralia

This comment has been minimized.

Copy link

weishenaustralia commented Dec 22, 2017

as Artistan mentioned, old domain url may exist in other tables such as the ones created by plugins.
@Artistan, do you have a working script share with us?
thanks

@theKindlyMallard

This comment has been minimized.

Copy link

theKindlyMallard commented Dec 27, 2017

Hi.

Thanks for the code.

In case of this MySQL issue #1270 - Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT), (utf8mb4_unicode_ci,IMPLICIT), (utf8mb4_unicode_ci,IMPLICIT) for operation 'replace' (after executing script) simply do not use variables.
So script updates looks like exapmple below:

UPDATE wp_options SET option_value = replace(option_value, 'http://oldsite.com', 'http://newsite.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = replace(post_content, 'http://oldsite.com', 'http://newsite.com');
UPDATE wp_links SET link_url = replace(link_url, 'http://oldsite.com', 'http://newsite.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://oldsite.com', 'http://newsite.com');
#UPDATE wp_posts SET guid = replace(guid, 'http://oldsite.com', 'http://newsite.com');

Solution used with

  • 5.7.19 - MySQL Community Server
  • phpMyAdmin 4.7.4

I would like to remind you that we should also check the plugins tables - links may be there too 😉

Cheers
@jaircuevajunior

This comment has been minimized.

Copy link

jaircuevajunior commented Jan 24, 2018

Hi guys,

First of all thanks for the tips, they are very appreciated.

What do you think of find/replace within dumpfile method?

I've came across this method after having some problems using this method with other tables, as Artistan has mentioned this possibility.

So I started to think about find/replacing inside dumpfile itself.

Looks like it works...

I have created a gist explaining the simplicity of this process [link removed]

@omittelstaedt

This comment has been minimized.

Copy link

omittelstaedt commented Feb 12, 2018

If you get the error

#1270 - Illegal mix of collations ...

then just add the collation for the vars. I.e. for for collation utf8_general_ci:

SET @oldsite='http://oldsite.com' COLLATE utf8_general_ci; 
SET @newsite='http://newsite.com' COLLATE utf8_general_ci;

....
# rest of the code as above
@nncl

This comment has been minimized.

Copy link

nncl commented Apr 23, 2018

I've tested what @jaircuevajunior made and worked like a charm, thanks mate.

@jeromeip

This comment has been minimized.

Copy link

jeromeip commented Mar 22, 2019

The procedure proposed by @jaircuevajunior is quite straightforward, however it will only fully work if the former URL and the new one have the same length. Therefore I suggest that you keep this in mind if, for example, you're working on a test version and plan to move to production.

When the lengths differs, you need to use a more sophisticated script that takes into consideration variable serialization frequently used in WordPress database.
For this, I've used successfully a few times a script developed by Interconnect/IT (it's free but you need to complete a form to get it).
[link removed]

@kaizendevar

This comment has been minimized.

Copy link

kaizendevar commented Oct 2, 2019

Thanks!

@posykrat

This comment has been minimized.

Copy link

posykrat commented Jan 21, 2020

You could use this tool to generate your sql script : [link removed]

@chuckreynolds

This comment has been minimized.

Copy link
Owner Author

chuckreynolds commented Jan 23, 2020

What everybody SHOULD use anymore is WP-CLI Find/Replace. End of conversation. https://developer.wordpress.org/cli/commands/search-replace/

@posykrat

This comment has been minimized.

Copy link

posykrat commented Jan 23, 2020

I couldn't agree more but:

  • wp cli is not available on all hosting services
  • using a command line tool is not possible for the majority of users

that's why extensions and tools exist

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.