备份MySQL数据库到阿里云OSS

将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========================

注意：本文归作者所有，未经作者允许，不得转载