How do I load a sql.gz file to my database? (importing)

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".

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 <database name>

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

In Debian/Ubuntu, apt-get install pv.

In macOS, brew install pv

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

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

On macOS, I used this:

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

Enter your password, and voila!

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.

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

for example:

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

On MacOS I've been using the following one-liner with no need of installing additional programs, except for the MySQL client itself.

$ cat /path/to/file.sql.gz | gzip -d | mysql -u root <db_name>

The first command, cat, prints the file. Its output, the file contents, is sent as the input to the next command, gzip. gzip with the the -d option decompresses the input passed to it and outputs the result, which is finally used as input for the MySQL client, the mysql program. The output -> input sending is brought to us by the | (pipe) operator on bash and other shell.

This script can also be used in some popular Linux distros, such as Ubuntu. I'm not sure whether gzip is always available. But it can be easily installed, if not, with:

$ sudo apt install gzip

If you are using small size database it's better to extract and import. Here is the extract command

tar -xf dbname.sql.tar.gz 

Here is importing command.

mysql -u username -p new_database < data-dump.sql

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.