I'm trying to import a gzipped SQL file into mysql directly. Is this the right way?

mysql -uroot -ppassword mydb > myfile.sql.gz
  • 9
    Even ignoring the gzip side of the question, your arrow is pointing the wrong way... – Matt Fletcher Sep 27 '16 at 9:47
up vote 182 down vote accepted
zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database

> will write the output of the mysql command on stdout into the file myfile.sql.gz which is most probably not what you want. Additionally, this command will prompt you for the password of the MySQL user "root".

  • 14
    As a good security practice, I would put my password on the command line, I would let mysql ask for it. – Prof. Moriarty May 3 '10 at 8:19
  • 9
    Or even better: create ~/.my.cnf with the credentials. ;) – joschi May 3 '10 at 10:46
  • 5
    As @Prof. Moriarty explans, you can modify the command to not use the password via zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database. It will know the last parameter is the database you wish to use, not your password. – bafromca Jan 20 '15 at 0:20
  • 3
    To slightly correct @Prof.Moriarty's comment, a good security practice would be to not put my password on the command line (where it will get stored in history, or seen over your shoulder), and let MySQL ask for it. The -p flag alone will cause MySQL to ask at a prompt for the password. – George Aug 3 '16 at 8:00
  • 3
    +1 for something that leaves the damn database dump compressed – Dmitri DB Jan 12 '17 at 19:18

To display a progress bar while importing a sql.gz file, download pv and use the following:

pv mydump.sql.gz | gunzip | mysql -u root -p

In CentOS/RHEL, you can install pv with yum install pv.

In Debian/Ubuntu apt-get install pv.

In MAC, brew install pv

  • 2
    pv seems to be in the Ubuntu repos too (at least in 12.04 LTS it is), but again you need to do sudo apt-get install pv to get it. Thanks Banjer, this is perfect for big database imports! – toon81 Feb 19 '15 at 15:25
  • I had to run pv mydump.sql.gz | gunzip | mysql -u root my_database_name. That was because I was importing tables and I don't have a password set for my root user – Cristiano Mendonça Feb 9 '16 at 22:19
  • In MAC, brew install pv – score Apr 28 '16 at 19:30

The simplest way is to unzip the database file before importing. Also as mentioned by @Prof. Moriarty you shouldn't be specifying the password in the command (you'll be asked for the password). This command taken from webcheatsheet will unzip and import the database in one go:

gunzip < myfile.sql.gz | mysql -u root -p mydb
  • 1
    Also, mydb needs to be created before importing. This doesn't create the db for you. – Siddhartha Apr 20 '15 at 23:00
  • i found my piping gunzip on a 10GB compressed file caused my import to freeze. not sure if that's due to memory constraints or something but i'd err on the side of doing one step at a time in the future. – Ryan Tuck Jan 7 '16 at 16:42
  • @RyanTuck That is pushing the limits of these kind of processes :) – icc97 Jan 7 '16 at 19:37
  • @Siddhartha That depends on the sql dump file. Sometimes they include create database statements. – rooby Aug 30 '17 at 22:55
  • @rooby that makes sense. – Siddhartha Sep 1 '17 at 0:41

If you get an error from zcat, in which the error message contains the file name with an extra suffix .Z, then try using gzcat instead, as described at https://stackoverflow.com/questions/296717/zcat-wont-unzip-files-properly

Also check if there is any USE-statement in the SQL file. Specifying the database at the command line doesn't guarantee that the data ends up there if a different destination is specified within the SQL file.

  • 1
    You just need to extend the command like so: pv mydump.sql.gz | gunzip | mysql -u root -p your_database. The accepted answer uses this approach. – bafromca Jan 20 '15 at 0:17

On macOS, I used this:

zcat < [Database].sql.gz | mysql -u root -p [Database Name in MySQL]

Enter your password, and voila!

For bzip2 compressed files (.sql.bz2), use:

bzcat <file> | mysql -u <user> -p <database>

OR

pv <file> | bunzip2 | mysql -u <user> -p <database>

to see progress bar.

You can use -c, --stdout, --to-stdout option of gunzip command

for example:

gunzip -c file.sql.gz | mysql -u root -p database

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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