Export and Import MySQL Databases in Terminal

Export database

Export your database to create a backup using the following command:

$ mysqldump --opt --hex-blob [-h host] -u [user] -p [database] > [database].sql

Compress with gzip

You can also compress your database on-the-fly with gzip:

mysqldump --opt --hex-blob [-h host] -u [user] -p [database] | gzip -c > [database].sql.gz

Show progress

To show progress, you can use pv:

mysqldump --opt --hex-blob [-h host] -u [user] -p [database] | pv | gzip -c > [database].sql.gz

Import database

Import your database to restore using the following command:

mysql -u [user] -p [database] < [database].sql

Uncompress with gzip

Uncompress a gzipped backup file before importing:

gunzip [database].sql.gz | mysql -u [user] -p [database]

Show progress

Show progress while restoring your database using pv:

pv [database].sql.gz | gunzip | mysql -u [user] -p [database]

Force import

Sometimes you may encounter import errors which are safe to ignore, although you should still review and troubleshoot the errors. To force the restore use the -f flag.

pv [database].sql.gz | gunzip | mysql -u [user] -p -f [database]