The following commands may help you save your time when it comes to backup, restore or repair your MySQL database.

  1. Export a database:
mysqldump -u <user> -p -h <host> <database> > /path/to/exported_file.sql

You can also export and compress a database as well:

mysqldump -u <user> -p -h <host> <database> | gzip > /path/to/exported_file.sql.gz
  1. Import a database from file:
mysql -u <user> -p -h <host> <database> < /path/to/file.sql
  1. Import a gzipped SQL file to a database:
gzip -dc data.sql.gz | mysql -u <user> -p -h <host> <database>
  1. Backup a database, compress it and send to remote server:
mysqldump -u <user> -p -h <host> --opt <database> | gzip -c | ssh [email protected] "cat > /remote/dir/DB_backup.gz"
  1. Check, optimize and repair a database if it is corrupted:
mysqlcheck -u <user> -p -h <host> --auto-repair -c -o <database>
  1. Monitor MySQL restore progress with pv: Instead of using:
mysql -u <user> -p -h <host> <database> < database_backup.sql

Use:

pv database_backup.sql | mysql -u <user> -p -h <host> <database>

To monitor a backup progress:

mysqldump -u <user> -p --lock-tables=false <database> | pv | gzip > /path/to/file.sql.gz

*Note: if you don’t have pv command on your system yet, you can install it by using ‘yum install pv’ (for RHEL/Centos) or ‘apt-get install pv’ (for Debian/Ubuntu)

  1. Duplicate a database

Of course you can export the database and import it to another database, but this is a cleaner one-liner that works. No intermediate file needed. First you need to create a new database (db2). Then copy data from an existing database (db1) to db2 using:

mysqldump -h <host> -u <user> -p<password> db1 | mysql -h <host> -u <user> -p<password> db2