Mysql使用binlog做增量备份

开启 binlog

编辑 MySQL 配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

写入下面代码开启二进制日志（binlog），并使其具备记录增量备份所需的信息，日志文件前缀为 mysql-bin，server-id服务实例 ID必须设置，必须是一个正整数（1~2^32-1）

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server-id = 1

重启 mysqlsudo service mysql restart

只做一次全量备份

mysqldump -uroot -p fs_db > full.sql

// 关闭外键、触发器导出
mysqldump \
  --single-transaction \
  --quick \
  --skip-lock-tables \
  --skip-add-locks \
  --skip-add-drop-table \
  --no-tablespaces \
  -uroot -p fs_db > full.sql

// 直接复制 data 目录最快
sudo systemctl stop mysql
sudo -s
sudo cp -r /var/lib/mysql/<数据库> <>
sudo systemctl start mysql

导出 binlog 增量文件

到出增量文件

mysqlbinlog --read-from-remote-server --host=localhost -uroot -p mysql-bin.000001 > inc1.sql

mysqlbinlog --read-from-remote-server --host=localhost -uroot -p mysql-bin.000002 > inc2.sql
...

数据库恢复方法

单个导入

mysql -uroot -p <数据库> < full.sql
mysql -uroot -p <数据库> <  inc1.sql
mysql -uroot -p <数据库> <  inc2.sql
...

批量导入

for f in inc*.sql; do
    echo "导入 $f ..."
    mysql -uroot -p fs_db < "$f"
done

合并所有 SQL 文件，再一次导入

cat full.sql day1.sql day2.sql | mysql -u root -p <数据库>

定时任务

查看 cron 服务状态

sudo systemctl status cron

启动 cron 服务

sudo systemctl start cron

开机自动启动

sudo systemctl enable cron

编辑当前用户的定时任务，第一次会让你选择编辑器，推荐 nano

whoami
crontab -e

查看Root 用户的任务

sudo crontab -l

删除Root 用户的所有任务

sudo crontab -r

crontab 一行格式

* * * * * command_to_run

五个星号依次代表：

1. 分钟（0-59）
2. 小时（0-23）
3. 日（1-31）
4. 月（1-12）
5. 星期（0-7，0 和 7 都表示星期日

30 2 * * * /home/user/backup.sh表示每天凌晨 2:30 执行 /home/user/backup.sh

符号	含义
*	任意时间
,	枚举值，例如 1,15 表示 1号和15号
-	区间，例如 1-5 表示 1 到 5
/	步长，例如 */10 表示每 10 分钟

# 每小时执行一次
0 * * * * /home/user/script.sh

# 每天凌晨 1 点执行
0 1 * * * /home/user/script.sh

# 每周一早上 8 点执行
0 8 * * 1 /home/user/script.sh

# 每 5 分钟执行一次
*/5 * * * * /home/user/script.sh

为了调试任务是否执行，可以将输出重定向到日志

* * * * * /home/user/script.sh >> /home/user/script.log 2>&1

在/home/user/.my.cnf文件写入下面代码避免在脚本中明文写密码

[client]
user=root
password=123456789

创建自动备份脚本backup.sh

#!/bin/bash


BACKUP_DIR="/home/user/sql_backups"
STATE_FILE="$BACKUP_DIR/last_backup.txt"
MYSQL_BINLOG_DIR="/var/log/mysql"
# 指定 MySQL 登录配置（建议使用 .my.cnf 避免在脚本中明文写密码）
MYSQL_CMD="mysql --defaults-file=/home/user/.my.cnf"
mkdir -p "$BACKUP_DIR"

# 1. 获取 MySQL 当前最新的 Binlog 文件名和位置 (End_log_pos)
CURRENT_STATUS=$($MYSQL_CMD -e "SHOW MASTER STATUS\G")
CURRENT_LOG=$(echo "$CURRENT_STATUS" | grep "File:" | awk '{print $2}')
CURRENT_POS=$(echo "$CURRENT_STATUS" | grep "Position:" | awk '{print $2}')

if [[ -z "$CURRENT_LOG" ]]; then
    echo "❌ Error: Could not get MySQL master status."
    exit 1
fi

echo "Current MySQL Status: $CURRENT_LOG at position $CURRENT_POS"

# 2. 读取上次备份的状态
START_POS=4 # 默认起始位置（Binlog 文件头通常是 4）
START_LOG=$CURRENT_LOG
if [[ -f "$STATE_FILE" ]]; then
    read LAST_LOG LAST_POS < "$STATE_FILE"
    # 如果上次备份的文件和现在是同一个，则断点续传
    if [[ "$LAST_LOG" == "$CURRENT_LOG" ]]; then
        START_POS=$LAST_POS
        echo "Resuming from $LAST_LOG position $START_POS"
    else
        echo "⚠️  Log rotation detected ($LAST_LOG -> $CURRENT_LOG). Backing up current log from start."
        START_POS=4
    fi
fi

