# How can I import/export a MySQL database without redirection?

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 '11 at 15:30
@DrColossos, perhaps not correctly setting the code page in a Windows command-prompt could cause scrambling. –  Synetech May 3 '11 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. –  xenoterracide May 3 '11 at 20:06

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 '11 at 19:59
If you're looking for a GUI/Windows interface to work with, try TOAD for mysql. quest.com/toad-for-mysql –  randy melder May 4 '11 at 21:46
Command line tool are capable of directly reading/writeing files too. –  Synetech May 5 '11 at 1:58

You may want to do this command on the import server

SHOW CREATE DATABASE dbname;


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.

UPDATE !!!

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

-
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 '11 at 11:12
Updated my answer. –  RolandoMySQLDBA Apr 14 '11 at 17:08
+1 I think the default-charset option is the important part and good explanation! –  DrColossos Apr 14 '11 at 17:28
@DrColossos +1 in Thanks for Looking over my Shoulder and Making Sure I answered the question contextually !!! –  RolandoMySQLDBA Apr 14 '11 at 17:31
;) Nevermind, I also learned something new! –  DrColossos Apr 14 '11 at 17:55

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.

-