107 lines
3.6 KiB
Bash
107 lines
3.6 KiB
Bash
|
#!/bin/bash
|
|||
|
|
currentTime=$(date +"%Y%m%d%H")
|
|||
|
|
userName=$1
|
|||
|
|
password=$2
|
|||
|
|
dbName=$3
|
|||
|
|
host=$4
|
|||
|
|
# 定义备份文件存放目录
|
|||
|
|
backUpPath=$5
|
|||
|
|
ossPath=$6
|
|||
|
|
execPath=""
|
|||
|
|
|
|||
|
|
if [ -z "$7" ]; then
|
|||
|
|
# 如果参数不存在，则使用默认值
|
|||
|
|
execPath="docker exec maria /usr/bin/mysqlbinlog"
|
|||
|
|
else
|
|||
|
|
# 如果参数存在，则使用命令行参数的值
|
|||
|
|
execPath="$7"
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
|
|||
|
|
# 获取上次备份的binlog文件名和位置
|
|||
|
|
LAST_BACKUP=$(cat "$backUpPath/last_backup.txt")
|
|||
|
|
LAST_BINLOG=$(echo "$LAST_BACKUP" | awk '{print $1}')
|
|||
|
|
LAST_POSITION=$(echo "$LAST_BACKUP" | awk '{print $2}')
|
|||
|
|
|
|||
|
|
# 如果是第一次运行，设置初始值
|
|||
|
|
|
|||
|
|
|
|||
|
|
# 获取当前binlog文件名和位置
|
|||
|
|
CURRENT_BINLOG=$(mysql -u$userName -p$password -h$host -N -e "SHOW MASTER STATUS" | awk '{print $1}')
|
|||
|
|
CURRENT_POSITION=$(mysql -u$userName -p$password -h$host -N -e "SHOW MASTER STATUS" | awk '{print $2}')
|
|||
|
|
|
|||
|
|
if [[ -z "$LAST_BACKUP" ]]; then
|
|||
|
|
LAST_BINLOG="ON.000001"
|
|||
|
|
LAST_POSITION="0"
|
|||
|
|
fi
|
|||
|
|
echo ""
|
|||
|
|
|
|||
|
|
echo current_binlog $CURRENT_BINLOG
|
|||
|
|
echo current_binPostition $CURRENT_POSITION
|
|||
|
|
# 备份增量变更到文件
|
|||
|
|
# 如果上次备份的binlog文件名和位置为空，则进行全量备份
|
|||
|
|
cd $backUpPath
|
|||
|
|
#if [ -z "$LAST_BINLOG" ]; then
|
|||
|
|
# mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST $MYSQL_DATABASE > "$BACKUP_DIR/full_backup.sql"
|
|||
|
|
#else
|
|||
|
|
echo "docker exec maria /usr/bin/mysqlbinlog -u"${userName}" -p"${password}" -h"${host}" --start-position="${LAST_POSITION}" --stop-position="${CURRENT_POSITION}" "$LAST_BINLOG" > "${backUpPath}"/"${dbName}"-"${currentTime}".sql"
|
|||
|
|
$execPath -u${userName} -p${password} -h${host} --start-position=${LAST_POSITION} --stop-position=${CURRENT_POSITION} /var/lib/mysql/$LAST_BINLOG > ${backUpPath}/${dbName}-incremental-${LAST_POSITION}-${CURRENT_POSITION}-${currentTime}.sql
|
|||
|
|
zip ${backUpPath}/${dbName}-incremental-${LAST_POSITION}-${CURRENT_POSITION}-${currentTime}.sql.zip ${backUpPath}/${dbName}-incremental-${LAST_POSITION}-${CURRENT_POSITION}-${currentTime}.sql
|
|||
|
|
rm ${backUpPath}/${dbName}-incremental-${LAST_POSITION}-${CURRENT_POSITION}-${currentTime}.sql
|
|||
|
|
ossutil cp ${dbName}-incremental-${LAST_POSITION}-${CURRENT_POSITION}-${currentTime}.sql.zip oss://$ossPath/$file
|
|||
|
|
#fi
|
|||
|
|
# 更新上次备份的binlog文件名和位置
|
|||
|
|
echo "$CURRENT_BINLOG $CURRENT_POSITION" > "last_backup.txt"
|
|||
|
|
|
|||
|
|
calculate_hour_diff() {
|
|||
|
|
# 传入两个时间字符串作为参数
|
|||
|
|
local time1=$1
|
|||
|
|
local time2=$2
|
|||
|
|
|
|||
|
|
# 截取时间字符串中的年、月、日和小时部分
|
|||
|
|
local year1=${time1:0:4}
|
|||
|
|
local month1=${time1:4:2}
|
|||
|
|
local day1=${time1:6:2}
|
|||
|
|
local hour1=${time1:8:2}
|
|||
|
|
|
|||
|
|
local year2=${time2:0:4}
|
|||
|
|
local month2=${time2:4:2}
|
|||
|
|
local day2=${time2:6:2}
|
|||
|
|
local hour2=${time2:8:2}
|
|||
|
|
|
|||
|
|
# 将时间字符串转换为时间戳
|
|||
|
|
local timestamp1=$(date -d "$year1-$month1-$day1 $hour1:00:00" +%s)
|
|||
|
|
local timestamp2=$(date -d "$year2-$month2-$day2 $hour2:00:00" +%s)
|
|||
|
|
|
|||
|
|
# 计算两个时间戳的差值，单位为小时
|
|||
|
|
local diff_hours=$(( ($timestamp2 - $timestamp1) / 3600 ))
|
|||
|
|
|
|||
|
|
# 返回计算结果
|
|||
|
|
echo $diff_hours
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
CURRENT_DATE=$(date +%Y%m%d%H)
|
|||
|
|
OLD_BACKUPS=()
|
|||
|
|
for file in ${dbName}-incremental-*.sql.zip; do
|
|||
|
|
file_date=$(echo "$file" | grep -oP "\d{10}(?=.sql.zip)")
|
|||
|
|
if [ ${#file_date} -le 1 ]; then
|
|||
|
|
continue
|
|||
|
|
fi
|
|||
|
|
echo $file_date
|
|||
|
|
difft=$(calculate_hour_diff $file_date $CURRENT_DATE )
|
|||
|
|
echo difft $difft
|
|||
|
|
if [[ difft -gt 24 ]]; then
|
|||
|
|
echo $file $CURRENT_DATE $file_date $((CURRENT_DATE - file_date))
|
|||
|
|
OLD_BACKUPS+=("$file")
|
|||
|
|
fi
|
|||
|
|
done
|
|||
|
|
|
|||
|
|
|
|||
|
|
# 遍历数组，打印每个文件名, 超过24小时的增量备份文件要删除
|
|||
|
|
for file in "${OLD_BACKUPS[@]}"; do
|
|||
|
|
echo "$file"
|
|||
|
|
rm $file
|
|||
|
|
ossutil rm oss://$ossPath/$file
|
|||
|
|
done
|