定时备份MySQL数据库并传输到OSS里

# cat /srv/shell/mysqlbackup.sh

#!/bin/bash

# 备份文件要保存的目录
DBUSER='root'
DBPASS='2nX#d557NUbjQt'

BACKPATH="/data2/backup/"

MYSQL=/usr/local/mysql/bin/mysql
MYSQLDUMP=/usr/local/mysql/bin/mysqldump

server_ip=$(/usr/sbin/ifconfig  eth0  |sed -n '2p' |awk '{print $2}' )

if [ ! -d "$BACKPATH" ]; then
  mkdir -p "$BACKPATH"
fi
# 要备份的数据库名'多个数据库用空格分开

databases=`$MYSQL -u$DBUSER -p$DBPASS -e "show databases\G"|grep Database|grep -v schema|grep -v test|awk '{print $2}'`
# 循环databases数组
for db in ${databases[*]}
  do
    echo -e " ####Begin $db Dump! Start_Time:`date +%F' '%T''%w`" >>${BACKPATH}backup.log
    # 备份数据库生成SQL文件
     #$MYSQLDUMP -u$DBUSER  -p$DBPASS   --default-character-set=utf8 -R  --triggers  --events --opt   --set-gtid-purged=off  --single-transaction --master-data=2 $db 2>/dev/null | gzip  >$BACKPATH$db-$(date +%Y%m%d).sql.gz
     $MYSQLDUMP -u$DBUSER  -p$DBPASS   --default-character-set=utf8 -R  --triggers  --events --opt   --set-gtid-purged=off  --single-transaction $db 2>/dev/null | gzip  >$BACKPATH$db-$(date +%Y%m%d).sql.gz
    echo -e " #### End  $db Dump! End_Time:`date +%F' '%T`" >>${BACKPATH}backup.log
done

#备份上传至OSS
echo -e "####Begin upload to OSS Start_Time:`date +%F' '%T''%w`" >>${BACKPATH}backup.log
/usr/local/bin/ossutil64 -c /data2/.ossutilconfig -u -r cp /data2/backup/ oss://huanma-backups/mysql/${server_ip}/
echo -e "#### End upload to OSS End_Time:`date +%F' '%T''%w`" >>${BACKPATH}backup.log

# 删除5天之前的备份数据
find $BACKPATH -mtime +4 -name "*.sql.gz" -exec rm -rf {} \;