mysql定时备份-LINUX

前景：mysql部署在docker上，镜像名为mysqlmaster;

新建目录

mkdir -p /usr/local/mysql/mysqlbak/data
mkdir -p /usr/local/mysql/mysqlbak/scripts
mkdir -p /usr/local/mysql/mysqlbak/logs

创建备份脚本

cd /usr/local/mysql/mysqlbak/scripts
vim backup.sh

#!/bin/bash

#备份目录
BACKUP_ROOT=/usr/local/mysql/mysqlbak
BACKUP_FILEDIR=$BACKUP_ROOT/data

#当前日期
DATE=$(date +%Y%m%d)

######备份######

#查询所有数据库
#-uroot -p123456表示使用root账号执行命令，且root账号的密码为:root
DATABASES=$(docker exec -i mysqlmaster mysql -uroot -proot -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")
#循环数据库进行备份
for db in $DATABASES
do
echo
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
docker exec -i mysqlmaster mysqldump -uroot -proot --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo
done

echo "done"

注意：定时任务执行docker命令一定要注意docker exec -it 的用法，定时任务执行没有终端需要去掉t;

设置脚本的执行权限

chmod 777 backup.sh

将备份操作加入到定时任务(每天凌晨2点定时执行)

crontab -e

00 2 * * * /usr/local/mysql/mysqlbak/scripts/backup.sh > /usr/local/mysql/mysqlbak/logs/backup.log 2>&1

创建删除脚本(定时删除7天前的备份数据)

vi backup_clean.sh

#!/bin/bash
echo ----------CLEAN BEGIN----------
find /usr/local/mysql/mysqlbak/data -mtime +7 -name "*.gz" -exec rm -rf {} \;
echo ----------CLEAN COMPLETE----------

设置脚本的执行权限

chmod 777 backup_clean.sh

将删除操作加入到定时任务(每天凌晨1点定时执行)

crontab -e
00 1 * * * /usr/local/mysql/mysqlbak/scripts/backup_clean.sh > /usr/local/mysql/mysqlbak/logs/backup_full_clean.log 2>&1

查看定时任务

crontab -l

参考：https://www.cnblogs.com/blazeZzz/p/10881297.html