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

Above cron ’ll run every day at 9:00 PM.

You can directly download this script from Here

Balvinder Singh

Hello, I am Balvinder Singh - DevOps Engineer with 2.5+ year of working experience with different server environments. Tag Line:-Linux | AWS| WHM |Monitoring | Virtualization | Optimization | Performance | Security | Release & Deployment. I love helping companies / clients to deploy their code / applicateions to well managed, optimized, secure server and can go extra mile to satisfy.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *