Moving a database from WordPress to WordPress Mu

Some rough notes, before I forgot what I did.

mysqldump --default-character-set="latin1" -elt database_name | sed 's/wp_/wp_{new blogs id}_/' > database_name.sql

–default-character-set is required because of MySQL’s unrestrained enthusiasm for fucking up encoding by assuming everything is encoded in utf8. Would it kill them to have mysqldump simply look up the encoding first? Apparently the answer is “yes”.

Speaking of which, you need to change the my.cnf file to default to utf8, and tell your webserver to serve utf8, AND tell PHP to default to utf8. Then the content will come across OK.

Zip the dumpfile, download, upload. Then play to the mysql line program.

mysql -u root -p target_database < database_name.sql

Be prepared for warts. In particular older installations of WordPress carry _category columns on some tables which will trip up the mysql insert. The solution is to drop those columns from the source database first.

You also need to update the URLs. For reasons inscrutable to mere mortals, WordPress Mu doesn’t store uploaded files in wp-content/uploads. Instead it changes that to wp-content/files. This serves no purpose, so far as I can tell, but it does force me to make updates to the database:

update wp_{newblogid}_posts set post_content = replace (post_content, '/wp-content/uploads/', '/wp-content/files/') where post_content like '%/uploads/%';

update wp_{newblogid}_posts set guid = replace (guid, '/wp-content/uploads/', '/wp-content/files/') where guid like '%/uploads/%';

Edit: commands used to move skepticlawyer.

# Change user ids and import

update wp_usermeta set user_id = 6 where user_id = 4;
update wp_users set ID = 6 where ID = 4;

mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_usermeta | sed 's/wp_/wp_3_/' > sl.usermeta.sql
mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_users | sed 's/wp_/wp_3_/' > sl.users.sql

# Update user ids on posts and comments
update wp_posts set post_author = 6 where post_author = 4;
update wp_comments set user_id = 6 where user_id = 4;

# Update URLs for files and attachments
update wp_posts set post_content = replace (post_content, '/wp-content/uploads/', '/wp-content/files/') where post_content like '%/uploads/%';
update wp_posts set guid = replace (guid, '/wp-content/uploads/', '/wp-content/files/') where guid like '%/uploads/%';

# Dump tables
mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_comments | sed 's/wp_/wp_3_/' > sl.comments.sql
mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_links | sed 's/wp_/wp_3_/' > sl.links.sql

mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_postmeta | sed 's/wp_/wp_3_/' > sl.postmeta.sql
mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_posts | sed 's/wp_/wp_3_/' > sl.posts.sql
mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_term_relationships | sed 's/wp_/wp_3_/' > sl.term_relationships.sql
mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_term_taxonomy | sed 's/wp_/wp_3_/' > sl.term_taxonomy.sql
mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_terms | sed 's/wp_/wp_3_/' > sl.terms.sql
mysqldump --default-character-set="latin1" --skip-opt --insert-ignore -elt skepticlawyer wp_options | sed 's/wp_/wp_3_/' > sl.options.sql

This entry was posted in Technical Notes. Bookmark the permalink.

1 Response to Moving a database from WordPress to WordPress Mu

  1. Pingback: Journal de Jacques :: Automated backups for Ozblogistan

Comments are closed.