Skip to content

Instantly share code, notes, and snippets.

@jahil
Last active October 15, 2022 19:45
Show Gist options
  • Save jahil/3bda68b3bd09b47efb6e to your computer and use it in GitHub Desktop.
Save jahil/3bda68b3bd09b47efb6e to your computer and use it in GitHub Desktop.
RDS Backup Script
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment