30

I have a .gz sql dump file (example: foo.sql.gz) that i want import in my database with the classic mysql command.

gunzip -c foo.sql.gz > foo.sql

mysql -uroot -ppassword foo < foo.sql

foo is the database.

How can i pipe these two commands in a single one?

Tried 

gunzip -c foo.sql.gz | mysql -uroot -ppassword foo

but doesn't seem to work; i get gzip: stdout: Broken pipe

apelliciari
  • 423
  • 2
  • 5
  • 7
  • 7
    FYI you really do not want to pass the mysql password on the command line. This leads to it being stored in ~/.bash_history for all to see. If you just put -p it will prompt you to enter your password which is much safer (and doesn't break the zcat pipe either) – thefreeman Jan 21 '13 at 23:36
  • 1
    You orignal command would work if gunzip < foo.sql.gz | mysql -uroot -ppassword foo – Yada Feb 01 '13 at 20:49

3 Answers3

42
zcat foo.sql.gz | mysql -uroot -ppassword foo

This will also leave foo.sql.gz as it is.

Nifle
  • 34,203
  • 26
  • 108
  • 137
14

For those on Max OSX there is a bug with zcat so you'll need to use gzcat instead.

gzcat foo.sql.gz | mysql -uroot -ppassword foo
SammyK
  • 241
  • 1
  • 4
9

All other answers are recommending writing the password in the command. This is a very bad practice and poses security risks. Please DON'T DO that.

You can leave the password empty in the command, than it will ask you to enter the password interactively. This way the password is not saved in the bash history.

gunzip < dump.sql.gz | mysql -u username -p databasename
Елин Й.
  • 191
  • 1
  • 4