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

简介

在当今数字化时代，数据是企业不可或缺的核心资产之一，而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任务定期执行此备份脚本，确保数据定期备份。
我是离线安装的MySQL，虽然配置了变量，但还是出现了一个“mysqldump: not found”的报错，好尴尬，但没关系，脚本里备份命令写全路径就行，例如这样/usr/local/mysql/bin/mysqldump，或者使用软链接将其加入全局变量，例如：sudo ln -s /usr/local/mysql/bin/mysql /usr/bin/。
执行备份脚本：

可以看到图中已成功生成了备份文件与日志文件。
然后直接干掉这个数据库，运行试试：

--删除数据库
DROP DATABASE test;
--创建数据库
CREATE DATABASE test;
--运行备份文件
source /home/test/bak_sql/2024-03-10_13-08-35.sql;

看看数据量：

select count(*) from related_table_1;

创建恢复脚本

编写一个数据库恢复脚本实现自动化恢复过程，减少人为错误，废话少说，直接上脚本：

#!/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

结论

本篇文章主要有三个部分：生成数据、备份脚本、恢复脚本，每一步骤都在虚拟机上完成了验证，已确认可以正常运行，请放心食用。