shell定时备份MySQL数据并解决 Using a password on the command line ...

mysql: [Warning] Using a password on the command line interface can be insecure 在MySQL5.6+以上版本直接在shell中输入密码会提示次警告且无法连接成功，那我们应该怎么解决这个问题呢，答案就是使用 mysql_config_editor创建一个填写账号密码的隐藏配置文件登录时使用--login-path来引用

1、使用mysql_config_editor配置账号密码

mysql_config_editor set --login-path=xxx --host=xxxxx --port=3306 --user=root --password

注意host在阿里云服务器上且用rds时使用内网ip

2、mysqldump使用

mysqldump  --login-path=xxx 数据库名称 > /tmp/导出的文件名称.sql

此处的login-path对应第一步的名称

3、完整的导出表名并自动上传到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-xxxx.aliyuncs.com"
###bucket名字###
Bucket="Bucket的名称"
###Access ID###
Id="xxx"
###Access Key###
Key="xxx"
### oss地址###
OssHost=$Bucket.$Host
### 第一步时指定login-path的xxx
LoginPath="xxx"
###备份数据库账号信息### grep -v 输出排除命令意思是mysql和information_schema不输出
DBS=`mysql --login-path=$LoginPath -Bse "show databases" | grep -v "information_schema" | grep -v "mysql"`
###罗列数据库信息###
#========================BackUp SQL========================

for dbName in $DBS; do
    mysqldump --login-path=$LoginPath --set-gtid-purged=OFF $dbName > /tmp/$dbName.$Date.sql
	###zip压缩设置的密码###
#    zip -P 密码 /tmp/$dbName.$Date.sql.zip /tmp/$dbName.$Date.sql
    zip /tmp/$dbName.$Date.sql.zip /tmp/$dbName.$Date.sql
	sendData="数据库[$dbName]在$Date执行备份, 操作结果："
    if [ -s /tmp/$dbName.$Date.sql.zip ] ; then

        source="/tmp/$dbName.$Date.sql.zip"
        dest="$BucketTime/$dbName.$Date.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.$OldDate*
			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 'https://oapi.dingtalk.com/robot/send?access_token=xxxxxx' \
			  --header 'Content-Type: application/json' \
			  --header 'cache-control: no-cache' \
			  --data '{"msgtype": "text",
						"text": {
							 "content": "'"$sendData"'"
						}
					  }'
done
#========================BackUp SQL========================

脚本来自于互联网，自行调整部分代码，因关闭原网页后没找到原网页，所以没有注明来源。请谅解