MySQL 备份与恢复 - MySQL 教程

备份概述

数据备份是数据库管理中最重要的任务之一。有效的备份策略可以保护数据免受硬件故障、人为错误、恶意攻击等威胁，确保业务连续性。

🔄 逻辑备份

导出 SQL 语句，可读性好，跨平台兼容，适合小到中型数据库

💿 物理备份

直接复制数据文件，速度快，适合大型数据库

📊 增量备份

只备份变更的数据，节省存储空间和时间

🔄 差异备份

备份自上次完整备份以来的所有变更

                    备份策略的核心原则：
                    3-2-1 原则：3份副本，2种不同媒介，1份异地存储
定期测试：定期验证备份的完整性和可恢复性
自动化：使用自动化工具减少人为错误
监控告警：建立备份监控和失败告警机制
文档记录：详细记录备份和恢复流程

                

备份类型	备份速度	恢复速度	存储空间	跨平台性	适用场景
逻辑备份	慢	慢	中等	优秀	小到中型数据库
物理备份	快	快	大	一般	大型数据库
增量备份	快	中等	小	中等	频繁变更的数据库
差异备份	中等	中等	中等	中等	平衡方案

逻辑备份

准备测试环境

-- 创建测试数据库和表
CREATE DATABASE backup_demo;
USE backup_demo;

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 创建产品表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO users (username, email, password_hash) VALUES
('alice', 'alice@example.com', 'hash1'),
('bob', 'bob@example.com', 'hash2'),
('charlie', 'charlie@example.com', 'hash3'),
('diana', 'diana@example.com', 'hash4'),
('eve', 'eve@example.com', 'hash5');

INSERT INTO products (name, description, price, stock_quantity, category) VALUES
('Laptop', 'High-performance laptop', 999.99, 50, 'Electronics'),
('Mouse', 'Wireless mouse', 29.99, 200, 'Electronics'),
('Keyboard', 'Mechanical keyboard', 79.99, 100, 'Electronics'),
('Monitor', '24-inch monitor', 199.99, 75, 'Electronics'),
('Headphones', 'Noise-cancelling headphones', 149.99, 120, 'Electronics');

INSERT INTO orders (user_id, order_number, total_amount, status) VALUES
(1, 'ORD-001', 999.99, 'delivered'),
(2, 'ORD-002', 109.98, 'shipped'),
(3, 'ORD-003', 29.99, 'paid'),
(1, 'ORD-004', 349.98, 'pending'),
(4, 'ORD-005', 149.99, 'delivered');

-- 创建视图
CREATE VIEW order_summary AS
SELECT 
    u.username,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
    SELECT 
        o.id,
        o.order_number,
        o.total_amount,
        o.status,
        o.created_at
    FROM orders o
    WHERE o.user_id = user_id
    ORDER BY o.created_at DESC;
END //
DELIMITER ;

-- 创建触发器
DELIMITER //
CREATE TRIGGER update_user_timestamp
    BEFORE UPDATE ON users
    FOR EACH ROW
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END //
DELIMITER ;

SELECT 'Test database and data created successfully' AS message;
                

使用 mysqldump 进行逻辑备份

# 1. 完整数据库备份
mysqldump -u root -p backup_demo > backup_demo_full.sql

# 2. 备份特定表
mysqldump -u root -p backup_demo users orders > backup_demo_tables.sql

# 3. 只备份表结构（不包含数据）
mysqldump -u root -p --no-data backup_demo > backup_demo_structure.sql

# 4. 只备份数据（不包含表结构）
mysqldump -u root -p --no-create-info backup_demo > backup_demo_data.sql

# 5. 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql

# 6. 备份时包含存储过程、函数、触发器
mysqldump -u root -p --routines --triggers backup_demo > backup_demo_complete.sql

# 7. 压缩备份
mysqldump -u root -p backup_demo | gzip > backup_demo.sql.gz

# 8. 备份时添加时间戳
mysqldump -u root -p backup_demo > backup_demo_$(date +%Y%m%d_%H%M%S).sql

# 9. 备份大数据库（分块处理）
mysqldump -u root -p --single-transaction --routines --triggers backup_demo > backup_demo_large.sql

# 10. 远程备份
mysqldump -h remote_host -u username -p database_name > remote_backup.sql

# 11. 备份时排除特定表
mysqldump -u root -p backup_demo --ignore-table=backup_demo.temp_table > backup_demo_exclude.sql

# 12. 创建一致性备份（InnoDB）
mysqldump -u root -p --single-transaction --flush-logs --master-data=2 backup_demo > backup_demo_consistent.sql
                

