I'm trying to import a gzipped SQL file into mysql directly. Is this the right way?
mysql -uroot -ppassword mydb > myfile.sql.gz
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
In CentOS/RHEL, you can install pv with
yum install pv.
apt-get install pv.
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
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.
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>
pv <file> | bunzip2 | mysql -u <user> -p <database>
to see progress bar.
You can use
-c, --stdout, --to-stdout option of
gunzip -c file.sql.gz | mysql -u root -p database