The basic installation of MySQL Server provides a great command line utility to take backup of MySQL databases
The ‘mysqldump‘ command is used for creation “dumps” of databases managed by MySQL.
These dumps are just files with all the SQL commands needed to recreate the database.
Backup a Database
Use the following command to backup a database:
$ mysqldump --opt -u [username] -p[password] [dbname] > [backupfile.sql]
Note: There is no space between -p and the password.
Example:
$ mysqldump --opt -u root -pSeCrEt customers_db > customers_db_backup.sql
Backup multiple Databases
Use the following command to backup several databases:
$ mysqldump --opt -u [username] -p[password] --databases [dbname_1] [dbname_2] ... > [backupfile.sql]
Example:
$ mysqldump --opt -u root -pSeCrEt --databases customers_db employees_db > backup.sql
Backup all Databases
Use the following command to backup all databases:
$ mysqldump --opt -u [username] -p[password] --all-databases > [backupfile.sql]
Example:
$ mysqldump --opt -u root -pSeCrEt --all-databases > all_db_backup.sql
Backup a Database with a Compression
Use the following command to backup a database with compression:
$ mysqldump --opt -u [username] -p[password] [dbname] | gzip -9 > [backupfile.sql]
Example:
$ mysqldump --opt -u root -pSeCrEt customers_db | gzip -9 > customers_db_backup.sql.gz
If you want to extract the .gz file, use the command below:
$ gunzip customers_db_backup.sql.gz