高级 mysqldump 选项

# 重要的 mysqldump 参数说明

# --single-transaction: 确保 InnoDB 表的一致性备份
# --flush-logs: 备份前刷新日志
# --master-data=2: 记录二进制日志位置（用于主从复制）
# --routines: 包含存储过程和函数
# --triggers: 包含触发器
# --events: 包含事件调度器
# --hex-blob: 使用十六进制格式导出二进制数据
# --complete-insert: 生成完整的 INSERT 语句
# --extended-insert: 使用多行 INSERT 语法（默认启用）
# --no-autocommit: 禁用自动提交
# --set-gtid-purged=OFF: 禁用 GTID 相关输出

# 生产环境推荐的备份命令
mysqldump -u backup_user -p \
  --single-transaction \
  --flush-logs \
  --master-data=2 \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --complete-insert \
  --set-gtid-purged=OFF \
  backup_demo > backup_demo_production.sql

# 备份脚本示例
#!/bin/bash
# backup_script.sh

DB_NAME="backup_demo"
DB_USER="root"
DB_PASS="your_password"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql"

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

# 执行备份
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  $DB_NAME > $BACKUP_FILE

# 压缩备份文件
gzip $BACKUP_FILE

# 删除7天前的备份
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

echo "Backup completed: ${BACKUP_FILE}.gz"
                

逻辑备份的恢复

-- 1. 恢复完整数据库
-- 首先删除现有数据库（谨慎操作）
DROP DATABASE IF EXISTS backup_demo;
CREATE DATABASE backup_demo;

-- 从备份文件恢复
-- mysql -u root -p backup_demo < backup_demo_full.sql

-- 2. 恢复到新数据库
CREATE DATABASE backup_demo_restored;
-- mysql -u root -p backup_demo_restored < backup_demo_full.sql

-- 3. 恢复特定表
-- mysql -u root -p backup_demo < backup_demo_tables.sql

-- 4. 从压缩备份恢复
-- gunzip < backup_demo.sql.gz | mysql -u root -p backup_demo

-- 5. 恢复时显示进度
-- pv backup_demo_full.sql | mysql -u root -p backup_demo

-- 6. 部分恢复（只恢复特定表的数据）
-- 从备份文件中提取特定表的 INSERT 语句
-- grep "INSERT INTO users" backup_demo_full.sql | mysql -u root -p backup_demo

-- 验证恢复结果
USE backup_demo;
SELECT 'Users table:' as info;
SELECT COUNT(*) as user_count FROM users;

SELECT 'Orders table:' as info;
SELECT COUNT(*) as order_count FROM orders;

SELECT 'Products table:' as info;
SELECT COUNT(*) as product_count FROM products;

-- 检查视图是否正常
SELECT 'Order summary view:' as info;
SELECT * FROM order_summary LIMIT 3;

-- 检查存储过程是否存在
SHOW PROCEDURE STATUS WHERE Db = 'backup_demo';

-- 检查触发器是否存在
SHOW TRIGGERS FROM backup_demo;
                

物理备份

使用文件系统备份

# 1. 停机备份（最安全但需要停机）
# 停止 MySQL 服务
sudo systemctl stop mysql
# 或者
sudo service mysql stop

# 复制数据目录
sudo cp -R /var/lib/mysql /backup/mysql_physical_$(date +%Y%m%d_%H%M%S)

# 启动 MySQL 服务
sudo systemctl start mysql

# 2. 热备份（使用 rsync，风险较高）
# 注意：这种方法可能导致数据不一致，不推荐用于生产环境
rsync -av /var/lib/mysql/ /backup/mysql_hot_backup/

# 3. 使用 LVM 快照备份（推荐）
# 创建 LVM 快照
sudo lvcreate -L 10G -s -n mysql_snapshot /dev/vg0/mysql_lv

# 挂载快照
sudo mkdir /mnt/mysql_snapshot
sudo mount /dev/vg0/mysql_snapshot /mnt/mysql_snapshot

# 从快照复制数据
sudo cp -R /mnt/mysql_snapshot /backup/mysql_lvm_$(date +%Y%m%d_%H%M%S)

# 卸载并删除快照
sudo umount /mnt/mysql_snapshot
sudo lvremove -f /dev/vg0/mysql_snapshot

# 4. 查看 MySQL 数据目录位置
-- 在 MySQL 中执行
SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'innodb_data_home_dir';
SHOW VARIABLES LIKE 'log_bin_basename';
                

使用 MySQL Enterprise Backup (MEB)

# MySQL Enterprise Backup 是 MySQL 企业版的官方物理备份工具
# 注意：这是商业版本的功能，社区版不包含

