How to use mysqlimport to read in result of mysqldump --databases

mysql mysqldump mysqlimport
Charles Anderson picture Charles Anderson · Feb 19, 2010 · Viewed 30.3k times · Source

I have successfully dumped an entire MySQL database using

mysqldump --databases

generating a nice .txt file. However, I can't see how to read the whole file back into MySQL in one go; mysqlimport seems to want just one table at a time.

Answer

Pascal MARTIN picture Pascal MARTIN · Feb 19, 2010

When you've generated some file (say db-dump.sql) with mysqldump, you can import it to your other database with the mysql command :

mysql --user=XXX --password=XXX --host=YOUR_HOST DATABASE_NAME < db-dump.sql


And, if you don't want the password to appear in a command, you can use :

mysql --user=XXX -p --host=YOUR_HOST DATABASE_NAME < db-dump.sql


As a sidenote, if you want to copy one DB to another one, you don't need to use a file, and can just directly pipe the output of mysqldump to mysql :

mysqldump --user=XXX --password=XXX --host=SOURCE_HOST SOURCE_DB | mysql --user=XXX --password=XXX --host=DESTINATION_HOST DESTINATION_DB

(It should even be faster, as you're not using a temporary file that resides on disk)

Related questions

MySQL import database but ignore specific table

I have a large SQL file with one database and about 150 tables. I would like to use mysqlimport to import that database, however, I would like the import process to ignore or skip over a couple of tables. What is …

Read More
Mysql ERROR: ASCII '\0' while importing sql file on linux server

I am getting following error while importing sql file ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0…

Read More
MySQL data export changes times

I have some backup and restore scripts that I am using for my database. The table has a timestamp field. The backup script looks like this: mysqldump -u user -ppass database --tab="../" --fields-terminated-by="|" --skip-comments table It creates two files, table.…

Read More