运维：CentOS 7.6 下 MySQL 5.7 自动备份与恢复完整指南

mkdir -p /data/mysql/bak# 备份文件存放目录
mkdir -p /data/mysql/scripts        # 脚本存放目录

vim /data/scripts/mysql_backup.sh

#!/bin/bash
# ====================================================
# MySQL 5.7 自动备份脚本
# 适用于 CentOS 7.6 环境
# 功能：自动备份数据库、压缩、删除过期备份、记录日志
# ====================================================

# ---------- 基础配置（请按实际修改） ----------
MYSQL_USER="root"                    # 数据库用户名
MYSQL_PASSWORD="your_password"       # 数据库密码
MYSQL_HOST="localhost"               # 数据库主机地址

# 要备份的数据库名（多个数据库用空格分隔，如 "db1 db2 db3"）
# 留空表示备份所有数据库
DATABASE_NAME="your_database"        # 修改为实际数据库名

BACKUP_DIR="/data/mysql/bak"      # 备份存放路径
LOG_FILE="/var/log/mysql_backup.log" # 日志文件路径
RETENTION_DAYS=7                    # 保留备份天数

# ---------- 以下为脚本逻辑，无需修改 ----------
DATE=$(date +%Y%m%d_%H%M%S)

if [ ! -d "$BACKUP_DIR" ]; then
    mkdir -p "$BACKUP_DIR"
fi

echo "=========================================" >> "$LOG_FILE"
echo "备份开始时间：$(date '+%Y-%m-%d %H:%M:%S')" >> "$LOG_FILE"

if [ -n "$DATABASE_NAME" ]; then
    BACKUP_FILE="$BACKUP_DIR/${DATABASE_NAME}_${DATE}.sql.gz"
    echo "正在备份数据库：$DATABASE_NAME" >> "$LOG_FILE"
    mysqldump -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --set-gtid-purged=OFF \
        --databases "$DATABASE_NAME" | gzip > "$BACKUP_FILE"
else
    BACKUP_FILE="$BACKUP_DIR/all_databases_${DATE}.sql.gz"
    echo "正在备份所有数据库..." >> "$LOG_FILE"
    mysqldump -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --set-gtid-purged=OFF \
        --all-databases | gzip > "$BACKUP_FILE"
fi

if [ $? -eq 0 ]; then
    echo "备份成功！备份文件：$BACKUP_FILE" >> "$LOG_FILE"
    echo "备份文件大小：$(ls -lh "$BACKUP_FILE" | awk '{print $5}')" >> "$LOG_FILE"
else
    echo "备份失败！请检查 MySQL 连接和权限配置。" >> "$LOG_FILE"
    exit 1
fi

echo "正在清理 ${RETENTION_DAYS} 天前的旧备份文件..." >> "$LOG_FILE"
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -exec rm -f {} \;

DELETED_COUNT=$(find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +"$RETENTION_DAYS" 2>/dev/null | wc -l)
echo "已删除 $DELETED_COUNT 个过期备份文件" >> "$LOG_FILE"

FILE_COUNT=$(ls -1 "$BACKUP_DIR"/*.sql.gz 2>/dev/null | wc -l)
echo "当前备份目录中共有 $FILE_COUNT 个备份文件" >> "$LOG_FILE"

echo "备份结束时间：$(date '+%Y-%m-%d %H:%M:%S')" >> "$LOG_FILE"
echo "=========================================" >> "$LOG_FILE"

chmod +x /data/mysql/scripts/mysql_backup.sh

/data/scripts/mysql_backup.sh

ls -lh /data/backup/mysql/
cat /var/log/mysql_backup.log

find / -name mysqldump -type f 2>/dev/null

/usr/local/mysql/bin/mysqldump

export PATH=$PATH:/usr/local/mysql/bin

systemctl start crond
systemctl enable crond
systemctl status crond

crontab -e

0 3 * * * /bin/bash /data/mysql/scripts/mysql_backup.sh >> /var/log/mysql_backup_cron.log 2>&1

tail -f /var/log/cron | grep mysql_backup

zcat /data/backup/mysql/all_databases_20260506_030001.sql.gz | mysql -uroot -p

# 若目标数据库已存在
zcat /data/mysql/bak/your_database_20260506_030001.sql.gz | mysql -uroot -p your_database

# 若目标数据库不存在，先创建
mysql -uroot -p -e "CREATE DATABASE your_database;"
zcat backup.sql.gz | mysql -uroot -p your_database

zcat backup.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -uroot -p

zcat backup.sql.gz | mysql -uroot -p --init-command="SET FOREIGN_KEY_CHECKS=0;"

mysql -uroot -p -e "SET SESSION SQL_LOG_BIN=0;"
zcat backup.sql.gz | mysql -uroot -p

cat > ~/.my.cnf <<EOF
[client]
user=root
password=your_password
host=localhost
EOF
chmod 600 ~/.my.cnf

AVAIL=$(df -BG $BACKUP_DIR | awk 'NR==2 {print $4}' | sed 's/G//')
if [ $AVAIL -lt 5 ]; then
    echo "磁盘剩余空间不足5GB，停止备份" >> $LOG_FILE
    exit 1
fi

export PATH=/usr/bin:/bin:/usr/local/bin:/usr/local/mysql/bin

GRANT PROCESS, SELECT, LOCK TABLES, RELOAD, SHOW VIEW ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

rsync -avz --delete "$BACKUP_DIR/" backup_user@remote_host:/remote/backup/path/