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

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.