Jump to content

Archived

This topic is now archived and is closed to further replies.

random1

[SOLVED] mysqldump and utf8 encoding

Recommended Posts

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\

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

All tables are utf8 and are using InnoDB engine.

Share this post


Link to post
Share on other sites

Show exactly the command you're using.

Share this post


Link to post
Share on other sites

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:

 

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

Share this post


Link to post
Share on other sites

How are you checking the encoding of output file?

Share this post


Link to post
Share on other sites

I'm checking in phpDesigner. It says ANSI.

 

Is there a better way to check?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

When I restore it inserts garbled text e.g:

 

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

 

instead of the actual korean, french or german characters.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 :D.

 

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

 

Thanks to all who helped.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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'");

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.