[SOLVED] mysqldump and utf8 encoding

[random1]

How do you use mysqldump to backup a utf8 encoded database?

 

Whenever I use it i get an output file in ANSI encoding.

 

I ran:

 

SHOW VARIABLES LIKE 'character_set%';

 

I get:

 

character_set_client	utf8
character_set_connection	utf8
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8
character_set_server	utf8
character_set_system	utf8
character_sets_dir	C:\xampp\mysql\share\charsets\

[Mchl]

Do you have any non ASCII characters stored in your tables?

[random1]

Yes I have checked and I have chinese, french and korean characters in my tables.

 

All tables are utf8 and are using InnoDB engine.

[Mchl]

Try running it with --default-character-set=utf8

 

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

[random1]

Tried that, same result : a ANSII encoded file dump.

[fenway]

Show exactly the command you're using.

[random1]

I'm using:

 

c:\xampp\mysql\bin\mysqldump.exe --default-character-set=utf8 --user=ADMINUSER --password=PASSWORD --opt DATABASENAME > c:\xampp\htdocs\webman1\backups\database\sql\database_full_backup.sql

 

Where ADMINUSER, PASSWORD, DATABASENAME are actual values.

 

This does run and work but the file character encoding is not utf8...

 

Example file output for foreign characters:

 

უსთáƒáƒ•áƒ”ლი ம௠இகழà¯à®šà¯à®šà®¿à®šà¯ŠÕ¸Ö‚Õ¿Õ¥Õ¬ Ö‡ Õ«Õ¶Õ®Õ« Õ¡Õ¶Õ

[Mchl]

How are you checking the encoding of output file?

[random1]

I'm checking in phpDesigner. It says ANSI.

 

Is there a better way to check?

[random1]

I did some more testing and backups made in MySQL Administrator, SQLyog or mysqldump all have an output as an ANSI file.

 

Really strange that this is not straight forward.

[Mchl]

And what happens if you try to restore a database using this file?

[random1]

When I restore it inserts garbled text e.g:

 

უსთáƒáƒ•áƒ”ლი ம௠இகழà¯à®šà¯à®šà®¿à®šà¯ŠÕ¸Ö‚Õ¿Õ¥Õ¬ Ö‡ Õ«Õ¶Õ®Õ« Õ¡Õ¶Õ

 

instead of the actual korean, french or german characters.

[fenway]

c:\xampp\mysql\bin\mysqldump.exe --default-character-set=utf8 --user=ADMINUSER --password=PASSWORD --opt DATABASENAME > c:\xampp\htdocs\webman1\backups\database\sql\database_full_backup.sql

That's why it's not working -- redirection on the command-line will not deal with UTF8 properly; use the -r filename option instead.

[random1]

I tried the '-r' switch as:

 

c:\xampp\mysql\bin\mysqldump.exe -r c:\xampp\htdocs\webman1\backups\database_full_backup2.sql --default-character-set=utf8 --user=USERNAME --password=PASSWORD --opt DATABASE NAME

 

Is this the correct syntax for it?

 

I get a file output for this but it's still ANSI, not UTF8.

[fenway]

Hmm... I wonder if that's related to this?

[random1]

I backed up and then restored the database using sqlyog and then reinsert the UTF8 foreign characters and it now creates a UTF-8 backups with the line:

 

c:\xampp\mysql\bin\mysqldump.exe -r c:\xampp\htdocs\webman1\backups\database\sql\database_full_backup2.sql --user=USERNAME --password=PASSWORD --opt DATABASENAME

 

Finally resolved .

 

BTW I'm using mysql 5.1 and the latest version of PHP.

 

Thanks to all who helped.

[veridicus]

Sorry to bump this old thread. I came across it when I had a similar problem, but came up with a different solution that some might find useful.

 

Tell mysqldump to output latin1 with no SET NAMES (--default-character-set=latin1 and -N parameters).  Tell mysql to also use latin1 when importing (--default-character-set=latin1).  This seems to prevent any double encoding / decoding of UTF8.

 

On linux / unix, that would look like:

mysqldump -u username -p --default-character-set=latin1 -N database > backup.sql
mysql -u username -p --default-character-set=latin1 database < backup.sql

 

See http://docforge.com/wiki/Mysqldump

 

[tazosmr]

I had the same problem, and tried the above solutions, but not solved it for me. As i think, that this topic is in the top of Google, many people might have the same problem, so i am posting a solution (SO, xx ADMIN, TAKE IN, THAT THIS IS IMPORTANT, AND DONT WARN ME AGAIN!) :

set UTF8 correctly inside your php file, after mysql_connect (or after mysql_select_db) command.

mysql_query("SET NAMES 'utf8'");

Edited January 16, 2014 by tazosmr