innobackup物理备份mysql，并将结果发送到钉钉

innobackup物理备份mysql，并将结果发送到钉钉

创建备份用户并授权

create user 'backupuser'@'127.0.0.1' identified by 'YourPassw0rd';

GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backupuser'@'127.0.0.1';

flush privileges;

show grants for 'backupuser'@'127.0.0.1';

脚本

#!/bin/bash

Ding_Webhook_Token='******钉钉Webhook_Token******'

function sendDingDing()
{
    curl -s https://oapi.dingtalk.com/robot/send?access_token="$Ding_Webhook_Token" \
    -H 'Content-Type: application/json' \
    -d '{"msgtype": "markdown","markdown": {"title": "综合数据备份","text": "'"$*"'"},"at": {"isAtAll": true}}' 2>&1 > /dev/null
}

DATE=$(date +%Y%m%d-%H%M%S)
DB_BACKUP_PATH=/data/backup-test-mysql
DB_USER=username
DB_PASSWORD=password
DB_HOST=127.0.0.1
DB_PORT=3306

/usr/bin/innobackupex \
--defaults-file=/etc/my.cnf \
--host=$DB_HOST \
--port=$DB_PORT \
--user=$DB_USER \
--password=$DB_PASSWORD \
--stream=tar $DB_BACKUP_PATH |gzip -> $DB_BACKUP_PATH/test-mysql-backups-$DATE.tar.gz

if [ $? -eq 0 ];then
	echo "测试环境MySQL(127.0.0.1)物理备份完成：$(date +%Y%m%d-%H%M%S) " >> /var/log/test-mysql-backups-$DATE.log
	sendDingDing "$(date '+%Y-%m-%d %H:%M') 测试环境MySQL(127.0.0.1)物理备份完成,并上传至测试用JuiceFS的backup-test-mysql目录中 "
else
	echo "备份失败：$(date +%Y%m%d-%H%M%S) " >> /var/log/test-mysql-backups-$DATE.log
	sendDingDing "$(date '+%Y-%m-%d %H:%M') 测试环境MySQL(127.0.0.1)物理备份失败，请检查备份脚本"
	exit 1

fi

#删除90天前的备份日志输出文件
find  /var/log -type f -name "test-mysql-backups-*.log" -ctime 90 | xargs rm -f

#删除90天前的备份
#find /data/mysql-backup-juicefs/backup-test-mysql -type f -ctime 90 |xargs rm -f

编写计划任务

crontal -l
#crontab下使用date命令需要转义%，例如： date +“%Y%m%d_%H:%M” 和 $(date +“%Y%m%d_%H:%M”)

#每周日 23点 备份测试环境数据库
0 23 * * 7  nohup /bin/bash /opt/backup-test-mysql/scripts/backup-test-mysql.sh >> /var/log/test-mysql-backups-$(date +\%Y\%m\%d-\%H\%M\%S).log 2>&1 &