I always need to Google this stuff every time I want to do backup and/or restoring the database, so might as well put it here for reference.

First of all, you need to have pv installed in your system, so check the installation guide for your OS accordingly. In short, pv can monitor the data in your pipeline, so it is perfect for this kind of situation, especially if your database is fairly large and you are unsure whether the operation is running as it should or not.

To back up a database, run

1
mysqldump -u <username> -p<password> <database> --single-transaction --skip-lock-tables --no-tablespaces | gzip | pv > /path/to/file.sql.gz

To restore it, run

1
pv /path/to/file.sql | mysql -u <username> -p<password> <database>

Disclaimer: Using a password on the command line interface can be insecure, so please use it at your discretion.

Tagged under cli, mysql, unix.