I've been told that redirection via shell (e.g. < and > ) can (not always) affect the collation of a database (.e.g utf8). How can I import an export without redirecting so that I don't have to worry about this.

  • Never heard of that before. Do you have some examples/proof for this? I don't think that redirection does something like that since *nix is completely aware of UTF8 and handles it as it should.
    – DrColossos
    Apr 13, 2011 at 15:30
  • @DrColossos, perhaps not correctly setting the code page in a Windows command-prompt could cause scrambling.
    – Synetech
    May 3, 2011 at 19:57
  • I keep meaning to go back and ask someone who understands the reason of what causes this... because I don't deal with it often enough. May 3, 2011 at 20:06

3 Answers 3


There's always the mysqlimport utility, however, your source would have to be delimited values... such as those obtained from a "SELECT * FROM ... INTO OUTFILE ..."

If you're moving large amounts of data, it might be faster to use this bulk loading method anyway.

Beware of the consequences it may have with replication however.

  • What about an export program? I agree that there should also be a way for an executable to directly read and write a source file, but then again, these come from *nix, where redirection and piping are much more frequently used than in Windows.
    – Synetech
    May 3, 2011 at 19:59
  • If you're looking for a GUI/Windows interface to work with, try TOAD for mysql. quest.com/toad-for-mysql
    – randomx
    May 4, 2011 at 21:46
  • Command line tool are capable of directly reading/writeing files too.
    – Synetech
    May 5, 2011 at 1:58

You may want to do this command on the import server


This will show you the default collation of the database you want to import into.

You can also change the import database's collation if needed.


First of all, I would look at the what the OS says the character set is. On the Linux command line just run this:

echo ${LANG}

From the MySQL point of view

You could explicitly name the default character set in the mysqldump

--default-character-set (default is utf8)

You can see it in the mysql client:

mysql> show variables like '%charac%';
| Variable_name            | Value                      |
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
8 rows in set (0.00 sec)

Give it a Try !!!

As for collation, you may need to set one or more environment variables like these:

mysql> show variables like '%coll%';<BR>
| Variable_name        | Value             |
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
3 rows in set (0.00 sec)

Set these explicitly upon export for mysqldump and import for mysql as follows:

mysqldump -h... -u... -p... --default-character-set=utf8 --triggers --routines source_db | mysql -h... -u... -p... --collation_connection=latin1_swedish_ci -A -Dtarget_db
  • 1
    I don't think this answers the question completely. I think the question is about the wrong conversion while directing the output of the backup to a .sql file and not the change of the collation. So the problem seems to be in the export itself, not in the collation of the table.
    – DrColossos
    Apr 14, 2011 at 11:12
  • Updated my answer. Apr 14, 2011 at 17:08
  • 1
    +1 I think the default-charset option is the important part and good explanation!
    – DrColossos
    Apr 14, 2011 at 17:28
  • @DrColossos +1 in Thanks for Looking over my Shoulder and Making Sure I answered the question contextually !!! Apr 14, 2011 at 17:31
  • ;) Nevermind, I also learned something new!
    – DrColossos
    Apr 14, 2011 at 17:55

Well, you could log into MySQL and do:

mysql> source myfile.sql

That takes the shell out of the picture altogether - but I would still recommend double-checking environment variables like NLS_LANG.

  • 2
    or mysql -e "source myfile.sql"
    – JackLeo
    Feb 13, 2015 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.