CentOS7设置mysqldump自动备份

1、创建备份用户

CREATE USER backup@localhost IDENTIFIED BY 'PASSWORD';
GRANT SELECT,LOCK TABLES,PROCESS ON *.* TO backup@localhost;
FLUSH PRIVILEGES;

2、编辑备份脚本

#!/bin/sh
# File: /path/to/scripts/mysql.sh

# 数据库相关参数
DB_HOST="localhost"
DB_USER="backup"
DB_PASS="PASSWORD"

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

# 备份保留天数
RESERVE_DAYS=7

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

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

# 备份并压缩
/usr/bin/mysqldump --opt -h$DB_HOST -u$DB_USER -p$DB_PASS -A | gzip > $BACKUP_DIR/$FILE

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

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

设置备份脚本权限

> chmod 100 /path/to/scripts/mysql.sh

3、设置每天凌晨5点自动备份

> crontab -e

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

> systemctl reload crond.service