mysql 备份至远程服务器失败_mysqldump跨实例备份到远程主机

备份脚本：

#!/bin/bash

set -eu

exclude_acc_tables=(acc.tb1 acc.tb2 acc.tb3)

exclude_tms_tables=(tms.tb1 tms.tb2 tms.tb3)

exclude_tms_string=''

exclude_acc_string=''

for tms_tb in "${exclude_tms_tables[@]}"

do

exclude_tms_string+=" --exclude-tables=${tms_tb}"

done

for acc_tb in "${exclude_acc_tables[@]}"

do

exclude_acc_string+=" --exclude-tables=${acc_tb}"

done

backfiledir=/logs/mysqlbackupfile

backuplist=/data/scripts/mysqlscripts/dumplist

backuptype=mysqlpump

center_DB_IP=192.168.2.100

center_DB_Port=3306

basedir=/usr/local/mysql/bin

username=root

passwd=BullShit@anquan1.COM

for dbname in `cat $backuplist`

do

remoteIP=`echo $dbname|cut -d '_' -f 1`

BACKUPStarttime=`date +%Y%m%d%H%M%S`

if [ "$remoteIP" == "192.168.2.101" ]; then

$basedir/mysqlpump --user=$username --password=$passwd --host=$remoteIP -A --single-transaction --default-parallelism=8 --exclude-databases=db_name --default-character-set=utf8 ${exclude_tms_string} | gzip > $backfiledir/$dbname.$BACKUPStarttime.gz

BACKUPendtime=`date +%Y%m%d%H%M%S`

backupfileSize=`du -sh $backfiledir/$dbname.$BACKUPStarttime.gz |cut -f 1`

if [ "$backupfileSize" = "0" -o "$backupfileSize" = "4.0K" ];then

echo $dbnam backup failed! > $backfiledir/backup_result

/usr/bin/python /data/scripts/mysqlscripts/mysql_backup_result.py

else

echo "$dbname backup successful!" > $backfiledir/backup_result

rsync -P $backfiledir/$dbname.$BACKUPStarttime.gz rsync_backup@192.168.2.200::backup/mysqlbak --password-file=/etc/rsync.password

/usr/bin/python /data/scripts/mysqlscripts/mysql_backup_result.py

fi

else

BACKUPStarttime=`date +%Y%m%d%H%M%S`

$basedir/mysqlpump --user=$username --password=$passwd --host=$remoteIP -B db_name --single-transaction --default-parallelism=8 --default-character-set=utf8 ${exclude_acc_string} | gzip > $backfiledir/$dbname.$BACKUPStarttime.gz

BACKUPendtime=`date +%Y%m%d%H%M%S`

backupfileSize=`du -sh $backfiledir/$dbname.$BACKUPStarttime.gz |cut -f 1`

if [ "$backupfileSize" = "0" -o "$backupfileSize" = "4.0K" ];then

echo $dbname backup failed! > $backfiledir/backup_result

/usr/bin/python /data/scripts/mysqlscripts/mysql_backup_result.py

else

echo "$dbname backup successful!" > $backfiledir/backup_result

rsync -P $backfiledir/$dbname.$BACKUPStarttime.gz rsync_backup@192.168.2.200::backup/mysqlbak --password-file=/etc/rsync.password

/usr/bin/python /data/scripts/mysqlscripts/mysql_backup_result.py

fi

fi

done

find $backfiledir/ -mtime +10 |xargs rm -rf

python发送钉钉通知脚本：

#!/usr/local/python3/bin/python3

#coding:utf-8

#数据库备份结果钉钉报警

import requests,json,sys,os,datetime

txt=open('/logs/mysqlbackupfile/backup_result','r')

msg=txt.read()

def info():

url = 'https://oapi.dingtalk.com/robot/send?access_token=c5e20a649e2882f04a0f6eaf240ae7d125853af43e35546440209bc714773277'

headers = {'Content-Type': 'application/json;charset=utf-8'}

formdata = {"msgtype": "text","text": {"content":str(msg)}}

hhh = requests.post(url=url, data=json.dumps(formdata), headers=headers)

print(hhh.text)

if __name__ == '__main__':

info()

txt.close()

dumplist：

192.168.2.102_mysql1_accountdb

192.168.2.101_mysql2_tmsdb