1. 创建数据库备份专用账号

CREATE USER ‘mysqldump’@‘localhost’ IDENTIFIED BY ‘oBAj@b8UiGGas#Ky’;
GRANT SELECT, LOCK TABLES, SHOW VIEW, PROCESS ON my_database.* TO ‘mysqldump’@‘localhost’;
FLUSH PRIVILEGES;

2. 编写backup_db.sh 脚本

#!/bin/bash

# 设置数据库参数
DB_NAME="my_database"
DB_USER="mysqldump" # 替换为你的MySQL用户名
DB_PASS="oBAj@b8UiGGas#Ky" # 替换为你的MySQL密码
BACKUP_DIR="/data/backup/mysql" # 替换为你的备份目录路径
DATE=$(date +%F) # 获取当前日期
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql" # 定义备份文件名

# 导出数据库（修改了默认socket路径，通过 --socket 参数指定，未修改则无需 socket 参数）
/data/mysql/bin/mysqldump -u $DB_USER -p$DB_PASS  --socket=/data/mysql/tmp/mysql.sock $DB_NAME > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Backup succeeded on $DATE" >> $BACKUP_DIR/backup_log.txt
else
    echo "Backup failed on $DATE" >> $BACKUP_DIR/backup_log.txt
fi

# 压缩备份文件
tar -czf $BACKUP_FILE.tar.gz $BACKUP_FILE

# 删除未压缩的备份文件
rm $BACKUP_FILE

3. 设置定时任务

执行命令 crontab -e
加入以下内容

#每周一凌晨备份数据库
0 0 * * 1 /data/backup/backup_db.sh