CentOS7设置mysqldump自动备份

1、创建备份用户
CREATE USER backup@localhost IDENTIFIED BY 'PASSWORD';
GRANT SELECT,PROCESS,REPLICATION CLIENT,FLUSH_TABLES,EVENT ON *.* TO backup@localhost;
FLUSH PRIVILEGES;

2、配置文件/etc/my.cnf里添加备份用户信息
[mysqldump]
user=backup
password=PASSWORD

3、编辑备份脚本
#!/bin/sh
# File: /path/to/scripts/mysql.sh

# 备份目录
DST_DIR="/path/to/backup/dir"

# 备份保留天数
RESERVE_DAYS=7

# 时间格式
TIME=`date +%Y-%m-%d`

# 备份文件名
FILE='db.$TIME.gz'

# 备份并压缩
/usr/bin/mysqldump --all-databases --triggers --routines --events --single-transaction --source-data=2 | gzip > $DST_DIR/$FILE

# 修改备份文件权限
chmod 400 $DST_DIR/$FILE

# 删除旧备份
find $DST_DIR -name "db.*.gz" -type f -mtime +$RESERVE_DAYS -exec rm {} \; > /dev/null 2>&1

设置备份脚本权限
> chmod 100 /path/to/scripts/mysql.sh

4、设置每天凌晨5点自动备份
> crontab -e

0 5 * * * /path/to/scripts/mysql.sh &> /dev/null

> systemctl reload crond.service