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 &