Mysql: change http://old-domain.com to http://new-domain.com in all tables

Question

I use the statements from here:

http://www.mydigitallife.info/2007/10/01/how-to-move-wordpress-blog-to-new-domain-or-location/

but I had to modify them because I use a multi-user interface and has a different table structure (strange numbered tables). As you can see in my previous question , all this is REALLY problematic ... So my question is:

Can I just change http://old-domain.com to http://new-domain.com everywhere ... in every table in this database? How can I do a search and replace mysql? Or maybe something else would be better to use for this task?

Answer 1

You can dump all db into a .sql file and perform a replacement and import it.

To upload db to sql file, you can use mysqldump command, or you can do though phpmyadmin

mysqldump --opt -uUSERNAME -pPASSWORD -h MYSQLDOMAIN YOURDB > yourdbdump.sql 

To replace the text in the .sql file, you can do

 sed -ie 's/old-domain.com/new-domain.com/g' yourdbdump.sql 

To restore it back

 mysql -uUSERNAME -pPASSWORD -h MYSQLDOMAIN YOURDB < yourdbdump.sql 

Answer 2

WordPress MU is a pain in the ass to migrate.

There is nothing that goes to the table at the table and replaces these URLs. And you want to be careful when editing the sql dump file in case the URL is stored inside any serialized objects or arrays, since then you will destroy the integrity of this data.

If you want to do this directly in MySQL, you can, but it will take a long time. It will also be the safest.

This will be easiest in PHPMyAdmin, even for experienced SQL gurus, because PHPMyAdmin is just convenient because you will often edit the SQL query. Basic request:

 Update "table_name" SET "column_name" = REPLACE("column_name","find","replace") WHERE "column_name" LIKE "%find%" 

So, in your case for message tables:

 Update wp_1_posts SET post_content = REPLACE(post_content,"www.oldurl.com","www.newurl.com") WHERE post_content LIKE "%www.oldurl.com%" 

Next, see the postmeta tables. You may encounter a problem if there is serialized data. So, first do a search to see what the data looks like and see if there are any specific fields that you should leave outside of find / replace script. Your update for this table will look like the last:

 Update wp_1_postmeta SET meta_value = REPLACE(meta_value,"www.oldurl.com","www.newurl.com") WHERE meta_value LIKE "%www.oldurl.com%" 

If you find the fields you want to exclude, change the script like this:

 Update wp_1_postmeta SET meta_value = REPLACE(meta_value,"www.oldurl.com","www.newurl.com") WHERE meta_value LIKE "%www.oldurl.com%" AND meta_name NOT IN ('meta_name_1', 'meta_name_2') 

The options table must be done manually - you have too many options to fasten something, automating it. Find the siteurl , home and fileupload_url . Look at something else that can also save the values ​​and update them manually.

Then update wp_blogs and wp_site accordingly.

MU migration are the things that nightmares are made of. Go slowly, look at everything, and most importantly, do it all in a duplicate of your master data, to check first. The database location is cheap, there is no lost data.

Answer 3

You need the INSERT() function.