高效管理百万级数据：MySQL备份与恢复实战指南

高效管理百万级数据：MySQL备份与恢复实战指南

在当今数字化时代，数据是企业不可或缺的核心资产之一。如何高效管理百万级数据的备份与恢复工作，是每个企业和开发者都需要面对的重要课题。本文将通过详细的步骤和最佳实践，为你提供全面而实用的解决方案。

环境准备

在开始之前，我们需要创建两张表并生成一百万条数据。以下是具体的SQL语句：

-- 创建主数据表
CREATE TABLE main_table (
  id INT NOT NULL AUTO_INCREMENT,
  data1 VARCHAR(255),
  data2 VARCHAR(255),
  data3 VARCHAR(255),
  data4 INT,
  data5 INT,
  data6 DATE,
  data7 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- 创建关联数据表
CREATE TABLE related_table_1 (
  id INT NOT NULL AUTO_INCREMENT,
  main_id INT,
  data1 VARCHAR(255),
  data2 VARCHAR(255),
  data3 VARCHAR(255),
  data4 INT,
  data5 INT,
  data6 DATE,
  data7 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (main_id) REFERENCES main_table(id)
);

接下来，我们生成一百万条数据：

INSERT INTO main_table (data1, data2, data3, data4, data5, data6) 
SELECT CONCAT('Data', a.a), CONCAT('Data', b.a), CONCAT('Data', c.a), RAND()*100, RAND()*100, CURDATE() 
FROM (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a 
CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b 
CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c 
LIMIT 1000000; -- 主表插入数据

INSERT INTO related_table_1 (main_id, data1, data2, data3, data4, data5, data6) 
SELECT id, CONCAT('MoreData', a.a), CONCAT('MoreData', b.a), CONCAT('MoreData', c.a), RAND()*100, RAND()*100, CURDATE() 
FROM main_table, 
(SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a 
CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b 
CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c 
LIMIT 1000000; -- 关联表生成约1,000,000条数据

备份脚本

接下来，我们创建一个备份脚本。以下是具体的代码：

#!/bin/bash
# 配置
DB_USER="your_db_user"
DB_PASSWORD="your_db_password"
DB_NAME="your_db_name"
BACKUP_PATH="/path/to/your/backup/directory"
LOG_PATH="/path/to/your/log/directory"
LOG_FILE="$LOG_PATH/backup.log"
#设置备份文件数量
MAX_BACKUP_FILES=10
#设置日志数量
MAX_LOG_FILES=10
# 设定日志文件大小阈值，例如1MB。
MAX_LOG_SIZE=1048576
# 创建备份和日志目录（如果它们不存在）
mkdir -p "$BACKUP_PATH"
mkdir -p "$LOG_PATH"
# 生成备份文件名
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="$BACKUP_PATH/$DB_NAME_$TIMESTAMP.sql"
# 开始备份，并记录开始时间
echo "[$(date +"%Y-%m-%d %H:%M:%S")] Starting backup for database: $DB_NAME" >> "$LOG_FILE"
START_TIME=$(date +%s)
# 执行备份命令
mysqldump -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" > "$BACKUP_FILE" 2>> "$LOG_FILE"
# 检查备份命令是否成功
if [ $? -eq 0 ]; then
  echo "[$(date +"%Y-%m-%d %H:%M:%S")] Backup successful: $BACKUP_FILE" >> "$LOG_FILE"
  # 计算备份文件大小
  BACKUP_SIZE=$(du -sh "$BACKUP_FILE" | cut -f1)
  echo "Backup size: $BACKUP_SIZE" >> "$LOG_FILE"
else
  echo "[$(date +"%Y-%m-%d %H:%M:%S")] Error during backup" >> "$LOG_FILE"
fi
# 记录备份结束时间，并计算耗时
END_TIME=$(date +%s)
ELAPSED_TIME=$((END_TIME - START_TIME))
echo "Elapsed time: $ELAPSED_TIME seconds" >> "$LOG_FILE"
# 备份文件轮转，删除最旧的，只保留最新的MAX_BACKUP_FILES个文件
find "$BACKUP_PATH" -name "*.sql" -type f | sort | head -n -"$MAX_BACKUP_FILES" | xargs rm -f
# 日志轮转，保留最新的N个日志文件
# 检查日志文件是否存在及其大小
if [ -f "$LOG_FILE" ]; then
    LOG_SIZE=$(stat -c%s "$LOG_FILE")
    if [ $LOG_SIZE -gt $MAX_LOG_SIZE ]; then
        # 如果当前日志文件超过阈值，则重命名当前日志文件，加上时间戳
        mv "$LOG_FILE" "$LOG_PATH/backup_$(date +"%Y-%m-%d_%H-%M-%S").log"
    fi
fi
find "$LOG_PATH" -name '*.log' -type f | sort | head -n -"$MAX_LOG_FILES" | xargs rm -f
# 结束脚本
echo "[$(date +"%Y-%m-%d %H:%M:%S")] Backup script completed" >> "$LOG_FILE"

注意事项：

• 权限：确保脚本有执行权限，使用chmod +x mysql_backup.sh命令。
• 配置：替换脚本中的DB_USER, DB_PASSWORD, DB_NAME, BACKUP_PATH, 和 LOG_PATH为实际值。
• 定时任务：考虑使用cron任务定期执行此备份脚本，确保数据定期备份。
• 如果出现“mysqldump: not found”的报错，可以使用软链接将其加入全局变量，例如：sudo ln -s /usr/local/mysql/bin/mysql /usr/bin/。

执行备份脚本后，可以看到已成功生成备份文件与日志文件。

恢复脚本

为了实现自动化恢复过程，减少人为错误，我们编写一个数据库恢复脚本。以下是具体的代码：

#!/bin/bash
# 检查是否提供了备份文件作为参数
if [ "$#" -ne 1 ]; then
    echo "Usage: $0 <backup_file_path>"
    exit 1
fi
BACKUP_FILE="$1" # 使用脚本参数指定的备份文件
# 配置
DB_USER="your_db_user"
DB_PASSWORD="your_db_password"
DB_NAME="your_db_name_to_restore"
BACKUP_PATH="/path/to/your/backup/directory"
LOG_PATH="/path/to/your/log/directory"
LOG_FILE="$LOG_PATH/restore.log"
# 创建日志目录（如果它不存在）
mkdir -p "$LOG_PATH"
# 开始恢复，并记录开始时间
echo "[$(date +"%Y-%m-%d %H:%M:%S")] Starting restore for database: $DB_NAME from file: $BACKUP_FILE" >> "$LOG_FILE"
START_TIME=$(date +%s)
# 执行恢复命令
mysql -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < "$BACKUP_FILE" 2>> "$LOG_FILE"
# 检查恢复命令是否成功
if [ $? -eq 0 ]; then
  echo "[$(date +"%Y-%m-%d %H:%M:%S")] Restore successful for database: $DB_NAME from file: $BACKUP_FILE" >> "$LOG_FILE"
else
  echo "[$(date +"%Y-%m-%d %H:%M:%S")] Error during restore" >> "$LOG_FILE"
fi
# 记录恢复结束时间，并计算耗时
END_TIME=$(date +%s)
ELAPSED_TIME=$((END_TIME - START_TIME))
echo "Elapsed time: $ELAPSED_TIME seconds" >> "$LOG_FILE"
# 结束脚本
echo "[$(date +"%Y-%m-%d %H:%M:%S")] Restore script completed" >> "$LOG_FILE"

执行命令时记得加上参数，例如：

sudo sh mysql_restore.sh /home/test/bak_sql/2024-03-10_13-08-35.sql

结论

本篇文章主要包含三个部分：生成数据、备份脚本、恢复脚本。每一步骤都在虚拟机上完成了验证，可以正常运行。通过本文提供的解决方案，你可以有效地管理百万级数据的备份与恢复工作，确保数据的完整性和可靠性。无论是面对突发故障、意外数据丢失还是系统升级，这些工具和技巧都将为你提供有力的支持。