项目需求 | MySQL增量备份与恢复的完整操作指南

目录

一、MySql数据库增量备份的工作原理

1、全量备份与增量备份

2、增量备份原理

二、进行增量备份

步骤1：启用二进制日志

使用 SHOW VARIABLES 命令查看二进制日志状态

步骤2：执行增量备份脚本

三、使用增量备份恢复损坏的数据库

步骤1：恢复全量备份

步骤2：执行增量恢复脚本恢复全量备份

---

一、MySql数据库增量备份的工作原理

1、全量备份与增量备份

在数据库管理中，备份是确保数据安全的关键步骤。数据库发生故障、数据丢失或误操作时，备份是恢复数据的唯一途径。全量备份保存整个数据库的完整副本，但随着数据量增加，备份时间和存储需求也会显著增加。相比之下，增量备份可以有效优化这一过程。增量备份仅保存自上次备份以来的变更数据，极大减少了备份时间和存储空间。特别是在数据更新频繁的业务环境中，增量备份是一种高效且经济的选择，能够快速恢复到最新的数据库状态，确保数据安全与业务连续性。

2、增量备份原理

MySql的增量备份实际上是在完成一次完整的全量备份之后，后续只备份自上次备份以来对数据库所做的变更，例如新增（INSERT）、修改（UPDATE）和删除（DELETE）操作。增量备份呢依赖于 二进制日志（Binary Logs），这些日志记录了所有对数据库的更改（如 INSERT、UPDATE 和 DELETE 操作）。在恢复数据库时，先恢复数据库的全备份，恢复到某个时间点的完整数据状态。再从前往后执行备份的增量备份日志，将数据库恢复到最新状态。

1. 全备份：
   • 全备份 是备份整个数据库的完整副本，通常在备份操作的起点进行。它包含所有表结构、数据等。
   • 全备份创建之后，后续的备份可以依赖增量备份来减少备份的数据量和时间。
2. 二进制日志（Binary Logs）：
   • MySql 的二进制日志是一个关键组件，它记录了所有对数据库造成更改的 SQL 语句，包括 INSERT、UPDATE、DELETE 等操作。通过保存这些二进制日志，可以实现增量备份。
   • 二进制日志不记录 SELECT 操作，因为它们不会更改数据。
3. 增量备份：
   • 增量备份不再保存整个数据库，而是通过读取并保存自上次备份以来的二进制日志文件，记录这些日志所涉及的更改。
   • 增量备份通常以二进制日志文件的方式存在（如 MySql-bin.000001），也可以通过 MySqlbinlog 工具导出为 SQL 格式，以便日后恢复。
4. 恢复过程：
   • 恢复数据库时，首先应用全备份，恢复到某个时间点的完整数据状态。
   • 然后，依次应用每个增量备份（即二进制日志文件），将数据库恢复到最新状态。

二、进行增量备份

步骤1：启用二进制日志

首先，确保 MySql 配置文件中启用了二进制日志功能。编辑 MySql 配置文件（my.cnf 或 my.ini）并添加以下行：

[MySqld]
log-bin=/var/log/MySql/MySql-bin

启用后，重新启动 MySql 服务：

sudo systemctl restart MySql

使用 SHOW VARIABLES 命令查看二进制日志状态

如果输出结果是 ON，说明二进制日志已开启；如果是 OFF，则表示二进制日志未启用。

步骤2：执行增量备份脚本

创建一个的bash脚本文件backup_MySql.sh，将下面的代码段进行如下四步修改后拷贝进去。

• 将 MySql_USER 和 MySql_PASSWORD 替换为你的 MySql 用户名和密码。
• 将 MySql_HOST 设置为 MySql 服务器的地址，通常是 localhost。
• 将BINLOG_DIR 设置为本机MySql数据库的二进制日志存放目录。
• 将倒数第二行的sleep后方的数字(文中是sleep 10)修改为你希望的增量备份的时间间隔。

#!/bin/bash

# MySql 配置
MySql_USER="root"
MySql_PASSWORD="xxxxxx."
MySql_HOST="localhost"
BACKUP_DIR="./backup"
LOG_FILE="$BACKUP_DIR/backup.log"
BINLOG_DIR="/www/server/data"  # 二进制日志目录
FULL_BACKUP_DIR="$BACKUP_DIR/full"
INCREMENTAL_BACKUP_DIR="$BACKUP_DIR/incremental"
LAST_BACKUP_FILE="$BACKUP_DIR/last_backup_pos.txt"

# 检查备份目录是否存在，不存在则创建
mkdir -p "$FULL_BACKUP_DIR" "$INCREMENTAL_BACKUP_DIR"

