一、备份效验的重要性
备份就是为了当业务数据出现问题时,能够有效的恢复,把企业损失降到最低,那么我们定期效验备份是否有效,就显得十分重要了,而这么繁琐并且繁重的工作,我们手工做会占用我们大量的时间与精力,所以就出现了此篇文章,在这里写了个脚本,帮助大家。
但我还是希望您,用不到备份,业务稳定
题外话:如何您都没有想过这个问题,可能是已经是高手了,请您留言告诉我您的建议,谢谢。
二、备份效验脚本(也可以提供线下select使用)
#---------------------------------------------------------
# $Name: analysis.sh
# $Version: v1.0
# $Author: qiuyuetao
# $organization: www.dgstack.cn
# $Create Date: 2018-12-27
# $Description: Analysis Databse
#---------------------------------------------------------
#source /etc/init.d/functions
DAY=`date +%Y-%m-%d`
DOWN_DIR="/home/backup/database/10.27.82.127"
DBUSER="用户"
DBPASSWD="数据库密码"
DB="analysis" #效验的库
BIN_PATH="/usr/bin"
LOG="/root/shell/analysis/analysis.log" #效验的结果
##WeiXin-报警-----------------------------------------------
CropID='企业微信ID'
Secret='HxTbS_-rNujfHBAqFt7XMifFKEHQTX_yTNK2ciGob43mHgfc_GJiNW2dwjlitOCS'
APIURL="https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=$CropID&corpsecret=$Secret"
TOKEN=$(/usr/bin/curl -s -G $APIURL | awk -F" '{print $10}')
POSTURL="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=$TOKEN"
##------------------------------------------------------
function body() {
local int AppID=1
local UserID=企业用户名称
local PartyID=2
printf '{n'
printf 't"touser": "'"$UserID""",n"
printf 't"toparty": "'"$PartyID""",n"
printf 't"msgtype": "text",n'
printf 't"agentid": "'"$AppID""",n"
printf 't"text": {n'
printf 'tt"content": "'"$Msg"""n"
printf 't},n'
printf 't"safe":"0"n'
printf '}n'
}
## Download backup file +++++++++++++++++++++++++++++++++++++++++++++++++++++++
for n in auditdb mysql wenda ylhc yloa yloanew
do
scp jumpuser@数据库IP:/data/backup/mysql/dump/$n/"$DAY"*.sql.gz $DOWN_DIR/$n
done
if [ $? -eq 0 ]; then
echo "Download database dump file success `date +%F %H:%M:%S`" >> "$LOG"
/bin/gzip -d /home/backup/database/10.27.82.127/ylhc/*.sql.gz &&
SOURCESQL=`ls $DOWN_DIR/ylhc/$(date +%F)*.sql`
echo $SOURCESQL
else
echo "Download database dump file failed!!! `date +%F %H:%M:%S`" >> "$LOG"
Msg="数据统计---数据库下载失败!"
echo "$(body guozhiheng0123 $2 )" $POSTURL
/usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL
exit
fi
##Import database +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sed -i 's#utf8mb4#utf8#g' $SOURCESQL
echo "import start `date +%F %H:%M:%S`" >> "$LOG"
"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -f "$DB" < $SOURCESQL
##-f 强制插入数据
if [ $? -eq 0 ]; then
echo "Analysis database import success `date +%F %H:%M:%S`" >> "$LOG"
Msg="数据统计---数据库同步成功(33)"
echo "$(body guozhiheng0123 $2 )" $POSTURL
/usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL
else
Msg="数据统计---数据库导入失败!"
echo "$(body guozhiheng0123 $2 )" $POSTURL
/usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL
echo "Analysis database import Failed! `date +%F %H:%M:%S`" >> "$LOG"
fi
echo "import stop `date +%F %H:%M:%S`" >> "$LOG"
三、运行结果
3.1 微信通知:
3.2 同步日志
四、备份效验
这个方法和思路就很多了,下面我简单举例一种根据时间戳
mysql -u "$DBUSER" -p"$DBPASSWD" -e "SELECT log_time FROM analysis.ylhccn_mobile_log ORDER BY log_time DESC LIMIT 1;"
#结果
+------------+
| log_time |
+------------+
| 1545819497 |
+------------+
一般时间戳与day-1 一致,就说明数据应该是有效的,不过此方法不严谨,更好的方法是在线对比数据的一致性。