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 


Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
CAPTCHA This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.