将MySQL数据库备份到阿里云,单表压缩快速恢复。zip压缩上传,上传速度快,节省oss存储空间
#!/bin/bash
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
export PATH
Date=`date +%Y-%m-%d_%H:%M:%S`
BucketTime=`date +%Y%m`
OldDate=$(date -d "-7 days" "+%Y-%m-%d")
###oss的地址###
#Host="oss-cn-beijing.aliyuncs.com"
Host="oss-cn-qingdao-internal.aliyuncs.com"
###bucket名字###
Bucket="oss-bucket名称"
###Access ID###
Id="阿里云oss AccessID"
###Access Key###
Key="阿里云oss Access key"
OssHost=$Bucket.$Host
LoginPath="MySQL loginPath登录设置名称"
###备份数据库账号信息### grep -v 输出排除命令意思是mysql和information_schema不输出
DBS=`mysql --login-path=$LoginPath -Bse "show databases" | grep -v "information_schema" | grep -v "mysql"`
###罗列数据库信息###
#========================BackUp SQL========================
CURRENT_TIME=`date +"%Y%m%d%H%M%S"`
for dbName in $DBS; do
echo "开始处理数据库: $dbName"
TABS=`mysql --login-path=$LoginPath -Bse "select table_name from information_schema.tables where table_schema='$dbName'"`
mkdir /tmp/$dbName-$CURRENT_TIME
echo "创建任务目录: /tmp/$dbName-$CURRENT_TIME"
for tabName in $TABS; do
# 跳过
if [ "${tabName}" = "跳过指定表的表名" ]; then
echo "开始备份表:$tabName"
else
echo "开始备份表:$tabName"
mysqldump --login-path=$LoginPath --skip-lock-tables --set-gtid-purged=OFF $dbName $tabName > /tmp/$dbName-$CURRENT_TIME/$tabName.sql
# mysqldump --login-path=$LoginPath --ignore-table=$dbName.忽略备份的表名 --skip-lock-tables --set-gtid-purged=OFF $dbName $tabName > /tmp/$dbName-$CURRENT_TIME/$tabName.sql
fi
done
done
# zip -P 密码 /tmp/$dbName.$Date.sql.zip /tmp/$dbName.$Date.sql
echo "开始压缩目录:/tmp/$dbName-$CURRENT_TIME"
zip -r /tmp/$dbName-$CURRENT_TIME.sql.zip /tmp/$dbName-$CURRENT_TIME
###zip压缩设置的密码###
sendData="数据库[$dbName]在$Date执行备份, 操作结果:"
if [ -s /tmp/$dbName-$CURRENT_TIME.sql.zip ] ; then
source="/tmp/$dbName-$CURRENT_TIME.sql.zip"
dest="$BucketTime/SQL/$dbName-$CURRENT_TIME.sql.zip"
resource="/${Bucket}/${dest}"
contentType=`file -ib ${source} |awk -F ";" '{print $1}'`
dateValue="`TZ=GMT env LANG=en_US.UTF-8 date +'%a, %d %b %Y %H:%M:%S GMT'`"
stringToSign="PUT\n\n${contentType}\n${dateValue}\n${resource}"
signature=`echo -en $stringToSign | openssl sha1 -hmac ${Key} -binary | base64`
url=http://${OssHost}/${dest}
echo "upload ${source} to ${url}"
curl -i -q -X PUT -T "${source}" \
-H "Host: ${OssHost}" \
-H "Date: ${dateValue}" \
-H "Content-Type: ${contentType}" \
-H "Authorization: OSS ${Id}:${signature}" \
${url}
if [ $? -ne 0 ];then
# echo -e ""dbName $dbName $Date Fail Upload"" | mutt -s "'dbName $dbName $Date Fail Upload'" zsljava@163.com
sendData="$sendData 上传oss失败!"
else
# echo -e ""dbName $dbName $Date Success"" | mutt -s "'dbName $dbName $Date Success'" zsljava@163.com
rm -rf /tmp/$dbName-$CURRENT_TIME*
sendData="$sendData 备份成功!"
fi
else
# echo -e ""dbName $dbName $Date Fail Backup "" | mutt -s "'dbName $dbName $Date Fail Backup'" zsljava@163.com
sendData="$sendData 备份失败!"
fi
# 发送钉钉消息 采用关键字白名单直接发送就行了
curl --request POST \
--url '钉钉消息通知群URL' \
--header 'Content-Type: application/json' \
--header 'cache-control: no-cache' \
--data '{"msgtype": "text",
"text": {
"content": "'"$sendData"'"
}
}'
#========================BackUp SQL========================
注意:本文归作者所有,未经作者允许,不得转载