MySQL历史数据迁移与归档实战：从SQL脚本到Pt-archiver的多种方案

MySQL迁移指定表历史数据的多种方案

在业务发展过程中，MySQL单表数据量往往会随着时间推移急剧膨胀，当单表数据量达到千万甚至亿级别时，数据库的查询性能、写入吞吐量以及日常运维（如DDL变更、备份恢复）都会面临巨大挑战。此时，将历史数据（冷数据）从在线表（热数据）中迁移出去，成为架构优化的必经之路。

本文将详细梳理MySQL迁移指定表历史数据的多种方案，从传统的SQL脚本到专业的开源工具，再到架构层面的优化，帮助开发者根据实际业务场景选择最合适的迁移策略。

一、业务前置准备与核心原则

在执行任何数据迁移之前，必须明确以下原则：

• 明确时间界限： 严格界定“历史数据”的阈值（如半年前、一年前），避免误删在线热数据。

• 先备后迁： 迁移前务必做好完整的数据备份。

• 避免大事务： 一次性删除或插入百万级数据会导致锁表、主从延迟甚至数据库OOM，必须分批（Chunk）执行。

• 验证机制： 迁移后需校验源表与目标表的数据条数或抽样校验数据一致性。

方案一：分批 INSERT INTO ... SELECT + DELETE（纯SQL脚本）

这是最基础、最轻量的方案，无需引入外部工具，适合数据量中等（百万级以内）且允许短暂业务停机或低峰期操作的场景。

实现思路： 利用主键ID或时间索引，将大事务拆分为小事务，循环执行插入与删除。

-- 1. 在目标库创建相同结构的归档表（建议只保留必要索引以加速插入）
CREATE TABLE archive_db.target_table LIKE source_db.source_table;

-- 2. 分批迁移数据（以主键ID范围分片，每次处理10000条）
INSERT INTO archive_db.target_table 
SELECT * FROM source_db.source_table 
WHERE create_time < '2023-01-01' 
AND id >= ? AND id < ? 
LIMIT 10000;

-- 3. 分批删除源表已迁移数据
DELETE FROM source_db.source_table 
WHERE create_time < '2023-01-01' 
AND id >= ? AND id < ? 
LIMIT 10000;

优缺点分析：

• 优点： 无需额外工具，逻辑简单，开箱即用。

• 缺点： 需要手动编写循环脚本控制分批逻辑；DELETE操作会产生大量Binlog，造成主从延迟；若中途失败，断点续传逻辑需要自行实现。

方案二：mysqldump + source + 清理（逻辑备份方式）

当数据量达到数百万甚至上千万时，纯SQL的INSERT效率会下降。可以借助MySQL原生的逻辑备份工具，将数据导出为SQL文件，再导入归档库。

# 1. 导出指定条件的历史数据（--where参数过滤）
mysqldump -h www.ipipp.com -u root -p source_db source_table 
  --where="create_time < '2023-01-01'" 
  --no-create-info 
  --skip-add-locks 
  --single-transaction > archive_data.sql

# 2. 将数据导入归档库
mysql -h www.ipipp.com -u root -p archive_db < archive_data.sql

关键步骤： 导入成功并验证后，仍需使用方案一中的分批DELETE逻辑清理源表数据。

优缺点分析：

• 优点： mysqldump导出效率较高，--single-transaction可保证数据一致性快照，不锁全表。

• 缺点： 导出的SQL文件可能非常庞大，导入时耗较长；DELETE源表数据的痛点依然存在；网络传输开销大。

方案三：pt-archiver（Percona工具集推荐方案）

pt-archiver 是Percona Toolkit中专门用于清理和归档历史数据的利器，是业界公认的大表数据归档最佳实践。

它底层通过分批低频次的INSERT和DELETE小事务实现，能够精准控制对线上数据库的冲击，且自带断点续传和延迟监控机制。

pt-archiver 
  --source h=www.ipipp.com,D=source_db,t=source_table,u=root,p=password 
  --dest h=www.ipipp.com,D=archive_db,t=archive_table,u=root,p=password 
  --where "create_time < '2023-01-01'" 
  --limit 1000 
  --commit-each 
  --progress 1000 
  --statistics 
  --no-delete

参数解析：

• --limit 1000：每次处理1000条记录，避免大事务。

• --commit-each：每次分批处理完毕后立即提交，减少锁持有时间。

• --no-delete：仅归档不删除（建议首次执行先只归档，验证无误后再去掉此参数执行删除）。

• --max-lag：可设置主从延迟阈值，超过延迟自动暂停，保护线上业务。

优缺点分析：

• 优点： 安全性极高，对线上业务几乎零影响；自动分批、支持断点续传；可监控主从延迟；效率比纯手写SQL高。

• 缺点： 需要安装Perl运行环境及Percona工具集；由于是逐行删除，依然会产生大量Binlog。

方案四：表分区（Partition）快速脱落

如果业务能提前规划，或者源表恰好适合按时间分区，那么使用分区表的EXCHANGE PARTITION或DROP PARTITION是效率最高的方式，物理级别操作，瞬间完成。

-- 假设源表已按RANGE按月分区，p2022包含了2022年的历史数据

-- 1. 创建一个与源表结构相同的空归档表（不能有主键/唯一键冲突限制，需适配）
CREATE TABLE archive_db.table_2022 LIKE source_db.source_table;
ALTER TABLE archive_db.table_2022 REMOVE PARTITIONING;

-- 2. 交换分区：将源表的p2022分区数据瞬间交换到归档表（元数据级别的替换，秒级完成）
ALTER TABLE source_db.source_table EXCHANGE PARTITION p2022 WITH TABLE archive_db.table_2022;

-- 3. 删除源表已空的分区定义（非必须，但能保持分区定义整洁）
ALTER TABLE source_db.source_table DROP PARTITION p2022;

优缺点分析：

• 优点： 速度极快（毫秒/秒级完成），不产生Binlog（针对DROP PARTITION），无主从延迟风险。

• 缺点： 改造成本高，需要将普通表转换为分区表；MySQL分区表有诸多限制（如唯一键必须包含分区键）；需要提前规划好分区策略。

方案五：Binlog实时同步（如Canal）渐进式归档

对于7x24小时不间断写入的核心业务，任何直接在源库执行批量DELETE的行为都存在风险。可以采用实时同步架构：

1. 通过Canal等中间件监听源库的Binlog。

2. 将增量数据实时写入归档库。

3. 在线上低峰期，通过应用程序层面将历史数据的查询路由切换至归档库。

4. 路由切换完毕并观察无异常后，再通过后台任务极其缓慢地清理源表数据。

优缺点分析：

• 优点： 业务完全无感知，平滑过渡，零停机，零风险。

• 缺点： 架构复杂度极高，需要维护Canal等同步链路；开发改造成本大。

总结与选型建议

数据迁移没有银弹，需要根据数据量、业务容忍度、技术成本综合考量：

• 数据量小（<百万）且允许停机： 选择方案一（纯SQL分批），简单直接。

• 数据量中等（百万~千万）且在线业务要求高： 选择方案三（pt-archiver），安全可靠，DBA必备。

• 新业务或可重构表结构： 选择方案四（表分区），一劳永逸，后续归档只需一条DDL命令。

• 超核心业务不容丝毫抖动： 选择方案五（Binlog同步+应用路由切换），用架构复杂度换取极致的可用性。

在进行历史数据清理时，务必牢记“慢即是快”，控制好每批数据的处理量与执行频率，确保线上数据库的稳定运行始终放在第一位。

MySQL数据迁移历史数据归档pt-archiver表分区Binlog同步