mysqldump and restore with special characters. øæåØÆÅ

Question

Locally I do this to dump and move a database, upgrading silverstripe 2.3 to 2.4:

mysqldump --opt  --default-character-set=latin1 --skip-set-charset --user=$root -p$password $oldDatabase -r db.sql  

iconv -f LATIN1 -t UTF8 db.sql > db_utf.sql 


CREATE DATABASE $newDatabase CHARACTER SET utf8 COLLATE utf8_swedish_ci; FLUSH PRIVILEGES; GRANT ALL PRIVILEGES ON $newDatabase . * TO '$newUser'@'localhost';  FLUSH PRIVILEGES;
SET NAMES utf8; SOURCE db_utf.sql;

And it works, but on the server Ubuntu 8.04, with mysql Ver 14.12 Distrib 5.0.51a. I get crazy √∏ charterers instead of øæåØÆå.

Anyone know where I've gone wrong?

Answer 1

Have you tried it without the iconv step?

Here's what I use when dumping UTF-8 databases:

mysqldump \
    -u $DB_USER -p"$DB_PASS" \
    --default-character-set=Latin1 \
    --result-file=$DATAFILE

And to restore:

mysql -u $DB_USER -p"$DB_PASS" \
    --default-character-set=latin1 < $DATAFILE

Answer 2

Perhaps just copy the tables to $newDatabase as latin1. Then, for each table, execute:

ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_swedish_ci

Answer 3

Because I wanted to back up the current database before an upgrade. mysqlhotcopy is a solution that worked for me (thanks unutbu).

http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html