mysql 自动备份脚本

编写脚本 /usr/local/backup/mysql_backup.sh
[root@localhost backup]# cat mysql_backup.sh 
#!/bin/bash

# 配置参数
DB_USER="root"               # MySQL 备份用户
DB_PASS="root"             # MySQL 备份用户密码
BACKUP_DIR="/usr/local/backup/mysql" # 修改为你的备份目录
DATE=$(date +"%Y%m%d_%H%M%S")       # 当前时间戳
MYSQL=/usr/local/mysql/bin/mysql    # 修改为你的 MySQL 可执行文件路径
MYSQLDUMP=/usr/local/mysql/bin/mysqldump # 修改为你的 mysqldump 可执行文件路径
GZIP=/bin/gzip                      # gzip 压缩工具路径

# 获取所有数据库名称
DATABASES=$($MYSQL -u$DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql)")

# 创建当天的备份目录
DAY_DIR="$BACKUP_DIR/$(date +"%Y-%m-%d")"
mkdir -p $DAY_DIR

# 备份每个数据库
for DB in $DATABASES; do
    FILE_NAME="$DAY_DIR/$DB-$DATE.sql.gz"
    $MYSQLDUMP --single-transaction --routines --triggers --events -u$DB_USER -p$DB_PASS $DB | $GZIP > $FILE_NAME
    echo "Backup of database $DB completed: $FILE_NAME"
done

# 删除一个月前的旧备份
find $BACKUP_DIR -type d -mtime +30 -exec rm -rf {} \;

echo "MySQL backup completed successfully."


定时任务设置
[root@localhost 2025-04-22]# crontab -l
0 0 * * 0 /usr/local/backup/mysql_backup.sh >> /usr/local/backup/mysql_backup.log 2>&1