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

3 comments

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
   

   Log in to Reply

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

   Log in to Reply

3. You want the INSERT() function.

   Log in to Reply