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…

wordpress-change-domain-migration.sql

/* 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);

Thanks :)

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';`

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 ...

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

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

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]

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

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

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]

Thanks!

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

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

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