How to create zip backup of all the databases in MySQL Server by using mysqldump - MySQL DBA Tutorial

Scenario:

You are working as MySQL DBA , you need to backup all the databases on MySQL Server in zip backup file by using mysqldump. Each time you perform the backups of all databases as zip, the backups files should be created in newly TimeStamp directory under /mysqlbackup.

Solution:

The below mysqldump script can be used to take the zip backup of all the databases on MySQL Server, Script will generate separate zip file for each of the databases on MySQL Server. 

#Provide the backup directory path in which you would like to create new direcoty and backup databases. In my case I have directory /mysqlbackup
BACKUP_DIR=/mysqlbackup/$(date +%Y-%m-%dT%H_%M_%S);
#Check and create new directory if not exits
test -d "$BACKUP_DIR" || mkdir -p "$BACKUP_DIR"
# Get the database list
for db in $(mysql -B -s --user='backupuser' --password='MyPassword$123' -e 'Select distinct Table_schema from information_Schema.tables;')
do
echo "Performing backup of Database : "$db
  # backup each database in a separate file
  mysqldump --user='backupuser' --password='MyPassword$123' "$db" --skip-lock-tables | gzip > "$BACKUP_DIR/$db.sql.gz"
done

Items to change in above script according to your environment

• Replace "backupuser" to your user name that you are using to run the mysqldump
• Replace "MyPassword$123" password with password for your user.

Once you will run above script after making required changes for user name and password, you will see a TimeStamp directory with your backup zip files.

MySQL- take backup of all databases as gzip file by using mysqldump