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 can be found here: http://stackoverflow.com/questions/152288/loading-utf-8-encoded-dump-into-mysql/ As many of us, use **.php** commands to do a backup, you may be unable to correctly export the database with UTF8 data. so, the problem is, that you need to use `mysql_query("SET NAMES 'utf8'");` after mysql_connect — T.Todua, Jan 16 '14 at 09:11
I tried the different solutions here without luck. Then I tried alternative 2 in this blog: [http://www.orthogonalthought.com/blog/index.php/2007/05/mysql-database-migration-and-special-characters/](http://www.orthogonalthought.com/blog/index.php/2007/05/mysql-database-migration-and-special-characters/), and it solved the problem. Appearently a bug in mysqldump is the cause of the problem. — Toggi, Dec 12 '11 at 13:29

score 23 · Answer 1 · answered Feb 12 '11 at 19:14

23

Try to run with the following command:

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

instead of redirecting the output with arrow '>'

answered Feb 12 '11 at 19:14

user614467

239
2
3

2

But why does this fix it? – Brian Mar 29 '18 at 16:51
1

@Brian, maybe because when we use '>' to write in any file it uses default encoding from our OS. In my case, CentOS. But when we use -r flag we let mysqldump handle file write, and thus don't have encoding problems. If you try any command followed by '>' to write a file it will work because the OS just write what would be printed in the bash cli. – Felipe Pena Aug 23 '18 at 18:10
I hope someone will explain what is the difference between > and -r option. – Deniz Porsuk Jul 07 '20 at 14:39

Bakly · Answer 2 · 2013-11-24T07:32:17.523

14

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

edited Nov 24 '13 at 07:32

answered Sep 22 '12 at 09:40

Bakly

590
5
17

Try it, i did have the same problem, if doesn't work let me now,when it does please check this as correct answer – Bakly Aug 24 '13 at 08:34
1

`--default-character-set=utf-8` should be `--default-character-set utf8` and `--default=character-set=utf-8` should be `--default-character-set utf8` – Diego Vieira Nov 22 '13 at 09:49
I just spend hours testing a dump / restore cycle and it is worth noting that `utf8` in mysql is not exactly `utf8`. What is more likely needed is actually `--default-character-set=utf8mb4`. I suggest that in order to (more) fully verify your restore ability and see this for yourself then store a page full of emojis in the database and try a backup/restore. I have confirmed this works on local dump to local restore and AWS Aurora RDB dump to local restore. – Techmag May 03 '19 at 16:34

score 6 · Answer 3 · edited May 23 '17 at 10:29

6

This fixed the issue for me.

Import the double encoded input.sql
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
Import clean output.sql

How to restore the database double encoded by mysqldump

edited May 23 '17 at 10:29

Community

1
1

answered Dec 04 '13 at 12:06

Mario Michelli

493
1
5
10

score 4 · Answer 4 · answered Dec 11 '13 at 06:53

4

I succeed as follows:

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

May this will help you.

answered Dec 11 '13 at 06:53

Sean

987
10
12

score 4 · Answer 5 · answered Apr 21 '11 at 10:30

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.

answered Apr 21 '11 at 10:30

thomas-peter

6,952
6
37
56

** If you disagree with this - please comment – thomas-peter Jun 04 '11 at 15:59

score 1 · Answer 6 · answered Aug 27 '10 at 11:43

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

answered Aug 27 '10 at 11:43

Mike

20,127
2
38
65

1

Still skewed up characters are in the db.sql file! Ã where å should be. – Mario Michelli Aug 27 '10 at 12:36
How are you checking the output file? Not all text editors are UTF-8 compatible, so may display a correct file incorrectly. – Mike Aug 27 '10 at 13:09

score 1 · Answer 7 · answered Aug 27 '10 at 12:25

1

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

answered Aug 27 '10 at 12:25

unutbu

711,858
148
1,594
1,547

I've been looking at the mysqldump and no matter what default-character-set I use I get encoded letters:Ã. But if I look at the same data in mysql the letters are fine ø; So the problem is getting the data out not putting it in. – Mario Michelli Aug 31 '10 at 14:00
Can you stop mysql temporarily with `sudo invoke-rc.d mysql stop` ? Or are you on some production server which must not stop? If you can stop mysql, you can directly copy the database in /var/lib/mysql. Once you've backed that up, restart mysql with `sudo invoke-rc.d mysql start` and then perhaps you can try the `ALTER TABLE` command above directly on the current database tables. It should convert you to utf8 without using `mysqldump`. – unutbu Aug 31 '10 at 14:19

score 1 · Accepted Answer · answered Feb 14 '19 at 15:55

This worked for me:

ssh the server and connect.
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

score 1 · Answer 9 · answered Mar 06 '19 at 16:02

1

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.

answered Mar 06 '19 at 16:02

Erik Höglund

11
1

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

9 Answers9