RDS Backup Script

rds_backup.sh

#!/bin/bash
# Database credentials
DB_USER=root
DB_PASS="-pvchangeme"
RDS_HOST=warp.cc7vd70wnd3ed.us-east-1.rds.amazonaws.com
# Get list of Database
DBS_LIST=$(mysql -u $DB_USER $DB_PASS -h $RDS_HOST -Bse 'show databases')
# Log file
BAKUP_LOG=/var/log/rds_backup.log
# Backup Base directory
BASE_BAK_FLDR=/backup/mysql
# Full backup file rotation threshold.
RM_FLDR_DAYS="+14"
# From here, only edit if you know what you are doing.
index=0
PING=$(mysqladmin ping -u $DB_USER $DB_PASS -h $RDS_HOST 2>/dev/null)
if [ "$PING" != "mysqld is alive" ]; then
        echo "Error:: Unable to connected to MySQL Server, exiting !!"
        exit 101
fi

case "$1" in
        full)
                mysql -u $DB_USER $DB_PASS -h $RDS_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 -h $RDS_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 -h $RDS_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 $DB -h $RDS_HOST --no-create-info --tables "
                                else
                                        DUMP_OPT="-u $DB_USER $DB_PASS $DB -h $RDS_HOST --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
    ;;
*)
                echo "Usage:: $0 (full)"
                echo "full: Full backup"
        ;;

esac