Mysql-经典实战案例（10）：如何用PT-Archiver完成大表的自动归档
真实痛点：电商订单表存储优化场景
现状分析
某电商平台订单表（order_info）每月新增500万条记录
- 主库：高频读写，SSD存储（空间告急）
- 历史库：HDD存储，只读查询
优化目标
- ✅ 自动迁移7天前的订单到历史库
- ✅ 每周六23:30执行，不影响业务高峰
- ✅ 确保数据一致性
第一章：前期准备：沙盒实验室搭建
1.1 实验环境架构
生产库：10.33.112.22
历史库：10.30.76.4
1.2 环境初始化（双节点执行）
# 主库建表
CREATE TABLE order_info (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) UNIQUE,
amount DECIMAL(10,2),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_create_time(create_time)
) ENGINE=InnoDB;
# 需要在历史库建表（保持相同结构）
CREATE TABLE order_archive (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) UNIQUE,
amount DECIMAL(10,2),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_create_time(create_time)
) ENGINE=InnoDB;
第二章：数据搬迁实战
2.1 模拟数据生成（快速生成30天数据）
# 登录主库执行
DELIMITER $$
CREATE PROCEDURE generate_orders()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 50000 DO
INSERT INTO order_info(order_no, amount, create_time)
VALUES (
CONCAT('NO', DATE_FORMAT(NOW(),'%Y%m%d'), LPAD(i,6,'0')),
ROUND(RAND()*1000,2),
DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*30) DAY)
);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL generate_orders(); -- 执行存储过程
DROP PROCEDURE generate_orders;
数据验证：
SELECT
COUNT(*) AS total,
MIN(create_time) AS earliest,
MAX(create_time) AS latest
FROM order_info;
+-------+---------------------+---------------------+
| total | earliest | latest |
+-------+---------------------+---------------------+
| 50000 | 2025-02-19 16:24:17 | 2025-03-20 16:34:00 |
+-------+---------------------+---------------------+
2.2 PT-Archiver手动搬迁示范
./pt-archiver \
--source h=10.33.112.22,D=pt,t=order_info,u=root,p=密码 \
--dest h=10.30.76.4,D=pt,t=order_archive,u=root,p=密码 \
--where "create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)" \
--progress 1000 \
--bulk-delete \
--limit 5000 \
--no-check-charset \
--nosafe-auto-increment \
--commit-each
-
参数解释（参数 | 说明）：
–source | 指定源数据库连接信息（IP/库名/表名/账号密码）
–dest | 指定目标数据库连接信息（IP/库名/表名/账号密码）
–where | 数据筛选条件（删除7天前的数据）
–progress | 每处理1000行输出进度
–bulk-delete | 启用批量删除模式（代替逐行删除）
–limit | 每批处理5000条数据
–no-check-charset | 跳过字符集一致性检查
–nosafe-auto-increment | 禁用自增主键安全校验 （避免漏掉最后一行数据）
–commit-each | 逐行提交事务（默认批量提交）
2.3 迁移效果验证
主库查询：
SELECT
COUNT(*) AS total,
MIN(create_time) AS earliest,
MAX(create_time) AS latest
FROM order_info;
+-------+---------------------+---------------------+
| total | earliest | latest |
+-------+---------------------+---------------------+
| 11638 | 2025-03-15 11:16:51 | 2025-03-21 11:25:56 |
+-------+---------------------+---------------------+
历史库验证：
SELECT
COUNT(*) AS total,
MIN(create_time) AS earliest,
MAX(create_time) AS latest
FROM order_archive;
+-------+---------------------+---------------------+
| total | earliest | latest |
+-------+---------------------+---------------------+
| 38362 | 2025-02-20 11:16:51 | 2025-03-14 11:25:55 |
+-------+---------------------+---------------------+
11638+38362=50000，无误。迁移成功！
第三章：无人值守自动化方案
接下来我们要做成每周某，定时自动迁移
3.1 自动化配置
vim /scripts/archive_orders.sh
#!/bin/bash
LOG_FILE="/var/log/archive_$(date +%Y%m%d).log"
/opt/percona-toolkit-3.6.0/bin/pt-archiver \
--source h=10.33.112.22,D=pt,t=order_info,u=root,p=密码 \
--dest h=10.30.76.4,D=pt,t=order_archive,u=root,p=密码 \
--where "create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)" \
--progress 1000 \
--bulk-delete \
--limit 5000 \
--purge \
--no-check-charset \
--nosafe-auto-increment \
--commit-each >> ${LOG_FILE} 2>&1
授权执行：
chmod +x /scripts/archive_orders.sh
3.3 配置定时任务
crontab -e
# 每周六23:30执行
30 23 * * 6 /bin/bash /scripts/archive_orders.sh
关键检查项：
- 确保
pt-archiver在PATH中 - 定时任务用户有权限访问数据库
- 日志目录提前创建
结语：解放人力的最后一步
大功告成！此时生产数据库：
✅ 主库始终保持轻量级状态
✅ 历史查询不再影响核心业务
✅ 自动归档策略稳定运行
现在就去为你的数据库实施这套方案吧！