# 1. 完整备份
mysqlbackup --user=backup_user --password=password \
  --backup-dir=/backup/meb_full_$(date +%Y%m%d_%H%M%S) \
  backup-and-apply-log

# 2. 增量备份
mysqlbackup --user=backup_user --password=password \
  --backup-dir=/backup/meb_incremental_$(date +%Y%m%d_%H%M%S) \
  --incremental \
  --incremental-base=dir:/backup/meb_full_20240120_100000 \
  backup

# 3. 压缩备份
mysqlbackup --user=backup_user --password=password \
  --backup-dir=/backup/meb_compressed_$(date +%Y%m%d_%H%M%S) \
  --compress \
  backup-and-apply-log

# 4. 恢复备份
mysqlbackup --backup-dir=/backup/meb_full_20240120_100000 \
  copy-back

# 注意：社区版用户可以考虑使用 Percona XtraBackup 作为替代
                

使用 Percona XtraBackup

# Percona XtraBackup 是开源的 MySQL 物理备份工具
# 支持热备份，不需要停机

# 安装 Percona XtraBackup
# Ubuntu/Debian:
# sudo apt-get install percona-xtrabackup-80

# CentOS/RHEL:
# sudo yum install percona-xtrabackup-80

# 1. 完整备份
xtrabackup --user=backup_user --password=password \
  --backup \
  --target-dir=/backup/xtrabackup_full_$(date +%Y%m%d_%H%M%S)

# 2. 准备备份（应用日志）
xtrabackup --prepare \
  --target-dir=/backup/xtrabackup_full_20240120_100000

# 3. 增量备份
# 首先需要一个完整备份作为基础
xtrabackup --user=backup_user --password=password \
  --backup \
  --target-dir=/backup/xtrabackup_base

# 第一次增量备份
xtrabackup --user=backup_user --password=password \
  --backup \
  --target-dir=/backup/xtrabackup_inc1 \
  --incremental-basedir=/backup/xtrabackup_base

# 第二次增量备份
xtrabackup --user=backup_user --password=password \
  --backup \
  --target-dir=/backup/xtrabackup_inc2 \
  --incremental-basedir=/backup/xtrabackup_inc1

# 4. 准备增量备份
# 准备基础备份
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/xtrabackup_base

# 应用第一个增量备份
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/xtrabackup_base \
  --incremental-dir=/backup/xtrabackup_inc1

# 应用第二个增量备份
xtrabackup --prepare \
  --target-dir=/backup/xtrabackup_base \
  --incremental-dir=/backup/xtrabackup_inc2

# 5. 恢复备份
# 停止 MySQL 服务
sudo systemctl stop mysql

