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".
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.
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
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!
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
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.
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!
You can use -c, --stdout, --to-stdout
option of gunzip
command
for example:
gunzip -c file.sql.gz | mysql -u root -p database
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.
pv mydump.sql.gz | gunzip | mysql -u root -p
your_database
. The accepted answer uses this approach.
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
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
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.
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
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)
If you are a windows user, I recommend you follow these steps:
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
After cholocatey installed, now just install gzip:
choco install gzip -y
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.