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

Try to run with the following command:

mysqldump -u root -p database -r output.sql

instead of redirecting the output with arrow '>'

Answer 2

It took me Two days to find out I had the same problem and solved it when trying to export a database in arabic using mysqldump and each time you open the outputfile in notepad++ its encoding is in ansi and you need it to be utf-8 my code for export and import was as follows it turns out i was right but i was checking the database on the terminal but the terminal doesn't support encoding and i just tried checking it with phpmyadmin and its good don't try to open the file in notepad++ or just try your application directly it will work.

export command

mysqldump -uuser -ppassword --default-character-set=utf8 dbname > outputfile //or even if you use -r instead of > no difference

import command mysql -uuser -ppassword --default-character-set=utf8 dbname < outputfille // please take in mind this does override existing database