Shell script to backup MySql database

#!/bin/bash

# Shell script to backup MySql database

 

USERNAME="mysql user name"    

PASSWORD="PASSWORD"

HOSTNAME="localhost" 

 

MYSQL="$(which mysql)"

MYSQLDUMP="$(which mysqldump)"

CHOWN="$(which chown)"

CHMOD="$(which chmod)"

GZIP="$(which gzip)"

 

DESTINATION="/directory1"    # Directoy to take backup

 

# Main directory where backup will be stored

BACKUPSTORE="$DEST/mysql"

HOST="$(hostname)"                                  # Get hostname

NOW="$(date +"%d-%m-%Y")"                  # Get data in dd-mm-yyyy format

FILE=""                                                           # File to store current backup file

DATABASELIST=""                                     # Store list of databases

IGNOREDATABASE="temp"                   # DO NOT BACKUP THESE DATABASES

 

[ ! -d $BACKUPSTORE ] && mkdir -p $ BACKUPSTORE || :

 

$CHOWN 0.0 -R $DESTINATION

$CHMOD 0600 $DESTINATION

 

# Get all database list

DATABSELIST="$($MYSQL -u $USERNAME -h $HOSTNAME -p$PASSWORD -Bse 'show databases')"

 

for db in $DATABASELIST

do

    skipdb=-1

    if [ "$IGNOREDATABASE" != "" ];

    then

            for i in $IGNOREDATABASE

            do

                [ "$db" == "$i" ] && skipdb=1 || :

            done

    fi

 

    if [ "$skipdb" == "-1" ] ; then

            FILE="$BACKUPSTORE/$db.$HOST.$NOW.gz"

            $MYSQLDUMP -u $USERNAME -h $HOSTNAME -p$PASSWORD $db | $GZIP -9 > $FILE

    fi

done