HowTo: BackUp MySQL Database From the Command Line

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