Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

mysql -uroot -ppassword mydb > myfile.sql.gz
share|improve this question

4 Answers 4

up vote 35 down vote accepted

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.

share|improve this answer
7  
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
3  
Or even better: create ~/.my.cnf with the credentials. ;) –  joschi May 3 '10 at 10:46
    
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 at 0:20

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

share|improve this answer
    
Also, mydb needs to be created before importing. This doesn't create the db for you. –  Siddhartha Apr 20 at 23:00

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.

share|improve this answer
1  
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 at 15:25

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.

share|improve this answer
    
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 at 0:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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