Roundtrip dump MySQL and UTF-8

The tables can be in CHARSET=latin1 or utf8 whatever.

– mysqldump export an UTF-8 encoded file. UTF8 is the default. (You could change it with option –default-character-set=.)

First of, don’t loose the encoding by editing the file with a non-UTF-8 editor.

The main problem comes when you inject the dump into MySQL.
– The documentation states that : “When a client connects to a MySQL server, the server indicates to the client what the server’s default character set is. The client switches to this character set for this connection.” (http://dev.mysql.com/doc/refman/4.1/en/character-sets.html)

But in fact :
mysql -h 1.2.3.4 -uroot
Your MySQL connection ... to server version: 4.1.18
mysql> show variables like "%character_set%";
| character_set_client | latin1
| character_set_connection | latin1
| character_set_database | utf8
| character_set_results | latin1
| character_set_server | utf8
| character_set_system | utf8

You have to state UTF8 :
mysql -h 1.2.3.4 -uroot --default-character-set=utf8
Your MySQL connection ... to server version: 4.1.18
mysql> show variables like "%character_set%";
| character_set_client | utf8
| character_set_connection | utf8
| character_set_database | utf8
| character_set_results | utf8
| character_set_server | utf8
| character_set_system | utf8

There is a danger with the pipe in mysql ... .
Check that your locale are UTF8. Eventually uses mysql -e "source dump.sql".

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s