MySQL Backup Korn Shell script using mysqldump (Backup 1 or ALL databases)

Overview

Note:  MysqlDump cannot backup information_schema by itself
 

How to backup your databases with a simple ksh script

#!/bin/ksh
#########################################################################
# Created by Philippe Courtois                                          #
# date: 2012-08-22                                                      #
#########################################################################
###set -x

if [ "$1" == "-?" ];then
        echo "USAGE: backup_mysql.ksh instance_name database_name|ALL "
        exit
fi

if [ $# -ge 2 ]; then
        export INSTANCE=$1
        echo "INSTANCE=$INSTANCE"
        export DATABASE=$2
        echo "DATABASE=$DATABASE"
else
        echo "USAGE: backup_mysql.ksh instance_name database_name|ALL"
        exit
fi

echo `date` " BACKUP BEGIN"
export TIMESTAMP=`date +%Y%m%d%H%M%S`
export BACKUP_BASE=/dbabackup # Backup folder
export BACKUP_HOME=$BACKUP_BASE/$1
export USER=mysql_user  #mysql root account or account with backup  rights
export HOST=mysql_host  # mysql hostname
export PASSWORD=user_password # mysql account password
export MYSQL=`which mysql`
export MYSQLDUMP=`which mysqldump`
export GREP=`which grep`
#export GZIP=`which gzip`
export PART=_
echo "TIMESTAMP=$TIMESTAMP"
echo "BACKUP_BASE=$BACKUP_BASE"
echo "BACKUP_HOME=$BACKUP_HOME"
echo "USER=$USER"
echo "HOST=$HOST"
echo "PASSWORD=$PASSWORD"
echo "MYSQL=$MYSQL"
echo "MYSQLDUMP=$MYSQLDUMP"
echo "GREP=$GREP"
#echo "GZIP=$GZIP"

if [ -d /dbabackup/$INSTANCE ]; then
        export DBABACKUP=/dbabackup/$INSTANCE
        echo "DBABACKUP=$DBABACKUP"
else
        mkdir -p /dbabackup/$INSTANCE
        export DBABACKUP=/dbabackup/$INSTANCE
        echo "DBABACKUP=$DBABACKUP"
fi

if [ "$DATABASE" == "ALL" ]; then
        for i in `echo 'SHOW DATABASES;' | $MYSQL -u$USER --password=$PASSWORD -h$HOST|$GREP -v '^Database$'|$GREP -v grep|$GREP -v information_schema`

        do
                FILENAME=$BACKUP_HOME/$i$PART$TIMESTAMP.sql
                echo `date` " BACKUP STARTING $i on $FILENAME"
                $MYSQLDUMP -u$USER --password=$PASSWORD --database $i > $FILENAME
                j=`ps -ef | grep  -i mysqldump | grep -v grep| wc -l`
                while [ "$j" -ne "0" ]
                do
                        sleep 5
                        j=`ps -ef | grep -i mysqldump | grep -v grep| wc -l`
                done
                echo `date` " BACKUP FINISHED $i"
        done
else
        export FILENAME=$BACKUP_HOME/$DATABASE$PART$TIMESTAMP.sql
        echo `date` " BACKUP STARTING $DATABASE on $FILENAME"
        $MYSQLDUMP -u$USER --password=$PASSWORD --database $DATABASE > $FILENAME
        j=`ps -ef | grep -i mysqldump | grep -v grep| wc -l`

        while [ "$j" -ne "0" ]
        do
                sleep 5
                j=`ps -ef | grep -i mysqldump | grep -v grep| wc -l`
        done

        /bin/gzip $FILENAME
        echo `date` " BACKUP FINISHED $DATABASE"
fi
echo `date` " BACKUP END"

exit 

Syntax how to execute the script

• Backup all the databases

./backup_mysql.ksh mysql ALL

this command will backup all databases (except  information_schema) from the instance mysql.

• Backup a specific database

./backup_mysql.ksh mysql mysql

This command will backup the database mysql in the instance mysql.


How to add this script in the crontab

#
# min|hour |day  |month|day  | script
#    |     |of mo|     |of wk|
#----|-----|-----|-----|-----|------------------------------------------------
#
#
### Backup ALL mysql instances every day
05 00 * * * /admin/mysql/nossql/backup_mysql.ksh mysql ALL 1>/admin/mysql/noslog/backup_mysql.log 2>&1
### Backup the mysql instance every day
05 00 * * * /admin/mysql/nossql/backup_mysql.ksh mysql mysql 1>/admin/mysql/noslog/backup_mysql.log 2>&1