mysql表truncate恢复

TRUNCATE 的危险性

TRUNCATE TABLE 是 MySQL 中最危险的操作之一。它会瞬间清空整张表的所有数据，而且不像 DELETE 那样逐行删除并记录每行的 undo log。一旦执行，常规的事务回滚（ROLLBACK）是无法恢复的。

但别慌，如果你开启了 binlog，数据还是有救的。

恢复原理

正常写入数据 -----> TRUNCATE TABLE -----> 数据清空
     |                    |
     v                    v
  binlog 记录了         binlog 也记录了
  所有 INSERT/UPDATE    TRUNCATE 语句
     |
     v
  重放 binlog 到 TRUNCATE 之前的位置 = 恢复数据

核心思路：从 binlog 中提取该表的所有写操作（INSERT/UPDATE/DELETE），重放到 TRUNCATE 发生之前的时间点。

恢复步骤

第一步：确认 binlog 是否开启

SHOW VARIABLES LIKE 'log_bin';
-- 必须是 ON

SHOW VARIABLES LIKE 'binlog_format';
-- ROW 格式最容易恢复，STATEMENT 格式也可以

第二步：找到相关的 binlog 文件

# 查看所有 binlog 文件
mysql -uroot -p -e "SHOW BINARY LOGS;"

# 找到 TRUNCATE 发生的时间点对应的 binlog
# 假设 TRUNCATE 发生在 2025-03-02 15:30
mysqlbinlog --start-datetime="2025-03-02 15:00:00" \
            --stop-datetime="2025-03-02 16:00:00" \
            /var/lib/mysql/mysql-bin.000123 | grep -i "truncate"

第三步：确定恢复的时间范围

# 找到 TRUNCATE 的精确位置（position）
mysqlbinlog --start-datetime="2025-03-02 15:25:00" \
            --stop-datetime="2025-03-02 15:35:00" \
            -v /var/lib/mysql/mysql-bin.000123 | grep -B5 -i "truncate"

# 输出类似：
# #250302 15:30:15 server id 1  end_log_pos 123456
# TRUNCATE TABLE your_table

第四步：导出 TRUNCATE 之前的数据

方法 A：如果有全量备份 + binlog

# 1. 先恢复最近的全量备份到临时库
mysql -uroot -p temp_db < full_backup.sql

# 2. 重放 binlog 到 TRUNCATE 之前的位置
mysqlbinlog --stop-position=123455 \
            /var/lib/mysql/mysql-bin.000123 \
            | mysql -uroot -p temp_db

# 3. 从临时库导出恢复的表
mysqldump -uroot -p temp_db your_table > recovered_table.sql

# 4. 导入到正式库
mysql -uroot -p your_db < recovered_table.sql

方法 B：只有 binlog（没有全量备份）

# 从最早的 binlog 开始，重放到 TRUNCATE 之前
# 先找到表创建以来的所有 binlog
mysqlbinlog --database=your_db \
            --stop-position=123455 \
            mysql-bin.000100 mysql-bin.000101 ... mysql-bin.000123 \
            | mysql -uroot -p temp_db

第五步：用 Python 从 binlog 提取数据（ROW 格式）

如果 binlog 是 ROW 格式，可以用 mysqlbinlog -v 解析出具体的行数据：

import subprocess
import re

# 解析 binlog 中的 INSERT 语句
cmd = [
    'mysqlbinlog', '-v', '--base64-output=DECODE-ROWS',
    '--database=your_db',
    '--stop-datetime=2025-03-02 15:30:00',
    '/var/lib/mysql/mysql-bin.000123'
]

output = subprocess.check_output(cmd, text=True)

# 提取 INSERT INTO your_table 的行
inserts = []
for line in output.split('\n'):
    if 'your_table' in line and 'INSERT' in line.upper():
        inserts.append(line)

# 写入恢复 SQL
with open('recover.sql', 'w') as f:
    for sql in inserts:
        f.write(sql + ';\n')

print(f"提取了 {len(inserts)} 条 INSERT 语句")

恢复流程图

发现 TRUNCATE 误操作
        |
        v
确认 binlog 开启? --否--> 无法恢复，只能找备份
        |
       是
        v
找到 TRUNCATE 的 binlog 位置(position)
        |
        v
有全量备份? --是--> 恢复备份 + 重放 binlog 到 TRUNCATE 前
        |
       否
        v
从最早 binlog 重放到 TRUNCATE 前（耗时长）
        |
        v
数据恢复到临时库
        |
        v
验证数据正确性
        |
        v
导入正式库

预防措施

措施	说明
开启 binlog	log_bin = ON，这是恢复的前提
使用 ROW 格式	binlog_format = ROW，记录每行变更，恢复最方便
定期全量备份	mysqldump 或 xtrabackup，配合 binlog 做增量恢复
权限控制	普通用户不给 DROP/TRUNCATE 权限
SQL 审计	生产环境 DDL 操作走审批流程
延迟从库	设置一个延迟 1 小时的从库，误操作后有缓冲时间

TRUNCATE vs DELETE 的区别

特性	TRUNCATE	DELETE
速度	极快（直接释放数据页）	慢（逐行删除）
事务回滚	不支持	支持
binlog 记录	记录一条 TRUNCATE 语句	记录每行 DELETE
自增 ID	重置为 1	不重置
触发器	不触发	触发 DELETE 触发器
WHERE 条件	不支持	支持

总结

TRUNCATE 恢复的关键在于 binlog。平时做好三件事：开启 binlog（ROW 格式）、定期全量备份、控制好权限。真出事了，冷静找 binlog 位置，重放到误操作之前即可。最好的恢复方案永远是"不需要恢复"--做好预防。