MySQL Database Backup | Bash Scripting

MySQL Database Backup | Bash Scripting

#!/bin/bash

TIMESTAMP=$(date +"%F")
BACKUP_DIR="/backup/databases/$TIMESTAMP"
MYSQL_USER="username"
MYSQL_PASSWORD="userpass"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

mkdir -p $BACKUP_DIR

find "$BACKUP_DIR" -mtime +3 -type d -exec rm -rf {} \;

databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "show databases"| grep -Ev "(database|Database|information_schema|performance_schema|phpmyadmin|mysql)"`

for db in $databases; do

$MYSQLDUMP -u $MYSQL_USER -p$MYSQL_PASSWORD --databases $db > $BACKUP_DIR/$db.sql

done

Making the sqlbak.sh file executable.

chmod 755 sqlbak.sh

Quick explanation of MySQL database backup shell script

1. Shell script executes and stores the backup of all databases in dir: /backup/databases/2017-06-24.
2. In the beginning of shell script, all the variables are set up. These should be modified by users accordingly; just fill in correct values against the variables in which modifications are required. For Example, To modify variable MYSQL_USER and MYSQL_PASSWORD enter the correct values against them.
3. mysqldump is command where the actual  MySQL database dump (backup) happens.
4. Kindly create a file with name sqlbak.sh in /root directory and input the below-listed text into it.

Adding the script to cron

crontab -e

00 12 * * * /root/sqlbak.sh

In the end, Using the above script you can automatically take backup of all MySQL databases running on your system.