random1 Posted January 24, 2009 Share Posted January 24, 2009 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\ Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/ Share on other sites More sharing options...
Mchl Posted January 24, 2009 Share Posted January 24, 2009 Do you have any non ASCII characters stored in your tables? Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-745171 Share on other sites More sharing options...
random1 Posted January 24, 2009 Author Share Posted January 24, 2009 Yes I have checked and I have chinese, french and korean characters in my tables. All tables are utf8 and are using InnoDB engine. Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-745173 Share on other sites More sharing options...
Mchl Posted January 24, 2009 Share Posted January 24, 2009 Try running it with --default-character-set=utf8 http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-745174 Share on other sites More sharing options...
random1 Posted January 24, 2009 Author Share Posted January 24, 2009 Tried that, same result : a ANSII encoded file dump. Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-745175 Share on other sites More sharing options...
fenway Posted January 27, 2009 Share Posted January 27, 2009 Show exactly the command you're using. Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-747526 Share on other sites More sharing options...
random1 Posted January 28, 2009 Author Share Posted January 28, 2009 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: უსთáƒáƒ•áƒ”ლი ம௠இகழà¯à®šà¯à®šà®¿à®šà¯ŠÕ¸Ö‚Õ¿Õ¥Õ¬ Ö‡ Õ«Õ¶Õ®Õ« Õ¡Õ¶Õ Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-748213 Share on other sites More sharing options...
Mchl Posted January 28, 2009 Share Posted January 28, 2009 How are you checking the encoding of output file? Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-748388 Share on other sites More sharing options...
random1 Posted January 29, 2009 Author Share Posted January 29, 2009 I'm checking in phpDesigner. It says ANSI. Is there a better way to check? Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-749146 Share on other sites More sharing options...
random1 Posted January 29, 2009 Author Share Posted January 29, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-749156 Share on other sites More sharing options...
Mchl Posted January 29, 2009 Share Posted January 29, 2009 And what happens if you try to restore a database using this file? Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-749368 Share on other sites More sharing options...
random1 Posted January 30, 2009 Author Share Posted January 30, 2009 When I restore it inserts garbled text e.g: უსთáƒáƒ•áƒ”ლი ம௠இகழà¯à®šà¯à®šà®¿à®šà¯ŠÕ¸Ö‚Õ¿Õ¥Õ¬ Ö‡ Õ«Õ¶Õ®Õ« Õ¡Õ¶Õ instead of the actual korean, french or german characters. Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-750346 Share on other sites More sharing options...
fenway Posted February 2, 2009 Share Posted February 2, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-752440 Share on other sites More sharing options...
random1 Posted February 2, 2009 Author Share Posted February 2, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-753006 Share on other sites More sharing options...
fenway Posted February 3, 2009 Share Posted February 3, 2009 Hmm... I wonder if that's related to this? Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-753330 Share on other sites More sharing options...
random1 Posted February 3, 2009 Author Share Posted February 3, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-753333 Share on other sites More sharing options...
veridicus Posted September 13, 2009 Share Posted September 13, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-917822 Share on other sites More sharing options...
tazosmr Posted January 16, 2014 Share Posted January 16, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/142236-solved-mysqldump-and-utf8-encoding/#findComment-1465380 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.