# 完全备份函数
full_backup() {
    TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
    FULL_BACKUP_PATH="$FULL_BACKUP_DIR/full_backup_$TIMESTAMP.sql"

    echo "Starting full backup at $TIMESTAMP" >> "$LOG_FILE"

    MySqldump -u "$MySql_USER" -p"$MySql_PASSWORD" -h "$MySql_HOST" --all-databases > "$FULL_BACKUP_PATH"

    # 记录二进制日志位置
    MySql -u "$MySql_USER" -p"$MySql_PASSWORD" -h "$MySql_HOST" -e "SHOW MASTER STATUS;" | awk 'NR==2 {print $1, $2}' > "$LAST_BACKUP_FILE"

    echo "Full backup completed at $TIMESTAMP" >> "$LOG_FILE"
}

# 增量备份函数
incremental_backup() {
    TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
    INCREMENTAL_BACKUP_PATH="$INCREMENTAL_BACKUP_DIR/incremental_backup_$TIMESTAMP.sql"
    
    echo "Starting incremental backup at $TIMESTAMP" >> "$LOG_FILE"

    # 读取上次备份的二进制日志位置
    if [ -f "$LAST_BACKUP_FILE" ]; then
        BINLOG_FILE=$(awk '{print $1}' "$LAST_BACKUP_FILE")
        BINLOG_POS=$(awk '{print $2}' "$LAST_BACKUP_FILE")

        # 进行增量备份
        MySqlbinlog --start-position="$BINLOG_POS" "$BINLOG_DIR/$BINLOG_FILE" > "$INCREMENTAL_BACKUP_PATH"

        # 记录当前的二进制日志位置
        MySql -u "$MySql_USER" -p"$MySql_PASSWORD" -h "$MySql_HOST" -e "SHOW MASTER STATUS;" | awk 'NR==2 {print $1, $2}' > "$LAST_BACKUP_FILE"

        echo "Incremental backup completed at $TIMESTAMP" >> "$LOG_FILE"
    else
        echo "No previous backup found, running full backup instead." >> "$LOG_FILE"
        full_backup
    fi
}

# 每隔3600秒运行增量备份
while true; do
    # 判断是否是第一次运行（没有完全备份），如果是则先做完全备份
    if [ ! -f "$LAST_BACKUP_FILE" ]; then
        full_backup
    else
        incremental_backup
    fi

    # 休眠10秒
    sleep 10
done

运行脚本：
给脚本添加执行权限并运行：

chmod +x backup_MySql.sh
./backup_MySql.sh

确认备份文件目录：确保增量备份的 SQL 文件存放在 INCREMENTAL_BACKUP_DIR（例如 /backup/MySql/incremental/）目录下，文件名格式为 incremental_backup_*.sql，并按时间顺序命名。

• 脚本会从指定的备份目录中读取所有按时间命名的增量 SQL 文件，并将它们按顺序恢复到指定的 MySql 数据库中。
• 如果恢复过程中遇到错误，脚本会停止并提示出错的文件。

三、使用增量备份恢复损坏的数据库

向数据库中生成一些数据，执行增量备份。

删除生成的数据，模拟数据库损毁的情况。

步骤1：恢复全量备份

MySql -uroot -p. < ./full_backup_2024-09-12_16-01-04.sql

以下参数需要进行替换或注意：

• -uroot：将root用户替换为你想使用的你的 MySql 用户名。
• -p：提示输入密码（恢复时需要输入 MySql 密码）。
• ./full_backup_2024-09-12_16-01-04.sql：将此文件替换为脚本执行时产生的全量备份文件。

步骤2：执行增量恢复脚本恢复全量备份

在增量备份脚本相同目录下创建restore_incremental.sh文件，将下面的代码段进行如下四步修改后拷贝进去。

• 将 MySql_USER 和 MySql_PASSWORD 替换为你的 MySql 用户名和密码
• 将 MySql_HOST 设置为 MySql 服务器的地址，通常是 localhost
• 将BINLOG_DIR 设置为本机MySql数据库的二进制日志存放目录。

#!/bin/bash

# MySql 登录信息
MySql_USER="root"
MySql_PASSWORD="xxxxxx."
MySql_HOST="localhost"

# 增量备份目录
INCREMENTAL_BACKUP_DIR="./backup/incremental"

# 检查是否有备份文件
if [ ! -d "$INCREMENTAL_BACKUP_DIR" ]; then
  echo "增量备份目录不存在: $INCREMENTAL_BACKUP_DIR"
  exit 1
fi

# 获取所有增量备份文件（按时间顺序）
sql_files=$(ls $INCREMENTAL_BACKUP_DIR/incremental_backup_*.sql | sort)

# 恢复每一个增量备份文件
for sql_file in $sql_files; do
  echo "正在恢复增量备份: $sql_file"
  MySql -u $MySql_USER -p$MySql_PASSWORD -h $MySql_HOST < $sql_file
  if [ $? -ne 0 ]; then
    echo "恢复 $sql_file 时出错"
    exit 1
  fi
done

echo "所有增量备份已成功恢复。"

运行脚本：
给脚本添加执行权限并运行：

chmod +x backup_MySql.sh
./backup_MySql.sh

刷新后数据全部恢复成功。