Typically when creating MySQL dumps, I prefer to use Holland. However sometimes you just need to be able to dump and import a database for various reasons.
Below are two quick commands for doing this with compressed files. If you have a 30G database dump, you probably don’t want to waste time uncompressing the whole thing first and using up valuable disk space. And you certainly don’t want a dump a 30G file on your file system.
Export to a compressed MySQL dump
This command will allow you to compress a database dump as you dump the database. Just be sure to remove the {} when you run the command, and replace database with the name of your database:
mysqldump -u {user} -p {database} | gzip > {database}.sql.gz
Or if you prefer to add a timestamp:
mysqldump -u {user} -p {database} | gzip > `date -I`.{database}.sql.gz
Import a compressed MySQL dump
This single command will allow you to import the compressed dump directly into MySQL without having to uncompress it first. This assumes you have already created the database within MySQL.
The command to import it is as follows:
gzip -dc < {database}.sql.gz | mysql -u {user} -p {database}