I'm trying to import a gzipped SQL file into mysql directly. Is this the right way?
mysql -uroot -ppassword mydb > myfile.sql.gz
Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.Sign up to join this community
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
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 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
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)