Backup (mysql dump) all your MySQL databases in separate files

Backup (mysql dump) all your MySQL databases in separate files

Sometimes we would like to dump all the MySQL databases. MySQL provides an easy solution to this problem:
1

mysqldump -u root -p –all-databases > all_dbs.sql

However this will dump stuff into one file. How to dump all databses into separate files? Well, here is my solution. A small bash script:

	
#! /bin/bash
 
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/backup/$TIMESTAMP"
MYSQL_USER="backup"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="password"
MYSQLDUMP=/usr/bin/mysqldump
 
mkdir -p "$BACKUP_DIR/mysql"
 
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
 
for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"
done

Be aware, that in order to execute this script from cron, you need to store password in it (so cron won’t be prompted to provide a password). That’s why, you should not use a root account. Instead just create a new user only for backups, with following privileges: