Created
June 14, 2017 15:41
-
-
Save gengxiankun/cd731a5139e1cd05007b446c2107a05d to your computer and use it in GitHub Desktop.
Automatically back up SQL scripts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env bash | |
# @auther <james@lightbeijing.com> | |
set -x | |
shopt -s extglob | |
BACKUP_HOST='' | |
BACKUP_USER='' | |
BACKUP_PASSWORD='' | |
BACKUP_PORT=3306 | |
MAIL_ADDR=('james@lightbeijing.com') | |
# Back up the root directory | |
BACKUP_DIR='/data/mysql_data/master_mysql-3306/data/db' | |
# Backup the database list, all for the entire library export | |
BACKUP_DATABASES=('all' 'dev') | |
# Sql backup level d(every day) w(every week) m(every month) | |
BACKUP_LEVEL='d' | |
# Daily days of sql retention days | |
BACKUP_RESERVED_D=7 | |
# Monthly backup of sql retention days | |
BACKUP_RESERVED_W=30 | |
# The number of days to keep the sql | |
BACKUP_RESERVED_M=360 | |
# Backup file prefix | |
BACKUP_FILE_PREFIX='backupsql_' | |
# he current year and the next day | |
CURRENT_TIME=$(date +%Y%m%d-%H%M%S) | |
# The timestamp of the current date | |
TIMESTAMP=$(date -d $(date +"%Y-%m-%d") +%s) | |
# One day timestamp | |
let DAY_TIMESTAMP=60*60*24 | |
# One week timestamp | |
let WEEK_TIMESTAMP=${DAY_TIMESTAMP}*7 | |
# One month timestamp | |
let MONTH_TIMESTAMP=${WEEK_TIMESTAMP}*4 | |
# Delete the timestamp of the daily backup | |
let RESERVED_D_TIMESTAMP=${TIMESTAMP}-${BACKUP_RESERVED_D}*${DAY_TIMESTAMP} | |
# Delete the timestamp of the monthly backup | |
let RESERVED_W_TIMESTAMP=${TIMESTAMP}-${BACKUP_RESERVED_W}*${DAY_TIMESTAMP} | |
# Delete the timestamp of the annual backup | |
let RESERVED_M_TIMESTAMP=${TIMESTAMP}-${BACKUP_RESERVED_M}*${DAY_TIMESTAMP} | |
# Create the root directory | |
if [ ! -d $BACKUP_DIR ] | |
then | |
echo "info: mkdir -p $BACKUP_DIR" | |
mkdir -p $BACKUP_DIR | |
fi | |
# Create a directory based on the root directory based on the parameters | |
_mkBackupDir () | |
{ | |
BACKUP_MKDIR="${BACKUP_DIR}/$1" | |
if [ ! -d $BACKUP_MKDIR ] | |
then | |
echo "info: mkdir -p ${BACKUP_MKDIR}" | |
mkdir -p $BACKUP_MKDIR | |
fi | |
return 0 | |
} | |
# The initial time of the embedded operation | |
_get_TIMESTAMP_EMBEDDING () | |
{ | |
if [ ! -e ${BACKUP_TMP_DIR}/timestampembedding ] | |
then | |
echo "info: Timestamp embedding" | |
echo $TIMESTAMP > ${BACKUP_TMP_DIR}/timestampembedding | |
fi | |
TIMESTAMP_EMBEDDING=$(cat ${BACKUP_TMP_DIR}/timestampembedding) | |
} | |
# Use mysqldump and gzip to package backup sql files | |
_dumpsql () | |
{ | |
_mkBackupDir $1/$2 && BACKUP_DATABASE_DIR=$BACKUP_MKDIR | |
BACKUP_FILE_SQL=${BACKUP_FILE_PREFIX}$1_$2_${CURRENT_TIME}_${TIMESTAMP}.sql.tar.gz | |
if [[ $2 == 'all' ]] | |
then | |
local DATABASE_NAME='--all-databases' | |
else | |
local DATABASE_NAME=$2 | |
fi | |
echo "Package export sql file : ${BACKUP_DATABASE_DIR}/${BACKUP_FILE_SQL}" >> $LOG_FILE | |
mysqldump -u${BACKUP_USER} -h${BACKUP_HOST} -P${BACKUP_PORT} -p${BACKUP_PASSWORD} ${DATABASE_NAME} | gzip > ${BACKUP_DATABASE_DIR}/${BACKUP_FILE_SQL} | |
return 0 | |
} | |
# Get the timestamp of the file | |
_getFileTimestamp () | |
{ | |
local filefead=${1##*_} | |
filetimestamp=${filefead%%.*} | |
} | |
# Get the maximum timestamp in the file in the directory, and clear the expired file | |
_getMaxTimestampAndClearOldFile () | |
{ | |
local files=$(ls $1) | |
_getFileTimestamp ${files['0']} && maxtimestamp=${filetimestamp} | |
case $2 in | |
'd') | |
local RESERVED_TIMESTAMP=$RESERVED_D_TIMESTAMP | |
;; | |
'w') | |
local RESERVED_TIMESTAMP=$RESERVED_W_TIMESTAMP | |
;; | |
'm') | |
local RESERVED_TIMESTAMP=$RESERVED_M_TIMESTAMP | |
;; | |
esac | |
for file in $files | |
do | |
_getFileTimestamp $file | |
if [[ $filetimestamp > $maxtimestamp ]] | |
then | |
maxtimestamp=$filetimestamp | |
fi | |
if [[ $filetimestamp < $RESERVED_TIMESTAMP ]] | |
then | |
echo "info: rm -rf ${file}" | |
rm -rf ${file} | |
fi | |
done | |
return 0 | |
} | |
_init () | |
{ | |
for database in ${BACKUP_DATABASES[@]} | |
do | |
echo " " >> $LOG_FILE | |
echo "${database}:" >> $LOG_FILE | |
echo "--------------------------------------------------------------" >> $LOG_FILE | |
if [[ $BACKUP_LEVEL == 'd' ]] | |
then | |
echo "Every Day :" >> $LOG_FILE | |
_getMaxTimestampAndClearOldFile ${BACKUP_DAY_DIR}/$database d | |
_dumpsql day $database | |
fi | |
let DIFF_TIMESTAMP=${TIMESTAMP}-${TIMESTAMP_EMBEDDING} | |
let DIFF_WEEK=${DIFF_TIMESTAMP}/${WEEK_TIMESTAMP} | |
let DIFF_MONTH=${DIFF_TIMESTAMP}/${MONTH_TIMESTAMP} | |
if [[ $DIFF_TIMESTAMP == 0 ]] | |
then | |
if [[ ! -d ${BACKUP_WEEK_DIR}/$database ]] | |
then | |
echo "Every Week :" >> $LOG_FILE | |
_dumpsql week $database | |
fi | |
if [[ ! -d ${BACKUP_MONTH_DIR}/$database ]] | |
then | |
echo "Every Month :" >> $LOG_FILE | |
_dumpsql month $database | |
fi | |
fi | |
if [[ $DIFF_WEEK != 0 && $BACKUP_LEVEL != 'm' ]] | |
then | |
_getMaxTimestampAndClearOldFile ${BACKUP_WEEK_DIR}/$database w | |
let MAX_DIFF_TIMESTAMP=${maxtimestamp}-${TIMESTAMP_EMBEDDING} | |
let MAX_DIFF_WEEK=${MAX_DIFF_TIMESTAMP}/${WEEK_TIMESTAMP} | |
if [[ $DIFF_WEEK > $MAX_DIFF_WEEK ]] | |
then | |
echo "Every Week :" >> $LOG_FILE | |
_dumpsql week $database | |
fi | |
fi | |
if [[ $DIFF_MONTH != 0 ]] | |
then | |
_getMaxTimestampAndClearOldFile ${BACKUP_MONTH_DIR}/$database m | |
let MAX_DIFF_TIMESTAMP=${maxtimestamp}-${TIMESTAMP_EMBEDDING} | |
let MAX_DIFF_MONTH=${MAX_DIFF_TIMESTAMP}/${MONTH_TIMESTAMP} | |
if [[ $DIFF_MONTH > $MAX_DIFF_MONTH ]] | |
then | |
echo "Every Month :" >> $LOG_FILE | |
_dumpsql month $database | |
fi | |
fi | |
done | |
return 0 | |
} | |
# Get the tmp path | |
_mkBackupDir tmp && BACKUP_TMP_DIR=${BACKUP_MKDIR} | |
# Get the initial buried time point | |
_get_TIMESTAMP_EMBEDDING | |
LOG_FILE=${BACKUP_TMP_DIR}/${BACKUP_FILE_PREFIX}_${TIMESTAMP}.log | |
echo "为IP是${BACKUP_HOST}端口为${BACKUP_PORT}的mysql服务器进行备份" >> $LOG_FILE | |
_mkBackupDir day && BACKUP_DAY_DIR=${BACKUP_MKDIR} | |
_mkBackupDir week && BACKUP_WEEK_DIR=${BACKUP_MKDIR} | |
_mkBackupDir month && BACKUP_MONTH_DIR=${BACKUP_MKDIR} | |
# Start the backup | |
_init | |
for email in $MAIL_ADDR | |
do | |
cat $LOG_FILE | mutt -s 'MySQL Backup Log and SQL Files for '`hostname`" - `date`" $email | |
done |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment