MySQL: Import and Export (Compressed) Databases

Submitted by Peter Majmesku on Mon, 08/29/2016 - 19:24

The export (dump)

This command will generate you a compressed dump file with the date information in the filename.

mysqldump -u username -p"password" database_name | gzip > $(date +"%Y-%m-%d")-database_name.sql.gz

Import the database dump

Firstly make sure that the "empty" database exists. Then import it:

mysql -uUSERNAME -p"PASSWORD" database_name < dump-filename.sql

Import a Gzip compressed database dump file on Linux (use "zcat < database-dump-file.sql.gz" on Mac):

zcat database-dump-file.sql.gz | mysql -uroot -ppassword database-name

Your MySQL user has no LOCK TABLES permission

If you receive the error

mysqldump: Got error: 1044: Access denied for user 'my-user'@'localhost' to database 'my-database' when using LOCK TABLES

then your MySQL has not the permission to lock the database tables. This would guarantee, that you get an non-corrupted database dump. Because there were no write-queries while you're dumping the database. You can skip this error and risk a corrupted database dump (which might still work for development purposes) with the --skip-lock-tables option. Here is the full command as an example:

mysqldump -uUSERNAME -pPASSWORD --skip-lock-tables DATABASENAME | gzip > DATABASENAME.sql.gz

A hint on Mac

On Mac you need to work with arrows to steer the stream. E.g. in "zcat":

$ zcat foo.txt.gz 
zcat: can't stat: foo.txt.gz (foo.txt.gz.Z): No such file or directory

Try this:

$ zcat < foo.txt.gz