RockyLinux9 MySQL 服务器 自动备份脚本

脚本

cd /usr/local/bin

touch /usr/local/bin/mysql_backup.sh

#!/bin/bash

# MySQL 备份脚本（优化目录结构版）
# 保存位置：/usr/local/bin/mysql_backup.sh

# 配置信息
DB_USER="root"         
DB_PASSWORD="aimsppro12!@"     
BACKUP_BASE_DIR="/var/backups/mysql" # 备份根目录
LOG_FILE="/var/log/mysql_backup.log"
DATE=$(date +"%Y%m%d_%H_%M_%S")       # 仅保留日期部分
BACKUP_DIR="$BACKUP_BASE_DIR/$DATE" # 按日期生成子目录
RETENTION=7 

# 创建备份目录
mkdir -p $BACKUP_DIR
touch $LOG_FILE

# 记录日志
log_message() {
    echo "[$(date)] $1" >> $LOG_FILE
}

log_message "开始备份 MySQL 数据库，存储目录：$BACKUP_DIR"

# 获取所有数据库列表
databases=$(mysql -u$DB_USER -p$DB_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

# 备份每个数据库
for db in $databases; do
    log_message "备份数据库: $db"
    backup_file="$BACKUP_DIR/${db}.sql.gz"
    
    mysqldump -u$DB_USER -p$DB_PASSWORD --databases $db | gzip > $backup_file
    
    if [ $? -eq 0 ]; then
        log_message "数据库 $db 备份成功: $backup_file"
    else
        log_message "错误: 数据库 $db 备份失败!"
        if [ -f $backup_file ]; then
            rm $backup_file
        fi
    fi
done

# 删除旧备份（按日期目录删除）
log_message "删除超过 $RETENTION 天的备份目录"
find $BACKUP_BASE_DIR -type d -name "[0-9]\{8\}" -m +$RETENTION -exec rm -rf {} \;

log_message "MySQL 备份完成!"

脚本权限

chmod +x /usr/local/bin/mysql_backup.sh

创建 systemd 服务

touch /etc/systemd/system/mysql-backup.service

[Unit]
Description=MySQL Backup Service
After=network.target mysql.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql_backup.sh

创建一个定时器文件

touch /etc/systemd/system/mysql-backup.timer

[Unit]
Description=Run MySQL Backup at specific times

[Timer]
OnCalendar=*-*-* 05:00:00
OnCalendar=*-*-* 11:00:00
OnCalendar=*-*-* 15:05:00
OnCalendar=*-*-* 17:00:00
OnCalendar=*-*-* 22:00:00
Persistent=true

[Install]
WantedBy=timers.target

启动并启用定时器

# 重新加载配置定时器
sudo systemctl daemon-reload
# 启动定时器
sudo systemctl start mysql-backup.timer
# 重启定时器
sudo systemctl restart mysql-backup.timer
# 停止定时器
sudo systemctl stop mysql-backup.timer
# 开机自启
sudo systemctl enable mysql-backup.timer
# 关闭开机自启
sudo systemctl disable mysql-backup.timer

验证定时任务

systemctl list-timers | grep mysql-backup

手动运行一次备份测试

systemctl start mysql-backup.service

检查备份文件是否生成

ls -l /var/backups/mysql

检查日志文件

cat /var/log/mysql_backup.log