To backup all databases in one file.
./mysql_backup.sh
To back up a particular database to a directory named the database.
./mysql_backup.sh DB_NAME
To backup every database in seperated directories.
./mysql_backup.sh combined
#!/bin/bash | |
STORE_FOLDER="/opt/mysql_backup" | |
MYSQL_HOST="127.0.0.1" | |
MYSQL_PORT="3306" | |
MYSQL_USER="root" | |
MYSQL_PASSWORD="" # SET YOUR PASSWORD | |
MYSQL="$(which mysql)" | |
MYSQLDUMP="$(which mysqldump)" | |
CURRENT=$(date +"%Y-%m-%d %H:%M:%S") | |
CURRENT_DAY=$(date +"%Y-%m-%d") | |
CURRENT_FILENAME="${CURRENT//[ ]/_}" | |
CURRENT_FILENAME="${CURRENT_FILENAME//[:]/-}" | |
HOUR_RETENTION="24" | |
DAY_RETENTION="7" | |
WEEK_RETENTION="5" | |
MONTH_RETENTION="12" | |
function do_backups() { | |
backup_db=$1 | |
# RUN DUMP | |
if [ "$backup_db" == "all" ]; then | |
BACKUP_PATH=$STORE_FOLDER/all | |
[[ ! -d "$BACKUP_PATH" ]] && mkdir -p "$BACKUP_PATH/hourly" | |
echo " Creating $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz" | |
$MYSQLDUMP -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD --all-databases | gzip -9 > $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz | |
else | |
BACKUP_PATH=$STORE_FOLDER/$backup_db | |
[[ ! -d "$BACKUP_PATH" ]] && mkdir -p "$BACKUP_PATH/hourly" | |
echo " Creating $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz" | |
$MYSQLDUMP -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD $backup_db | gzip -9 > $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz | |
fi | |
[[ ! -d "$BACKUP_PATH/daily" ]] && mkdir -p "$BACKUP_PATH/daily" | |
[[ ! -d "$BACKUP_PATH/weekly" ]] && mkdir -p "$BACKUP_PATH/weekly" | |
[[ ! -d "$BACKUP_PATH/monthly" ]] && mkdir -p "$BACKUP_PATH/monthly" | |
# COPY DAILY | |
if [ `date +%k` -eq 23 ]; then | |
if [ ! -f "$BACKUP_PATH/daily/$CURRENT_FILENAME.sql.gz" ]; then | |
cp $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz $BACKUP_PATH/daily/$CURRENT_DAY.sql.gz | |
fi | |
fi | |
# COPY WEEKLY | |
if [ `date +%u` -eq 7 ]; then | |
if [ ! -f "$BACKUP_PATH/weekly/$CURRENT_FILENAME.sql.gz" ]; then | |
cp $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz $BACKUP_PATH/weekly/$CURRENT_DAY.sql.gz | |
fi | |
fi | |
# COPY MONTHLY | |
if [ `date +%d` -eq 25 ]; then | |
if [ ! -f "$BACKUP_PATH/monthly/$CURRENT_FILENAME.sql.gz" ]; then | |
cp $BACKUP_PATH/hourly/$CURRENT_FILENAME.sql.gz $BACKUP_PATH/monthly/$CURRENT_DAY.sql.gz | |
fi | |
fi | |
# DELETE OLD BACKUPS | |
find $BACKUP_PATH/hourly/ -type f -not -newermt "`date +"%Y-%m-%d %H:%M:%S" --date $HOUR_RETENTION' hours ago'`" -delete | |
find $BACKUP_PATH/daily/ -type f -not -newermt "`date +"%Y-%m-%d %H:%M:%S" --date $DAY_RETENTION' days ago'`" -delete | |
find $BACKUP_PATH/weekly/ -type f -not -newermt "`date +"%Y-%m-%d %H:%M:%S" --date $WEEK_RETENTION' weeks ago'`" -delete | |
find $BACKUP_PATH/monthly/ -type f -not -newermt "`date +"%Y-%m-%d %H:%M:%S" --date $MONTH_RETENTION' months ago'`" -delete | |
} | |
echo "*** MySQL Backup" | |
echo | |
echo "To be deleted:" | |
echo " Deleting hourly backups older than "`date +"%Y-%m-%d %H:%M:%S" --date $HOUR_RETENTION' hours ago'` | |
echo " Deleting daily backups older than "`date +"%Y-%m-%d %H:%M:%S" --date $DAY_RETENTION' days ago'` | |
echo " Deleting weekly backups older than "`date +"%Y-%m-%d %H:%M:%S" --date $WEEK_RETENTION' weeks ago'` | |
echo " Deleting monthly backups older than "`date +"%Y-%m-%d %H:%M:%S" --date $MONTH_RETENTION' months ago'` | |
echo | |
# RUN BACKUP | |
echo "Starting MySQL backup..." | |
if [ -z "$1" ]; then | |
do_backups all | |
else | |
if [ "$1" == "combined" ]; then | |
databases=($($MYSQL -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASSWORD -Bse "show databases" | grep -i -v "_schema" | grep -i -v "sys" | grep -i -v "mysql")) | |
for db in "${databases[@]}"; do | |
echo "Starting $db MySQL backup..." | |
do_backups $db | |
done | |
else do_backups $1 | |
fi | |
fi |