MySQL Backup Script

mysql_backup.sh

#!/bin/bash
DB_USER=root
DB_PASS=""
HOST=localhost
DBS_LIST=$(mysql -u $DB_USER $DB_PASS -Bse 'show databases')
BASE_BAK_FLDR=/www/backups/dump
RM_FLDR_DAYS="+7"
index=0

PING=$(mysqladmin ping --host $HOST -u $DB_USER $DB_PASS 2>/dev/null)

if [ "$PING" != "mysqld is alive" ]; then
        echo "Error:: Unable to connected to MySQL Server, exiting !!"
        exit 101
fi

mysql -u $DB_USER $DB_PASS --host $HOST -e "FLUSH LOGS"

for DB in $DBS_LIST; do
        DB_BKP_FLDR=$BASE_BAK_FLDR/$(date +%d-%m-%Y)/$DB
        [ ! -d $DB_BKP_FLDR ]  && mkdir -p $DB_BKP_FLDR
        mysqldump -u $DB_USER $DB_PASS --host $HOST -R -d --single-transaction $DB | \
        gzip -c > $DB_BKP_FLDR/000-DB_SCHEMA.sql.gz
        index=0
        table_types=($(mysql -u $DB_USER $DB_PASS --host $HOST -e "show table status from $DB" | \
        awk '{ if ($2 == "MyISAM" || $2 == "InnoDB") print $1,$2}'))
        table_type_count=${#table_types[@]}
        while [ "$index" -lt "$table_type_count" ]; do
                START=$(date +%s)
                TYPE=${table_types[$index + 1]}
                table=${table_types[$index]}
                echo -en "$(date) : backup $DB : $table : $TYPE "
                if [ "$TYPE" = "MyISAM" ]; then
                DUMP_OPT="-u $DB_USER $DB_PASS --host $HOST $DB --no-create-info --tables "
                else
                DUMP_OPT="-u $DB_USER $DB_PASS --host $HOST $DB --no-create-info --single-transaction --tables"
                fi
                mysqldump  $DUMP_OPT $table |gzip -c > $DB_BKP_FLDR/$table.sql.gz
                index=$(($index + 2))
                echo -e " - Total time : $(($(date +%s) - $START))\n"
        done
done

if [ ! -z "$RM_FLDR_DAYS" ]; then
        echo -en "$(date) : removing folder : "
        find $BASE_BAK_FLDR/ -maxdepth 1 -mtime $RM_FLDR_DAYS -type d -exec rm -rf {} \;
        echo
fi