Mysql数据定时备份

一、备份准备&备份测试

1.备份目录准备

可自定义

#mysql专用目录
mkdir /mysql
#mysql备份目录
mkdir /mysql/backup
#mysql备份脚本
mkdir /mysql/backup/scripts
#mysql备份文件
mkdir /mysql/backup/files
#mysql备份日志
mkdir /mysql/backup/logs

2.备份脚本准备

(1)、脚本内容

vi /mysql/backup/scripts/backup_full.sh

#!/bin/bash

# cront进程不会自动加载用户目录下的.profile文件，所以需要加source /etc/profile使环境变量生效
source /etc/profile
 
#备份目录
BACKUP_ROOT=/mysql/backup
BACKUP_FILEDIR=$BACKUP_ROOT/files
BACKUP_LOGDIR=$BACKUP_ROOT/logs
 
#当前日期
DATE=$(date +%Y%m%d)
 
######备份######
 
#查询所有数据库
#-uroot -p123456表示使用root账号执行命令，且root账号的密码为:123456
DATABASES=$(mysql -uroot -p123456 -e "show databases" | grep -Ev "Database|sys|information_schema")
echo $DATABASES
#循环数据库进行备份
for db in $DATABASES
do
echo
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
mysqldump -uroot -pRoot@1024 --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"

(2)、如果是Docker启动的MySQL

#!/bin/bash

# 进入名字为mysql_good 的docker容器
docker exec -i 容器名称 /bin/bash <<'EOF'
# 在docker内建立对应日期的文件夹
mkdir -p /usr/good/data/mysql/$(date +%Y%m%d)
# 备份文件到指定文件夹
mysqldump -u 用户名 密码 数据库名 > /usr/good/data/mysql/$(date +%Y%m%d)/名称_$(date +%Y%m%d_%H%M%S).sql
# 退出docker容器
exit
EOF

# 进入宿主机的备份目录
cd /home/opuser/back_mysql/files/
# 将容器内的备份文件拷贝到本地
docker cp yishu_mysql:/usr/good/data/mysql/$(date +%Y%m%d) /mysql/backup/files

# 备份之后，我们发现容器里还存在导出的数据库，这样当前主机就有两本同样导出的数据库，可以删除容器里的数据库
docker exec -i 容器名称 /bin/bash <<'EOF'
#删除容器中导出的数据库
rm -rf /usr/good/data/mysql/$(date +%Y%m%d)
#退出docker容器
exit
EOF

(3)、脚本执行可能报错，解决方法

mysqldump: Error 1227: Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation when dumping tablelog_statusat row: 0，这是因为没有导出的权限

# 解决方法（进入mysql数据库）,执行命令
## 授权
grant BACKUP_ADMIN on *.* to 'root'@'%';
## 刷新
FLUSH PRIVILEGES;

二、定时任务

1.安装crontab

yum install -y crontab

2.创建定时备份任务

第一步骤写好的脚本backup_full.sh

#添加定时任务
crontab -e
#每天凌晨3点执行
00 3 * * * sh /mysql/backup/scripts/backup_full.sh
#查看定时任务
crontab -l

定时时间格式

• 每五分钟执行 */5 * * * *
• 每小时执行 0 * * * *
• 每天执行 0 0 * * *
• 每周执行 0 0 * * 0
• 每月执行 0 0 1 * *
• 每年执行 0 0 1 1 *
  
  

3.定时清理7天以前的备份

(1)、创建文件清理脚本

# 创建脚本文件
vi /mysql/backup/scripts/backup_full_clean.sh
 
# 写入以下内容
#!/bin/bash
find /mysql/backup/files -mtime +7 -name "*.gz" -exec rm -rf {} \;

(2)、创建定时清理任务

#添加定时任务
crontab -e
#每天凌晨1点执行
00 1 * * * sh /mysql/backup/scripts/backup_full_clean.sh
#查看定时任务
crontab -l