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.sql"
ZIP_FILE="db.$TIME.tar.gz"

# 备份并压缩
/usr/bin/mysqldump --all-databases --triggers --routines --events --single-transaction > $DST_DIR/$FILE
cd $DST_DIR
tar zcf $ZIP_FILE $FILE
rm -f $FILE

# 修改权限
chmod 400 $DST_DIR/$ZIP_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