Skip to content

Instantly share code, notes, and snippets.

@chuckreynolds
Last active February 10, 2023 18:56
Show Gist options
  • Save chuckreynolds/6032234 to your computer and use it in GitHub Desktop.
Save chuckreynolds/6032234 to your computer and use it in GitHub Desktop.
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);
Copy link

ghost commented Feb 27, 2017

Thanks :)

@denis-cto
Copy link

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
Copy link

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
Copy link

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
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
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
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
Copy link

nncl commented Apr 23, 2018

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

@jeromeip
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]

@martinacostadev
Copy link

Thanks!

@clementbiron
Copy link

clementbiron commented Jan 21, 2020

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

@chuckreynolds
Copy link
Author

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

@clementbiron
Copy link

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