# 3. 检查是否有新数据
if [[ "$START_POS" -eq "$CURRENT_POS" ]]; then
    echo "✅ No new data changes. Skip backup."
    exit 0
fi

# 4. 生成备份文件名
DAY_DIR=$(date +"%Y%m%d")
mkdir -p "$BACKUP_DIR/$DAY_DIR"
TIME=$(date +"%H%M%S")
BACKUP_FILE="$BACKUP_DIR/$DAY_DIR/inc-$TIME.sql"

# 5. 执行增量备份
echo "Backing up $CURRENT_LOG from $START_POS to $CURRENT_POS ..."
if mysqlbinlog --start-position="$START_POS" --stop-position="$CURRENT_POS" "$MYSQL_BINLOG_DIR/$CURRENT_LOG" > "$BACKUP_FILE"; then
    # 6. 成功后更新状态文件
    echo "$CURRENT_LOG $CURRENT_POS" > "$STATE_FILE"
    echo "✅ Incremental backup completed: $BACKUP_FILE"
else
    echo "❌ Backup failed!"
    rm -f "$BACKUP_FILE" # 删除失败的空文件
    exit 1
fi

每天自动合并增量文件

在/etc/cron.daily/ 目录下创建新的脚本daily_merge，去掉扩展名，Debian/Ubuntu 的 run-parts 机制通常要求脚本名没有 .sh 后缀，注意BACKUP_DIR需要修改为你的备份目录

#!/bin/bash

BACKUP_DIR="/home/user/sql_backups"
# 获取昨天的日期
YESTERDAY=$(date -d "yesterday" +"%Y%m%d")
TARGET_DIR="$BACKUP_DIR/$YESTERDAY"

# 如果昨天的目录存在
if [ -d "$TARGET_DIR" ]; then
    echo "Merging files for $YESTERDAY..."
    
    # 1. 合并文件 (按文件名排序非常重要！)
    # 把所有 inc-*.sql 合并成一个 daily-20251201.sql
    cat "$TARGET_DIR"/inc-*.sql > "$BACKUP_DIR/daily-$YESTERDAY.sql"
    
    # 2. 检查合并是否成功
    if [ -s "$BACKUP_DIR/daily-$YESTERDAY.sql" ]; then
        # 成功后，删除原来的碎片目录
        rm -rf "$TARGET_DIR"
        echo "✅ Merged into daily-$YESTERDAY.sql"
    else
        echo "❌ Merge failed, keeping original files."
    fi
fi

赋予执行权限

sudo chmod +x /etc/cron.daily/daily_merge

备份文件目录

**/sql_backups**$ 
**.**
├── **20251201**
│   └── inc-170001.sql
├── full.sql
└── last_backup.txt

恢复脚本

创建restore.sh，并复制下面代码

#!/bin/bash

# 备份根目录
BACKUP_DIR="/home/user/sql_backups"
DB_NAME=""
DB_USER="root"


# 1. 检查全量备份是否存在
FULL_BACKUP="$BACKUP_DIR/full.sql"
if [[ ! -f "$FULL_BACKUP" ]]; then
    echo "❌ Error: 全量备份文件未找到: $FULL_BACKUP"
    exit 1
fi

# 2. 危险操作警告
echo "============================================="
echo "⚠️  警告：即将开始数据恢复！"
echo "⚠️  目标数据库: [ $DB_NAME ]"
echo "⚠️  这将覆盖该数据库中的现有数据！"
echo "============================================="
read -p "❓ 确认要继续吗？(输入 yes 确认): " CONFIRM

if [[ "$CONFIRM" != "yes" ]]; then
    echo "🚫 操作已取消。"
    exit 0
fi

# 3. 收集所有增量备份文件
echo "🔍 正在搜索并排序增量备份文件..."
INC_FILES=$(find "$BACKUP_DIR" -name "inc-*.sql" | sort)

COUNT=$(echo "$INC_FILES" | wc -w)
echo "📄 找到全量备份: 1 个"
echo "📄 找到增量备份: $COUNT 个"

# 4. 开始恢复
echo "🚀 开始恢复数据，请稍候..."
echo "---------------------------------------------"

# 组合命令解释：
# 1. echo "SET SQL_LOG_BIN=0;" -> 告诉 MySQL 恢复过程中不要写新的 Binlog（提高速度，防止死循环）
# 2. cat $FULL_BACKUP -> 读取全量数据
# 3. cat $INC_FILES -> 读取所有增量数据
# 4. | mysql ... -> 通过管道一次性传给数据库执行

(
    echo "SET SQL_LOG_BIN=0;"
    cat "$FULL_BACKUP"
    if [ -n "$INC_FILES" ]; then
        cat $INC_FILES
    fi
) | mysql -u "$DB_USER" -p "$DB_NAME"

# 5. 检查结果
if [ $? -eq 0 ]; then
    echo "---------------------------------------------"
    echo "✅ 恢复成功！数据库已还原到最新状态。"
else
    echo "---------------------------------------------"
    echo "❌ 恢复过程中出现错误，请检查密码或 SQL 文件。"
fi