229

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

mysql -uroot -ppassword mydb > myfile.sql.gz
1
  • 24
    Even ignoring the gzip side of the question, your arrow is pointing the wrong way... Sep 27, 2016 at 9:47

13 Answers 13

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

18
  • 22
    As a good security practice, I would put my password on the command line, I would let mysql ask for it. May 3, 2010 at 8:19
  • 14
    Or even better: create ~/.my.cnf with the credentials. ;)
    – joschi
    May 3, 2010 at 10:46
  • 9
    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, 2015 at 0:20
  • 9
    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, 2016 at 8:00
  • 9
    +1 for something that leaves the damn database dump compressed
    – Dmitri DB
    Jan 12, 2017 at 19:18
126

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

In Amazon Linux2, sudo amazon-linux-extras install epel and then sudo yum install pv

4
  • 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, 2015 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 Feb 9, 2016 at 22:19
  • 1
    In MAC, brew install pv
    – score
    Apr 28, 2016 at 19:30
  • 1
    Great answer! I had to import huge database (3GB compressed, 50GB plaintext) and having progress bar really helped
    – borisano
    Aug 17, 2022 at 14:34
74

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
5
  • 1
    Also, mydb needs to be created before importing. This doesn't create the db for you.
    – Siddhartha
    Apr 20, 2015 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.
    – ryantuck
    Jan 7, 2016 at 16:42
  • @RyanTuck That is pushing the limits of these kind of processes :)
    – icc97
    Jan 7, 2016 at 19:37
  • 3
    @Siddhartha That depends on the sql dump file. Sometimes they include create database statements.
    – rooby
    Aug 30, 2017 at 22:55
  • @rooby that makes sense.
    – Siddhartha
    Sep 1, 2017 at 0:41
20

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

0
7

On macOS, I used this:

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

Enter your password, and voila!

1
  • Obviously you don't need the < sign because zcat is using directed input already. But this works beautifully. Oct 20, 2023 at 12:53
5

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

for example:

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

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
  • 2
    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, 2015 at 0:17
4

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
3

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
1

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.

1

To export in .sql.qz command is :-

mysqldump -u username -p  database | gzip   >  database.sql.gz   

To import the .sql.qz file command is:-

gunzip <  database.sql.gz  | mysql -u usrname -p newdatabase
0

Lets say you need to populate user_data with mysql, try this:

export DEBIAN_FRONTEND=noninteractive \
    apt-get update -yq
    dpkg -l | grep mysql-server || (echo "Installing MySQL..." \
    && apt-get install -yq mysql-server \
    && echo -e "\n[mysqld]\nbind-address=0.0.0.0\nskip-name-resolve=1" | tee -a /etc/mysql/my.cnf \
    && aws s3 cp --quiet s3://your-bucket/mysqldump_all_databases.sql.gz - | zcat | mysql -uroot\
    && systemctl restart mysql)
0

If you are a windows user, I recommend you follow these steps:

  1. The first step is to install gzip, I recommend you do it using Chocolatey. You can install it via the following link: https://chocolatey.org/install

  2. After cholocatey installed, now just install gzip:

    choco install gzip -y

  3. Once installed, you can now unzip and import your sql.gz files directly into the MySQL prompt with the following command in command prompt (cmd.exe):

    gzip -cd backup.sql.gz | mysql -uUSER -pPASSWORD -hLOCALHOST DATABASE

Notes and useful:

  • If you want to dump and compress it directly using gzip, just do it using the command below:

    mysqldump -uUSER -pPASSWORD -hHOSTNAME DATABASE_NAME | gzip -a9 > PATH_TO_SAVE_FILE_SQL_GZ

  • The -h parameter in mysqldump does not require for using in localhost. Use this for remote MySQL server

  • Use in gzip the -9 parameter to the best compression level or -1 parameter to fast compression.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .