MySQL DBA Training - How to backup all MySQL databases to single backup file by using mysqldump

Scenario:

You are working as MySQL DBA and you need to take backup of all MySQL Databases to single backup file, how will you take backup of all databases without zip and with zip by using mysqldump.

Solution: 

You can use below script to take backup of all databases to single backup file by using mysqldump. In below example we are not zipping the backup file.

mysqldump --user=ProvideUser --password=ProvidePassword --all-databases >/destinationdirectory/AllDatabases.sql

Example: 

In below example we are using "backupuser" to take backup of all databases in single file.

mysqldump --user=backupuser --password='MyPassword$123' --all-databases >/mysqlbackup/AllDatabases.sql

To take the backup in gznip, you can use command like below

mysqldump --user=backupuser --password='MyPassword$123' --all-databases | gzip >/mysqlbackup/AllDatabases.sql.gz

Notice that if you will zip the backup file the size will be smaller and it can help if you need to move across network to restore on another MySQL Server.

Backup all databases to single backup file by using mysqldump - MySQL Training