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?

Solution 1

This worked for me:

1. ssh the server and connect.
2. create db dump running

mysqldump -h my_guid.cloud.database.com -u my_user -p my_database_name -r ~/my_db_backup.sql

The console will prompt and ask for the password and there you can type it, my pwd had special characters so i was able to run this command with ease

Solution 2

Try to run with the following command:

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

instead of redirecting the output with arrow '>'

Solution 3

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

Solution 4

This fixed the issue for me.

1. Import the double encoded input.sql

2. Export it again mysqldump -h "$DB_HOST -u "$DB_USER" -p"$DB_PASSWORD" --opt --quote-names --skip-set-charset --default-character-set=latin1 "$DB_NAME" > output.sql

3. Import clean output.sql

How to restore the database double encoded by mysqldump

Solution 5

It's very important to make sure the client is set to UTF8. Confusingly, it's not the same as setting your database to UTF8. Open /etc/my.cnf and make sure you have default-character-set = utf8 under [mysql] not just under [mysqld]

Now you should be able to pipe UTF8 dumps directly into the mysql client. I also recommend using the option --hex-blob on the mysqldump command as mysqldump is not perfect.

View more solutions