mysql-backup/extended.sh

#!/bin/bash
##################################
#
# Installation
#
# 0. Place this script in /usr/local/sbin with 700 rights
# 1. Create a user in MySQL with SELECT, RELOAD and LOCK TABLES on all databases.
# 2. fill in the username and password of the mysql account below
# 3. create the directory /var/myexport
# 4. crontab this script to run 3 times a day
#
#################################

USER=<user>
#PASS=<pass>
COPIES=2
BASE=<path>

if [ ! -d $BASE ]
then
        echo "Backup share not available, stopping"
        exit 1
fi


if [ $# -gt 0 ]; then
        DBLIST="$*"
else
        #DBLIST=`mysql -u $USER --password=$PASS -s -e 'show databases'`
        DBLIST=`mysql -u $USER -s -e 'show databases'`
fi

for N in $DBLIST;
do
        case $N in
                "Database")
                                echo "ignoring Database"
                ;;
                "information_schema")
                                echo "ignoring Information_Schema"
                ;;
                "performance_schema")
                                echo "ignoring Performance_schema"
                ;;
                *)
                                echo "dumping $N"
                                i=$(($COPIES - 1))
                                while [ $i -ge 0 ];
                                do
                                        [ -s $BASE/$N.sql.$i.gz ] && mv $BASE/$N.sql.$i.gz $BASE/$N.sql.$(($i+1)).gz
                                        i=$(($i-1))
                                done
                                #mysqldump -u $USER --opt --password=$PASS $N | gzip >$BASE/$N.sql.0.gz
                                mysqldump -u $USER --opt $N | gzip >$BASE/$N.sql.0.gz
                ;;
        esac
done