Bash script to backup MySQL databases one by one

Bash script to backup MySQL databases on regular bases

Backing up all your MySQL databases one by one is a pain which need human attention also.

Here is a small bash script to backup databases to a directory with name of db corresponding date when db taken.

The script will skip any database whose name starts with an underscore, so I can have test or junk databases that I don’t want to back up.

• You need to change lines 3, 4  to reflect your MySQL user, password.
• Note : All off your databases backup stored in /home/mysql_backup/$DATE directory.

#!/bin/bash
#Author : linuxtweaks.in
############################################Variables to Change###################################################
USER="Mysql_username"                   #change here for Mysql username
PASSWORD="Mysql_password"               #change here for Mysql password
DATE=$(date +%F)                        
mkdir -p /home/mysql_backup/$DATE       #Auto create directory with date namne
BKPDIR="/home/mysql_backup/$DATE"       
##################################################################################################################
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] && [[ "$db" != "performance_schema" ]]; then
        echo "Dumping database: $db" >> dump.log
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $BKPDIR/`date +%Y%m%d`.$db.sql
        gzip $BKPDIR/`date +%Y%m%d`.$db.sql
    fi
done
echo "Backup Seucessfully created on $date" >> dump.log

Now you just need to make it executable by running the following command.

chmod +x mysql_backup.sh

And then add it to the crontab so it’ll run automagically.

crontab -e
00 21 * * * /path/to/mysql_backup.sh