脚本功能：
- 支持全量 | 增量备份（备份时指定 full | incr）；
- 支持备份到华为云对象存储中；
- 流式上传到对象存储中，本地不落盘；
- 备份完成后，会验证备份的有效性；
- 备份失败发送企业微信告警；
- 配合定时任务，每周六全量备份，周日至周五增量备份；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
| #!/bin/bash
DB_NAME=$1
BACKUP_TYPE=$2
TS=$(date "+%Y%m%d")
MYSQL_USER="backup"
MYSQL_PASSWORD="abcd1234"
CONF_FOO_PATH="/etc"
CONF_FILES=("my.cnf" "my.cnf.d")
_result_socket=$(grep -s '^socket' $CONF_FOO_PATH/${CONF_FILES[1]}/*.cnf | awk -F[=\ ] '{print $NF}' | sort -u)
PORT=$(grep -s '^port' $CONF_FOO_PATH/${CONF_FILES[1]}/*.cnf | awk -F[=\ ] '{print $NF}' | sort -u)
MYSQL_SOCKET=${_result_socket:-'/var/lib/mysql/mysql.sock'}
LOCAL_BACKUP_DIR="/data/backup"
RCLONE="/usr/local/bin/rclone"
RCLONE_REMOTE="huawei"
RCLONE_BUCKET="backup-bucket/mysql"
WECHAT_WEBHOOK_KEY="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
[[ $# != 2 ]] && echo -e "Usage:\n\t$0 (库名) (full|incr)" && exit 1
raise_error() {
msg="### 备份失败! \n#### $DB_NAME $TS $BACKUP_TYPE\n#### $1"
for i in {1..3}; do
curl -s "https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=$WECHAT_WEBHOOK_KEY" \
-H "Content-Type: application/json" \
-d '{
"msgtype": "markdown",
"markdown": {
"content": "'"$msg"'"
}
}'
[[ $? == 0 ]] && break || sleep $i
done
echo "$msg"
exit 1
}
[[ -z "$MYSQL_SOCKET" ]] && MYSQL_SOCKET="/var/lib/mysql/mysql.sock"
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -S "$MYSQL_SOCKET" -e "select 1;" >/dev/null 2>&1 || raise_error "数据库账号或Socket连接失败"
ulimit -n 1024000
backup_start_sec=$(date +%s)
mkdir -p $LOCAL_BACKUP_DIR/{lastTimeCheckPoint,thisTimeCheckPoint,tmpDir}
# 备份版本和配置上传
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -S "$MYSQL_SOCKET" -e "select version()\G" | \
awk '/version/{print $2}' | grep -o '[0-9\.]*' > $LOCAL_BACKUP_DIR/${DB_NAME}-${TS}.version
$RCLONE copy $LOCAL_BACKUP_DIR/${DB_NAME}-${TS}.version ${RCLONE_REMOTE}:${RCLONE_BUCKET}/ || raise_error "版本文件上传失败"
rm -f $LOCAL_BACKUP_DIR/${DB_NAME}-${TS}.version
tar -zcf $LOCAL_BACKUP_DIR/${DB_NAME}-${TS}-conf.tgz -C $CONF_FOO_PATH my.cnf my.cnf.d
$RCLONE copy $LOCAL_BACKUP_DIR/${DB_NAME}-${TS}-conf.tgz ${RCLONE_REMOTE}:${RCLONE_BUCKET}/ || raise_error "配置文件上传失败"
rm -f $LOCAL_BACKUP_DIR/${DB_NAME}-${TS}-conf.tgz
BACKUP_OBJECT="${DB_NAME}-${TS}-${BACKUP_TYPE}.tar.gz"
if [[ $BACKUP_TYPE == "full" ]]; then
mariabackup --backup --slave-info --tmpdir=$LOCAL_BACKUP_DIR/tmpDir \
--user="$MYSQL_USER" --password="$MYSQL_PASSWORD" --socket="$MYSQL_SOCKET" \
--extra-lsndir=$LOCAL_BACKUP_DIR/thisTimeCheckPoint \
--stream=mbstream | gzip -c | $RCLONE rcat --s3-upload-cutoff 64M --s3-chunk-size 128M ${RCLONE_REMOTE}:${RCLONE_BUCKET}/${BACKUP_OBJECT} || raise_error "全量备份或上传失败"
### >>>>>>>>>>> 恢复测试 START <<<<<<<<<<<<<<
echo "开始恢复测试..."
RESTORE_TEST_DIR="$LOCAL_BACKUP_DIR/restore_test/$PORT"
rm -rf "$RESTORE_TEST_DIR" && mkdir -p "$RESTORE_TEST_DIR"
file_list=()
full_ts=""
for ((i=0;i<=7;i++)); do
ts_check=$(date -d "$TS -$i day" +%Y%m%d)
if $RCLONE lsf ${RCLONE_REMOTE}:${RCLONE_BUCKET}/ | grep -q "^${DB_NAME}-${ts_check}-full.tar.gz$"; then
full_ts="$ts_check"
file_list+=("${DB_NAME}-${ts_check}-full.tar.gz")
break
fi
done
if [[ -z "$full_ts" ]]; then
raise_error "恢复测试失败：未找到全量备份"
fi
for ((i=0;i<=7;i++)); do
ts_check=$(date -d "$full_ts +$i day" +%Y%m%d)
if $RCLONE lsf ${RCLONE_REMOTE}:${RCLONE_BUCKET}/ | grep -q "^${DB_NAME}-${ts_check}-incr.tar.gz$"; then
file_list+=("${DB_NAME}-${ts_check}-incr.tar.gz")
fi
done
echo "下载恢复测试文件：${file_list[@]}"
for f in "${file_list[@]}"; do
$RCLONE copy ${RCLONE_REMOTE}:${RCLONE_BUCKET}/$f $RESTORE_TEST_DIR/ || raise_error "恢复测试失败：下载文件 $f 失败"
done
echo "开始测试解压和prepare..."
full_dir="$RESTORE_TEST_DIR/full"
mkdir -p "$full_dir"
gzip -dc $RESTORE_TEST_DIR/${file_list[0]} | mbstream -x -C "$full_dir" || raise_error "全量解压失败"
mariabackup --prepare --target-dir="$full_dir" --apply-log-only || raise_error "全量prepare失败"
prev_lsn=$(awk -F'= ' '/to_lsn/ {print $2}' $full_dir/xtrabackup_checkpoints)
for ((i=1;i<${#file_list[@]};i++)); do
incr_dir="$RESTORE_TEST_DIR/incr_$i"
mkdir -p "$incr_dir"
gzip -dc $RESTORE_TEST_DIR/${file_list[$i]} | mbstream -x -C "$incr_dir" || raise_error "增量 $i 解压失败"
from_lsn=$(awk -F'= ' '/from_lsn/ {print $2}' $incr_dir/xtrabackup_checkpoints)
if [[ "$from_lsn" != "$prev_lsn" ]]; then
echo "增量 $i from_lsn ($from_lsn) 与前一个 to_lsn ($prev_lsn) 不一致，跳过"
continue
fi
mariabackup --prepare --target-dir="$full_dir" --incremental-dir="$incr_dir" --apply-log-only || raise_error "增量 $i prepare失败"
prev_lsn=$(awk -F'= ' '/to_lsn/ {print $2}' $incr_dir/xtrabackup_checkpoints)
done
mariabackup --prepare --target-dir="$full_dir" || raise_error "最后apply-log失败"
echo "恢复测试通过"
rm -rf "$RESTORE_TEST_DIR"
### >>>>>>>>>>> 恢复测试 END <<<<<<<<<<<<<<
elif [[ $BACKUP_TYPE == "incr" ]]; then
mariabackup --backup --slave-info --tmpdir=$LOCAL_BACKUP_DIR/tmpDir \
--user="$MYSQL_USER" --password="$MYSQL_PASSWORD" --socket="$MYSQL_SOCKET" \
--extra-lsndir=$LOCAL_BACKUP_DIR/thisTimeCheckPoint \
--incremental-basedir=$LOCAL_BACKUP_DIR/lastTimeCheckPoint \
--stream=mbstream | gzip -c | $RCLONE rcat ${RCLONE_REMOTE}:${RCLONE_BUCKET}/${BACKUP_OBJECT} || raise_error "增量备份或上传失败"
else
raise_error "参数错误: 仅支持 full 或 incr"
fi
backup_end_sec=$(date +%s)
backup_spent_sec=$[ $backup_end_sec - $backup_start_sec ]
START=$(date -d@$backup_start_sec +"%Y-%m-%d %H:%M:%S")
END=$(date -d@$backup_end_sec +"%Y-%m-%d %H:%M:%S")
SPENT="$((backup_spent_sec / 3600))小时 $(((backup_spent_sec / 60) % 60))分钟 $((backup_spent_sec % 60))秒"
SIZE=$(/usr/local/bin/rclone lsf ${RCLONE_REMOTE}:${RCLONE_BUCKET}/ --format "ps" | grep "^${DB_NAME}-${TS}-${BACKUP_TYPE}.tar.gz;" | awk -F';' '{ printf "%d", $2 / 1024 / 1024 / 1024 }')
rm -rf $LOCAL_BACKUP_DIR/lastTimeCheckPoint/*
cp -a $LOCAL_BACKUP_DIR/thisTimeCheckPoint/* $LOCAL_BACKUP_DIR/lastTimeCheckPoint/
echo "备份成功: $DB_NAME-$TS-$BACKUP_TYPE 已上传到OBS"
echo -e "备份成功!\n Size: $SIZE GB \nStart: $START\n End: $END\nSpent: $SPENT"
|
设置定时任务
1
2
3
4
| #Ansible: 全量备份 每周六
25 3 * * 6 bash -x /usr/local/scripts/mysql-backup.sh DB_NAME full > /var/log/mysql/DB_NAME-$(date "+\%Y\%m\%d")-full.log 2>&1
#Ansible: 增量备份 每周日至周五
25 3 * * 0-5 bash -x /usr/local/scripts/mysql-backup.sh DB_NAME incr > /var/log/mysql/DB_NAME-$(date "+\%Y\%m\%d")-incr.log 2>&1
|