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

9

22

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?

Gramineous answered 27/8, 2010 at 11:29 Comment(2)
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/, and it solved the problem. Appearently a bug in mysqldump is the cause of the problem.Ancylostomiasis
SOLUTION can be found here: #152788 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_connectOverweigh
C
2

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

Calabresi answered 14/2, 2019 at 15:55 Comment(0)
M
31

Try to run with the following command:

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

instead of redirecting the output with arrow '>'

Mission answered 12/2, 2011 at 19:14 Comment(3)
But why does this fix it?Icebound
@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.Grisham
I hope someone will explain what is the difference between > and -r option.Phagocytosis
F
19

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

Fredric answered 22/9, 2012 at 9:40 Comment(3)
Try it, i did have the same problem, if doesn't work let me now,when it does please check this as correct answerFredric
--default-character-set=utf-8 should be --default-character-set utf8 and --default=character-set=utf-8 should be --default-character-set utf8Rapping
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. 🏁Owen
G
6

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

Gramineous answered 4/12, 2013 at 12:6 Comment(0)
S
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.

Stambaugh answered 21/4, 2011 at 10:30 Comment(1)
** If you disagree with this - please commentStambaugh
B
5

I succeed as follows:

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

May this will help you.

Brazil answered 11/12, 2013 at 6:53 Comment(0)
L
2

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
Labyrinth answered 27/8, 2010 at 11:43 Comment(2)
Still skewed up characters are in the db.sql file! Ã where å should be.Gramineous
How are you checking the output file? Not all text editors are UTF-8 compatible, so may display a correct file incorrectly.Labyrinth
C
2

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

Calabresi answered 14/2, 2019 at 15:55 Comment(0)
G
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
Gaskell answered 27/8, 2010 at 12:25 Comment(2)
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.Gramineous
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.Gaskell
A
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.

Aphotic answered 6/3, 2019 at 16:2 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.