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

Question

is this right?

mysql -uroot -ppassword mydb > myfile.sql.gz

Answer 1

zcat /path/to/file.sql.gz | mysql -u 'root' -p 'password' 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.

Answer 2

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

Answer 3

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.

Answer 4

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.