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

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 √∏ characters instead of øæåØÆå.

Anyone know where I've gone wrong?

mysqlsilverstripe


Solution 1:[1]

Try to run with the following command:

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

instead of redirecting the output with arrow '>'

Solution 2:[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

Solution 3:[3]

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 4:[4]

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.

Solution 5:[5]

I succeed as follows:

mysql --default-character-set=utf8 -u ..

May this will help you.

Solution 6:[6]

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

Solution 7:[7]

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

Solution 8:[8]

Only way that worked for me was to export the utf-8 tables as latin-1 (character set of file: iso-8859-1) in phpmyadmin.

Open the exported file in notepad++ convert to UTF8 (with BOM)

Then upload file and import with SOURCE dump.sql.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user614467
Solution 2
Solution 3 Community
Solution 4 thomas-peter
Solution 5 Sean
Solution 6 Mike
Solution 7 unutbu
Solution 8 Erik Höglund

Related Questions