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

Question

I'm using 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'm using multi-user and it has different table structure (weird numbered tables). As you can see in my previous question it's all REALLY problematic... So my question is:

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

Answer 1

You could dump the whole db to .sql file and do the replace and import it back.

To dump db to sql file you could use mysqldump command or you can do though phpmyadmin

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

To replace text in .sql file you could do like

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

You want the INSERT() function.

Answer 3

WordPress MU is a pain in the ass to migrate.

There's nothing that'll go table to table and replace those URLs. And you want to be careful editing the sql dump file in case the url is stored inside any serialized objects or arrays as you'll then destroy the integrity of that data.

If you want to do it directly inside MySQL you can, but it'll be time consuming. It'll also be the safest.

This'll be easiest in PHPMyAdmin, even for experience SQL gurus as PHPMyAdmin is just plain convenient as you'll be editing the SQL query often. The base query is:

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

So, in your case, for the post 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, look at the postmeta tables. You could run in to trouble here if there is serialized data. So do a search first to see what the data looks like and see if there's any specific fields you should leave out of the find/replace script. Your update for this table will look much like the last one:

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 found fields that you need to exclude, modify the script like so:

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 should be done by hand - you've got too much potential to screw something up by automating it. Look for the siteurl, home and fileupload_url fields. Look for anything else that may have stored the values as well and update those by hand.

Then Update the wp_blogs and wp_site appropriately.

Migrating MU is the stuff nightmares are made of. Go slow, look at everything, and, most importantly do all this on a duplicate of your main data to test first. The database space is cheap, lost data is not.