MySQL迁移表数据到历史表，并定时同步到历史表，不影响主从复制

一、基于【分区】的归档与清理（推荐）

核心思路

对 open_api_log 表按 create_time 做时间范围分区
每天仅新增一个新分区，自动让更老的分区落到“历史”表；
通过 ALTER TABLE … EXCHANGE PARTITION 实现极速“分区与表”交换，达到秒级归档／清理的效果；
分区操作是 DDL，一条语句完成，不会产生逐行删删删的 Binlog，极大减少主库 IO 压力，也不易阻塞复制。

操作步骤

修改表结构为分区表

-- 注意：分区键必须是 DATETIME 或者能映射到 INT 的字段
ALTER TABLE open_api_log
PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p0 VALUES LESS THAN (TO_DAYS(NOW()) - INTERVAL 7 DAY),
  PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

p0：历史分区，存放 7 天以前的数据
p1：当前数据分区

创建“历史”表结构（已存在 open_api_log_his），确保与分区表结构一模一样，且不分区。

每天通过事件或脚本，交换 p0 与 open_api_log_his：

-- 1）先把历史分区 p0 挂到 open_api_log_his
ALTER TABLE open_api_log
  EXCHANGE PARTITION p0
  WITH TABLE open_api_log_his
  WITHOUT VALIDATION;
-- 2）再给 open_api_log 新增一个空分区 p0
ALTER TABLE open_api_log
  REORGANIZE PARTITION p1 INTO (
    PARTITION p0 VALUES LESS THAN (TO_DAYS(NOW()) - INTERVAL 7 DAY),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
  );

第1步：不是行级删除，而是分区与表元数据交换，速度非常快，Binlog 只记录 DDL。
第2步：重组剩余分区，保持新旧分区命名一致，以便下一次循环。

将“新” open_api_log_his 按需清理
- 如果你想对历史表再分区或按月归档，可以在 open_api_log_his 上再做分区或周期性清理。

定时方案

MySQL 内置事件（需确保 event_scheduler=ON）：

CREATE EVENT evt_archive_open_api_log
  ON SCHEDULE EVERY 1 DAY
  STARTS '2025-07-02 03:00:00'
DO
BEGIN
  -- 见上面交换分区的两条 ALTER 语句
END;

或者 Linux Crontab，调用 mysql 命令执行 SQL 脚本：

0 3 * * *  mysql -uroot -pYourPass -e "ALTER TABLE …; ALTER TABLE …;"

主从复制影响

DDL 操作（分区、交换）会作为单条事件写入 Binlog，Slave 上执行相同 DDL，不会“断链”。
几乎零阻塞：DDL 虽会短暂锁表，但由于不遍历数据，耗时通常在几秒以内，不会造成大事务阻塞复制线程。
推荐：先在测试环境演练一次，确认分区数、分区定义及 DDL 权限，然后再部署到生产。

二、基于【批量行迁移】的归档脚本

如果暂时无法使用分区，可用此方法。

核心思路

循环批量 INSERT … SELECT … LIMIT N 将 7 天前数据搬到 open_api_log_his；
紧跟 DELETE … LIMIT N 删除主表相同行；
分批次执行（例如每批 5,000 条），避免单次长事务；
通过脚本＋定时 自动化。

示例脚本（Shell + SQL）

#!/bin/bash
# archive_open_api_log.sh
USER=root; PASS=YourPass; DB=bfm; HOST=127.0.0.1; PORT=10004
# 每批处理条数
BATCH=5000
# 计算 7 天前日期
CUTOFF=$(date -d '7 days ago' '+%F %T')

while true; do
  # 1. 插入历史表
  ROWS=$(mysql -N -u$USER -p$PASS -h$HOST -P$PORT -e "
    INSERT IGNORE INTO ${DB}.open_api_log_his
    SELECT * FROM ${DB}.open_api_log
    WHERE create_time < '$CUTOFF'
    ORDER BY create_time
    LIMIT $BATCH;
    SELECT ROW_COUNT();
  ")
  # 2. 获取插入行数
  N=$(echo $ROWS | tail -n1)
  if [ "$N" -eq 0 ]; then
    echo 'No more rows to archive.'; break;
  fi
  # 3. 删除主表数据
  mysql -u$USER -p$PASS -h$HOST -P$PORT -e "
    DELETE FROM ${DB}.open_api_log
    WHERE create_time < '$CUTOFF'
    ORDER BY create_time
    LIMIT $BATCH;
  "
  echo "Processed batch: $N rows."
  sleep 1  # 避免对主库压力过大
done

将此脚本放到 /usr/local/bin/，并在主库所在服务器的 Crontab 中每天凌晨执行一次：

0 2 * * * /usr/local/bin/archive_open_api_log.sh >> /var/log/arch_log.log 2>&1

主从复制影响

行级操作（INSERT + DELETE）会被按批记录到 Binlog，Slave 上同样重放。
批次较小（5k），单批事务短，锁冲突和复制延迟可控。
注意：
- 确保 binlog_format=ROW（你已配置），能安全记录逐行操作；
- 脚本期间若有意外中断，脚本再次运行会跳过已归档数据（因使用 INSERT IGNORE）；
- 推荐监控 Slave 延迟：若延迟超过如 30s，暂停脚本，待延迟恢复后再继续。

补充建议

监控与告警
- 监控脚本执行成功率、主从延迟、open_api_log 表行数。
- 出现异常（如大量错误、复制错误），第一时间告警。
备份验证
- 定期对 open_api_log_his 做快照或备份，保证归档数据安全。
- 在脚本或事件里，必要时对归档批次做校验（对比行数）。
容量规划
- 如果历史表持续增长，考虑对 open_api_log_his 再做分区，按月或按年清理。
- 建议保留至少 3 个月的历史数据，具体视业务需求而定。
测试演练
- 强烈建议先在测试库上演练一次全流程（包括断网、脚本中断、DDL 回滚等场景），确认无误后再上线生产。

通过上述两种方案，你可以在不影响写业务、不损坏主从复制链路的前提下，自动化、分批或秒级地完成 7 天前数据的归档与清理。

posted @ 2025-07-01 14:21 wangwenzhe 阅读(96) 评论(0) 收藏举报

刷新页面返回顶部

Jesusloveyou

MySQL迁移表数据到历史表，并定时同步到历史表，不影响主从复制

一、基于【分区】的归档与清理（推荐）

核心思路

操作步骤

定时方案

主从复制影响

二、基于【批量行迁移】的归档脚本

核心思路

示例脚本（Shell + SQL）

主从复制影响

补充建议

公告