# 清空数据目录
sudo rm -rf /var/lib/mysql/*

# 恢复数据
xtrabackup --copy-back \
  --target-dir=/backup/xtrabackup_base

# 修改权限
sudo chown -R mysql:mysql /var/lib/mysql

# 启动 MySQL 服务
sudo systemctl start mysql

# 6. 压缩备份
xtrabackup --user=backup_user --password=password \
  --backup \
  --compress \
  --target-dir=/backup/xtrabackup_compressed_$(date +%Y%m%d_%H%M%S)

# 解压备份
xtrabackup --decompress \
  --target-dir=/backup/xtrabackup_compressed_20240120_100000

# 删除压缩文件
find /backup/xtrabackup_compressed_20240120_100000 -name "*.qp" -delete
                

二进制日志备份

配置二进制日志

-- 查看二进制日志配置
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog%';
SHOW VARIABLES LIKE 'expire_logs_days';

-- 查看二进制日志文件
SHOW BINARY LOGS;

-- 查看当前二进制日志位置
SHOW MASTER STATUS;

-- 查看二进制日志内容
-- SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 配置二进制日志（在 my.cnf 中）
-- [mysqld]
-- log-bin=mysql-bin
-- binlog-format=ROW
-- expire-logs-days=7
-- max-binlog-size=100M
-- sync-binlog=1

-- 手动刷新二进制日志
FLUSH BINARY LOGS;

-- 清理旧的二进制日志
-- PURGE BINARY LOGS TO 'mysql-bin.000010';
-- PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
                

使用二进制日志进行点时间恢复

# 1. 模拟数据变更和误操作
# 首先记录当前时间和二进制日志位置
mysql -u root -p -e "SHOW MASTER STATUS;"
echo "Current time: $(date)"

# 在 MySQL 中执行一些操作
mysql -u root -p backup_demo << EOF
-- 插入一些新数据
INSERT INTO users (username, email, password_hash) VALUES
('frank', 'frank@example.com', 'hash6'),
('grace', 'grace@example.com', 'hash7');

-- 更新一些数据
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

-- 记录这个时间点
SELECT NOW() as 'Good state timestamp';
EOF

# 等待一段时间，然后模拟误操作
sleep 5

mysql -u root -p backup_demo << EOF
-- 误删除数据（模拟事故）
DELETE FROM users WHERE username IN ('frank', 'grace');
DROP TABLE products;

SELECT NOW() as 'Accident timestamp';
EOF

# 2. 点时间恢复过程
# 首先从完整备份恢复
mysql -u root -p << EOF
DROP DATABASE IF EXISTS backup_demo;
CREATE DATABASE backup_demo;
EOF

# 恢复完整备份
mysql -u root -p backup_demo < backup_demo_full.sql

# 3. 使用 mysqlbinlog 恢复到特定时间点
# 找到需要恢复的二进制日志文件
mysql -u root -p -e "SHOW BINARY LOGS;"

# 查看二进制日志内容，找到误操作的时间点
mysqlbinlog /var/lib/mysql/mysql-bin.000001 | grep -A 5 -B 5 "DELETE FROM users"

# 恢复到误操作之前的时间点
# 假设误操作发生在 2024-01-20 14:30:00
mysqlbinlog --stop-datetime="2024-01-20 14:30:00" \
  /var/lib/mysql/mysql-bin.000001 | mysql -u root -p backup_demo

# 4. 基于位置的恢复（更精确）
# 查看特定位置的日志
mysqlbinlog --start-position=1000 --stop-position=2000 \
  /var/lib/mysql/mysql-bin.000001

# 恢复到特定位置
mysqlbinlog --stop-position=1500 \
  /var/lib/mysql/mysql-bin.000001 | mysql -u root -p backup_demo

# 5. 跳过特定事务
# 如果需要跳过某个有问题的事务
mysqlbinlog --start-position=1000 --stop-position=1400 \
  /var/lib/mysql/mysql-bin.000001 | mysql -u root -p backup_demo

mysqlbinlog --start-position=1600 \
  /var/lib/mysql/mysql-bin.000001 | mysql -u root -p backup_demo
                

二进制日志管理脚本

#!/bin/bash
# binlog_backup.sh - 二进制日志备份脚本

MYSQL_USER="root"
MYSQL_PASS="your_password"
BACKUP_DIR="/backup/binlogs"
DATE=$(date +%Y%m%d_%H%M%S)

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

# 获取当前二进制日志文件列表
mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW BINARY LOGS;" | \
  awk 'NR>1 {print $1}' > /tmp/binlog_list.txt

# 刷新二进制日志，生成新的日志文件
mysql -u $MYSQL_USER -p$MYSQL_PASS -e "FLUSH BINARY LOGS;"

# 备份除最新文件外的所有二进制日志
while read binlog_file; do
    if [ "$binlog_file" != "$(tail -1 /tmp/binlog_list.txt)" ]; then
        cp /var/lib/mysql/$binlog_file $BACKUP_DIR/
        echo "Backed up: $binlog_file"
    fi
done < /tmp/binlog_list.txt

# 压缩备份的二进制日志
cd $BACKUP_DIR
tar -czf binlogs_$DATE.tar.gz mysql-bin.*
rm -f mysql-bin.*

# 清理旧的备份（保留30天）
find $BACKUP_DIR -name "binlogs_*.tar.gz" -mtime +30 -delete

echo "Binary log backup completed: binlogs_$DATE.tar.gz"

# 清理 MySQL 中的旧二进制日志（保留7天）
mysql -u $MYSQL_USER -p$MYSQL_PASS -e \
  "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

rm -f /tmp/binlog_list.txt
                

备份策略和自动化

备份策略设计

📅 每日策略

增量备份 + 二进制日志备份

📊 每周策略

差异备份 + 备份验证

🗓️ 每月策略

完整备份 + 异地存储

🔄 实时策略

主从复制 + 读写分离

时间	备份类型	备份方法	保留期限	存储位置
每天 02:00	增量备份	XtraBackup	7天	本地存储
每天 03:00	二进制日志	mysqlbinlog	30天	本地存储
每周日 01:00	完整备份	mysqldump	4周	本地 + 远程
每月1日 00:00	完整备份	XtraBackup	12个月	异地存储
实时	主从复制	MySQL Replication	持续	从服务器

自动化备份脚本

#!/bin/bash
# comprehensive_backup.sh - 综合备份脚本

# 配置参数
MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
MYSQL_HOST="localhost"
BACKUP_BASE_DIR="/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"
EMAIL_ALERT="admin@example.com"
RETENTION_DAYS=30

# 创建日期标识
DATE=$(date +%Y%m%d_%H%M%S)
DAY_OF_WEEK=$(date +%u)  # 1=Monday, 7=Sunday
DAY_OF_MONTH=$(date +%d)

# 日志函数
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a $LOG_FILE
}

# 错误处理函数
handle_error() {
    log_message "ERROR: $1"
    echo "Backup failed: $1" | mail -s "MySQL Backup Failed" $EMAIL_ALERT
    exit 1
}

# 检查 MySQL 连接
check_mysql_connection() {
    mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "SELECT 1;" > /dev/null 2>&1
    if [ $? -ne 0 ]; then
        handle_error "Cannot connect to MySQL server"
    fi
}

# 创建备份目录
create_backup_dirs() {
    mkdir -p $BACKUP_BASE_DIR/{daily,weekly,monthly,binlogs}
    if [ $? -ne 0 ]; then
        handle_error "Cannot create backup directories"
    fi
}

# 每日增量备份
daily_backup() {
    log_message "Starting daily incremental backup"
    
    DAILY_DIR="$BACKUP_BASE_DIR/daily/$DATE"
    mkdir -p $DAILY_DIR
    
    # 使用 XtraBackup 进行增量备份
    if [ -d "$BACKUP_BASE_DIR/weekly/latest" ]; then
        BASE_DIR="$BACKUP_BASE_DIR/weekly/latest"
    else
        # 如果没有周备份，创建完整备份
        BASE_DIR="$DAILY_DIR"
        xtrabackup --user=$MYSQL_USER --password=$MYSQL_PASS \
            --backup --target-dir=$BASE_DIR
    fi
    
    if [ "$BASE_DIR" != "$DAILY_DIR" ]; then
        xtrabackup --user=$MYSQL_USER --password=$MYSQL_PASS \
            --backup --target-dir=$DAILY_DIR \
            --incremental-basedir=$BASE_DIR
    fi
    
    if [ $? -eq 0 ]; then
        log_message "Daily backup completed successfully"
    else
        handle_error "Daily backup failed"
    fi
}

# 每周完整备份
weekly_backup() {
    log_message "Starting weekly full backup"
    
    WEEKLY_DIR="$BACKUP_BASE_DIR/weekly/$DATE"
    mkdir -p $WEEKLY_DIR
    
    # 使用 mysqldump 进行逻辑备份
    mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS \
        --single-transaction --routines --triggers --events \
        --all-databases | gzip > $WEEKLY_DIR/full_backup.sql.gz
    
    if [ $? -eq 0 ]; then
        # 创建符号链接指向最新备份
        ln -sfn $WEEKLY_DIR $BACKUP_BASE_DIR/weekly/latest
        log_message "Weekly backup completed successfully"
    else
        handle_error "Weekly backup failed"
    fi
}

# 每月归档备份
monthly_backup() {
    log_message "Starting monthly archive backup"
    
    MONTHLY_DIR="$BACKUP_BASE_DIR/monthly/$DATE"
    mkdir -p $MONTHLY_DIR
    
    # 使用 XtraBackup 进行物理备份
    xtrabackup --user=$MYSQL_USER --password=$MYSQL_PASS \
        --backup --compress --target-dir=$MONTHLY_DIR
    
    if [ $? -eq 0 ]; then
        log_message "Monthly backup completed successfully"
        
        # 上传到远程存储（示例：rsync 到远程服务器）
        # rsync -av $MONTHLY_DIR/ backup-server:/remote/backup/mysql/
    else
        handle_error "Monthly backup failed"
    fi
}

# 二进制日志备份
binlog_backup() {
    log_message "Starting binary log backup"
    
    BINLOG_DIR="$BACKUP_BASE_DIR/binlogs/$DATE"
    mkdir -p $BINLOG_DIR
    
    # 获取二进制日志文件列表
    mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS \
        -e "SHOW BINARY LOGS;" | awk 'NR>1 {print $1}' > /tmp/binlog_list.txt
    
    # 刷新日志
    mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "FLUSH BINARY LOGS;"
    
    # 备份二进制日志文件
    while read binlog_file; do
        if [ -f "/var/lib/mysql/$binlog_file" ]; then
            cp /var/lib/mysql/$binlog_file $BINLOG_DIR/
        fi
    done < /tmp/binlog_list.txt
    
    # 压缩备份
    cd $BINLOG_DIR && tar -czf ../binlogs_$DATE.tar.gz .
    rm -rf $BINLOG_DIR
    
    log_message "Binary log backup completed"
    rm -f /tmp/binlog_list.txt
}

# 清理旧备份
cleanup_old_backups() {
    log_message "Cleaning up old backups"
    
    # 清理每日备份（保留7天）
    find $BACKUP_BASE_DIR/daily -type d -mtime +7 -exec rm -rf {} +
    
    # 清理每周备份（保留4周）
    find $BACKUP_BASE_DIR/weekly -type d -mtime +28 -exec rm -rf {} +
    
    # 清理二进制日志备份（保留30天）
    find $BACKUP_BASE_DIR/binlogs -name "*.tar.gz" -mtime +$RETENTION_DAYS -delete
    
    log_message "Cleanup completed"
}

# 验证备份
verify_backup() {
    log_message "Verifying backup integrity"
    
    # 检查最新的备份文件
    LATEST_BACKUP=$(find $BACKUP_BASE_DIR -name "*.sql.gz" -o -name "*.tar.gz" | \
                   sort -r | head -1)
    
    if [ -n "$LATEST_BACKUP" ]; then
        # 检查文件完整性
        if file $LATEST_BACKUP | grep -q "gzip compressed"; then
            gzip -t $LATEST_BACKUP
            if [ $? -eq 0 ]; then
                log_message "Backup verification passed: $LATEST_BACKUP"
            else
                handle_error "Backup verification failed: $LATEST_BACKUP"
            fi
        fi
    fi
}

# 发送成功通知
send_success_notification() {
    BACKUP_SIZE=$(du -sh $BACKUP_BASE_DIR | cut -f1)
    echo "MySQL backup completed successfully.
Total backup size: $BACKUP_SIZE
Date: $(date)" | \
        mail -s "MySQL Backup Success" $EMAIL_ALERT
}

# 主执行流程
main() {
    log_message "Starting MySQL backup process"
    
    check_mysql_connection
    create_backup_dirs
    
    # 根据日期决定备份类型
    if [ $DAY_OF_MONTH -eq 1 ]; then
        # 每月1日执行月备份
        monthly_backup
    elif [ $DAY_OF_WEEK -eq 7 ]; then
        # 每周日执行周备份
        weekly_backup
    else
        # 其他时间执行日备份
        daily_backup
    fi
    
    # 每天都执行二进制日志备份
    binlog_backup
    
    # 清理旧备份
    cleanup_old_backups
    
    # 验证备份
    verify_backup
    
    # 发送成功通知
    send_success_notification
    
    log_message "MySQL backup process completed successfully"
}

# 执行主函数
main
                

使用 Crontab 调度备份

# 编辑 crontab
crontab -e

# 添加以下备份任务

# 每天凌晨2点执行备份
0 2 * * * /path/to/comprehensive_backup.sh >> /var/log/mysql_backup_cron.log 2>&1

# 每周日凌晨1点执行完整备份
0 1 * * 0 /path/to/weekly_full_backup.sh >> /var/log/mysql_backup_cron.log 2>&1

# 每月1日凌晨0点执行月度备份
0 0 1 * * /path/to/monthly_backup.sh >> /var/log/mysql_backup_cron.log 2>&1

# 每小时备份二进制日志
0 * * * * /path/to/binlog_backup.sh >> /var/log/mysql_backup_cron.log 2>&1

# 查看 crontab 任务
crontab -l

# 监控 cron 日志
tail -f /var/log/mysql_backup_cron.log

# 备份脚本权限设置
chmod +x /path/to/comprehensive_backup.sh
chmod +x /path/to/weekly_full_backup.sh
chmod +x /path/to/monthly_backup.sh
chmod +x /path/to/binlog_backup.sh

# 创建专用的备份用户
mysql -u root -p << EOF
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, SHOW DATABASES, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EOF
                

灾难恢复

灾难恢复计划

🚨 灾难恢复步骤

评估损失：确定数据丢失的范围和原因
停止服务：防止进一步的数据损坏
准备环境：准备恢复所需的硬件和软件环境
恢复数据：按照恢复计划执行数据恢复
验证数据：检查恢复数据的完整性和一致性
测试应用：验证应用程序功能正常
恢复服务：重新启动生产服务
监控系统：密切监控系统运行状态
总结经验：分析事故原因，改进备份策略

完整的灾难恢复示例

#!/bin/bash
# disaster_recovery.sh - 灾难恢复脚本

# 配置参数
MYSQL_USER="root"
MYSQL_PASS="recovery_password"
BACKUP_DIR="/backup/mysql"
RECOVERY_LOG="/var/log/mysql_recovery.log"
TARGET_TIME="2024-01-20 14:30:00"  # 恢复到的目标时间

# 日志函数
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a $RECOVERY_LOG
}

# 第1步：停止 MySQL 服务
stop_mysql() {
    log_message "Stopping MySQL service"
    sudo systemctl stop mysql
    if [ $? -eq 0 ]; then
        log_message "MySQL service stopped successfully"
    else
        log_message "Failed to stop MySQL service"
        exit 1
    fi
}

# 第2步：备份当前损坏的数据（如果可能）
backup_corrupted_data() {
    log_message "Backing up corrupted data for analysis"
    CORRUPTED_BACKUP_DIR="/backup/corrupted_$(date +%Y%m%d_%H%M%S)"
    mkdir -p $CORRUPTED_BACKUP_DIR
    
    if [ -d "/var/lib/mysql" ]; then
        cp -R /var/lib/mysql $CORRUPTED_BACKUP_DIR/
        log_message "Corrupted data backed up to $CORRUPTED_BACKUP_DIR"
    fi
}

# 第3步：清理数据目录
clean_data_directory() {
    log_message "Cleaning MySQL data directory"
    sudo rm -rf /var/lib/mysql/*
    sudo mkdir -p /var/lib/mysql
    sudo chown mysql:mysql /var/lib/mysql
    log_message "Data directory cleaned"
}

# 第4步：恢复基础备份
restore_base_backup() {
    log_message "Restoring base backup"
    
    # 查找最新的完整备份
    LATEST_FULL_BACKUP=$(find $BACKUP_DIR/weekly -name "full_backup.sql.gz" | sort -r | head -1)
    
    if [ -z "$LATEST_FULL_BACKUP" ]; then
        log_message "No full backup found"
        exit 1
    fi
    
    log_message "Using backup: $LATEST_FULL_BACKUP"
    
    # 启动 MySQL（临时）
    sudo systemctl start mysql
    sleep 10
    
    # 恢复备份
    gunzip < $LATEST_FULL_BACKUP | mysql -u $MYSQL_USER -p$MYSQL_PASS
    
    if [ $? -eq 0 ]; then
        log_message "Base backup restored successfully"
    else
        log_message "Failed to restore base backup"
        exit 1
    fi
}

# 第5步：应用二进制日志进行点时间恢复
apply_binlog_recovery() {
    log_message "Applying binary log for point-in-time recovery"
    
    # 查找需要应用的二进制日志文件
    BINLOG_FILES=$(find $BACKUP_DIR/binlogs -name "*.tar.gz" | sort)
    
    for binlog_archive in $BINLOG_FILES; do
        log_message "Processing binlog archive: $binlog_archive"
        
        # 解压二进制日志
        TEMP_DIR="/tmp/binlog_recovery_$(date +%s)"
        mkdir -p $TEMP_DIR
        tar -xzf $binlog_archive -C $TEMP_DIR
        
        # 应用二进制日志到目标时间
        for binlog_file in $TEMP_DIR/mysql-bin.*; do
            if [ -f "$binlog_file" ]; then
                mysqlbinlog --stop-datetime="$TARGET_TIME" $binlog_file | \
                    mysql -u $MYSQL_USER -p$MYSQL_PASS
            fi
        done
        
        # 清理临时文件
        rm -rf $TEMP_DIR
    done
    
    log_message "Binary log recovery completed"
}

# 第6步：验证恢复结果
verify_recovery() {
    log_message "Verifying recovery results"
    
    # 检查数据库连接
    mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SELECT 1;" > /dev/null 2>&1
    if [ $? -ne 0 ]; then
        log_message "Cannot connect to MySQL after recovery"
        exit 1
    fi
    
    # 检查关键表的数据
    mysql -u $MYSQL_USER -p$MYSQL_PASS -e "
        SELECT 'Database recovery verification' as status;
        SHOW DATABASES;
        USE backup_demo;
        SELECT COUNT(*) as user_count FROM users;
        SELECT COUNT(*) as order_count FROM orders;
        SELECT COUNT(*) as product_count FROM products;
    "
    
    log_message "Recovery verification completed"
}

# 第7步：重启服务
restart_services() {
    log_message "Restarting MySQL service"
    sudo systemctl restart mysql
    
    if [ $? -eq 0 ]; then
        log_message "MySQL service restarted successfully"
    else
        log_message "Failed to restart MySQL service"
        exit 1
    fi
}

# 主恢复流程
main_recovery() {
    log_message "Starting disaster recovery process"
    
    stop_mysql
    backup_corrupted_data
    clean_data_directory
    restore_base_backup
    apply_binlog_recovery
    verify_recovery
    restart_services
    
    log_message "Disaster recovery completed successfully"
}

# 执行恢复
main_recovery
                

恢复测试和验证

-- 创建恢复测试脚本
DELIMITER //
CREATE PROCEDURE TestRecovery()
BEGIN
    DECLARE user_count INT;
    DECLARE order_count INT;
    DECLARE product_count INT;
    DECLARE test_result VARCHAR(100);
    
    -- 检查数据完整性
    SELECT COUNT(*) INTO user_count FROM users;
    SELECT COUNT(*) INTO order_count FROM orders;
    SELECT COUNT(*) INTO product_count FROM products;
    
    -- 验证数据一致性
    IF user_count > 0 AND order_count > 0 AND product_count > 0 THEN
        SET test_result = 'Recovery test PASSED';
    ELSE
        SET test_result = 'Recovery test FAILED';
    END IF;
    
    -- 输出测试结果
    SELECT 
        test_result as result,
        user_count as users,
        order_count as orders,
        product_count as products,
        NOW() as test_time;
        
    -- 检查外键约束
    SELECT 'Foreign key check:' as info;
    SELECT 
        o.id,
        o.user_id,
        u.username
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    WHERE u.id IS NULL
    LIMIT 5;
    
    -- 检查数据范围
    SELECT 'Data range check:' as info;
    SELECT 
        MIN(created_at) as earliest_record,
        MAX(created_at) as latest_record
    FROM users;
END //
DELIMITER ;

-- 执行恢复测试
CALL TestRecovery();

-- 性能测试
SET @start_time = NOW(6);
SELECT 
    u.username,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;
SET @end_time = NOW(6);

SELECT 
    'Performance test' as test_type,
    TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) / 1000 as execution_time_ms;

-- 清理测试对象
DROP PROCEDURE IF EXISTS TestRecovery;
                

最佳实践和注意事项

🎯 备份最佳实践：

定期测试恢复：至少每月测试一次完整的恢复流程
多层备份策略：结合逻辑备份、物理备份和实时复制
异地存储：重要备份必须存储在不同地理位置
自动化监控：建立备份成功/失败的自动告警机制
文档化流程：详细记录备份和恢复的操作步骤
权限控制：严格控制备份文件的访问权限
加密保护：对敏感数据的备份进行加密

⚠️ 常见错误和注意事项：

备份未测试：备份文件损坏或无法恢复时才发现
权限问题：恢复时文件权限不正确导致 MySQL 无法启动
版本不兼容：不同 MySQL 版本间的备份恢复兼容性问题
磁盘空间不足：恢复时目标磁盘空间不够
字符集问题：备份和恢复时字符集设置不一致
时区问题：时间戳数据在不同时区间的转换问题
依赖关系：忽略外键约束和触发器的恢复顺序

备份安全和合规

# 1. 备份文件加密
# 使用 GPG 加密备份文件
mysqldump -u root -p backup_demo | gzip | \
    gpg --cipher-algo AES256 --compress-algo 2 --symmetric \
    --output backup_demo_encrypted_$(date +%Y%m%d).sql.gz.gpg

# 解密备份文件
gpg --decrypt backup_demo_encrypted_20240120.sql.gz.gpg | \
    gunzip | mysql -u root -p backup_demo_restored

# 2. 使用 OpenSSL 加密
mysqldump -u root -p backup_demo | \
    openssl enc -aes-256-cbc -salt -out backup_demo_ssl_$(date +%Y%m%d).sql.enc

# 解密 OpenSSL 备份
openssl enc -aes-256-cbc -d -in backup_demo_ssl_20240120.sql.enc | \
    mysql -u root -p backup_demo_restored

# 3. 备份文件完整性校验
# 创建校验和
sha256sum backup_demo_full.sql > backup_demo_full.sql.sha256

# 验证校验和
sha256sum -c backup_demo_full.sql.sha256

# 4. 安全的备份传输
# 使用 rsync over SSH
rsync -avz -e ssh /backup/mysql/ user@backup-server:/remote/backup/mysql/

# 使用 scp 传输
scp backup_demo_full.sql.gz user@backup-server:/remote/backup/

# 5. 备份访问日志
# 记录备份文件访问
auditctl -w /backup/mysql -p rwxa -k mysql_backup_access

# 查看访问日志
ausearch -k mysql_backup_access
                

-- 清理示例数据和对象
DROP DATABASE IF EXISTS backup_demo;

SELECT 'Backup and recovery tutorial completed. All example objects cleaned up.' AS message;
                