我要评论一、文档说明
本文档适用于 mysql 数据库中数据被误删（如 truncate/delete 操作）后的恢复场景，涵盖 binlog 验证、数据提取、格式转换、批量导入 全流程。
本文档以 labeems 库的 meter_data 表为例，适配物联网场景下的时序数据恢复，也可通用到其他业务表。
二、前置条件
- 服务器环境：linux（centos 8/openeuler/ubuntu 均可）
- mysql 版本：5.7+ / 8.0+
- 核心依赖：mysql 已开启 binlog（二进制日志）
- 权限要求：操作需使用
root用户，或具备select/file/super权限的数据库账户
三、核心概念
| 术语 | 说明 |
|---|---|
| binlog | mysql 二进制日志，记录所有数据修改操作，是误删恢复的核心依据 |
| row 模式 | binlog 的一种格式，记录行级数据变更，恢复精度最高，本文档默认此模式 |
mysqlbinlog | mysql 官方工具，用于解析 binlog 文件内容 |
insert ignore | 插入数据时跳过主键/唯一索引重复的行，避免恢复时的 1062 错误 |
四、恢复操作全流程
步骤1：验证 binlog 是否开启（关键前提）
1.1 登录 mysql 终端
mysql -u root -p
输入 root 密码后进入 mysql 命令行。
1.2 查询 binlog 状态
执行以下 sql 命令，确认 binlog 配置：
-- 核心查询：是否开启 binlog show variables like 'log_bin'; -- 扩展查询：binlog 存储路径、格式 show variables like '%binlog%';
- 结果解读
log_bin值为on表示已开启，off表示未开启（未开启则无法通过 binlog 恢复）log_bin_basename为 binlog 文件存储路径（如/var/lib/mysql/binlog.000032）binlog_format推荐为row，行级日志恢复精度最高
步骤2：提取 binlog 中的误删数据
2.1 解析 binlog 到文本文件
使用 mysqlbinlog 工具，筛选目标表的日志并导出为文本：
# 替换为实际的 binlog 文件路径（如 binlog.000032） mysqlbinlog --base64-output=decode-rows --verbose /var/lib/mysql/binlog.000032 | grep -a 10 -b 5 'labeems`.`meter_data' > 数据记录2.txt # 恢复指定时间前所有数据的解析指令（核心补充） # 方式1：仅按时间筛选（恢复到指定时间前的所有数据） mysqlbinlog --stop-datetime="2025-12-05 10:00:00" --base64-output=decode-rows /var/lib/mysql/binlog.000032 > /root/binlog_before_time.sql # 方式2：时间+表名双重筛选（精准恢复指定表到指定时间前的数据） mysqlbinlog --stop-datetime="2025-12-05 10:00:00" --base64-output=decode-rows --verbose /var/lib/mysql/binlog.000032 | grep -a 10 -b 5 'labeems`.`meter_data' > /var/lib/mysql/数据记录.txt # 方式3：多binlog文件按时间筛选（恢复多个文件中指定时间前的所有数据） mysqlbinlog --stop-datetime="2025-12-05 10:00:00" /var/lib/mysql/binlog.000030 /var/lib/mysql/binlog.000031 /var/lib/mysql/binlog.000032 > /root/all_binlog_before_time.sql
- 参数说明
--base64-output=decode-rows：解码 row 模式的 binlog 内容--verbose：输出详细的行数据变更记录--stop-datetime="2025-12-05 10:00:00"：核心参数，仅解析/恢复该时间点之前的所有数据（格式：yyyy-mm-dd hh:mm:ss）grep：筛选目标表labeems.meter_data的日志片段
2.2 验证解析结果
执行以下命令，查看解析后的文件是否包含有效数据行：
# 查看前 50 行内容，确认是否有 @1-@8 字段 head -50 /var/lib/mysql/数据记录.txt
- 正常结果示例
### insert into `labeems`.`meter_data` ### set ### @1=415455 ### @2='4006' ### @3=1754579818 ### @4=1754579820 ### @5=4325.81 ### @6=0.00 ### @7=0.00 ### @8=0.00
步骤3：数据格式转换（binlog → 可执行 sql）
3.1 编写转换脚本（批量生成 insert 语句）
新建脚本文件 binlog2sql.sh，用于将 binlog 文本转换为批量恢复 sql：
vim /root/binlog2sql.sh
粘贴以下内容：
#!/bin/bash
# 原始 binlog 文件
input="/var/lib/mysql/数据记录.txt"
# 最终批量 sql 文件
output="meter_data_restore.sql"
# 每2000行合并1条insert（83万条仅生成415条）
batch_size=2000
# 清空文件，写入基础配置
> $output
echo "use labeems;" >> $output
echo "set autocommit=0; begin;" >> $output
echo "set foreign_key_checks=0; set unique_checks=0;" >> $output
# 核心：提取@1-@8并批量拼接values（保留原始单引号）
awk -v bs=$batch_size '
begin {
# 初始化存储数组和计数器
vals[1] = ""; vals[2] = ""; vals[3] = ""; vals[4] = "";
vals[5] = ""; vals[6] = ""; vals[7] = ""; vals[8] = "";
count=0; # 累计行数
batch_vals=""; # 存储批量values内容
}
# 匹配@1-@8行，提取值（保留所有原始格式）
/### @1=/ { vals[1] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[1]); }
/### @2=/ { vals[2] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[2]); }
/### @3=/ { vals[3] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[3]); }
/### @4=/ { vals[4] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[4]); }
/### @5=/ { vals[5] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[5]); }
/### @6=/ { vals[6] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[6]); }
/### @7=/ { vals[7] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[7]); }
/### @8=/ {
vals[8] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[8]);
# 拼接单条values
single_val = sprintf("(%s, %s, %s, %s, %s, %s, %s, %s)",
vals[1], vals[2], vals[3], vals[4], vals[5], vals[6], vals[7], vals[8]);
# 累计到批量变量中
batch_vals = batch_vals single_val ",";
count++;
# 每batch_size行生成1条批量insert
if (count % bs == 0) {
# 去掉最后一个逗号
sub(/,$/, "", batch_vals);
# 写入批量insert（用ignore跳过重复主键）
print "insert ignore into meter_data (id, meter_code, read_time, insert_time, meter_data, i, u, p) values " batch_vals ";" >> "'"$output"'";
batch_vals=""; # 清空批量变量
}
}
# 处理最后一批不足batch_size的行
end {
if (batch_vals != "") {
sub(/,$/, "", batch_vals);
print "insert ignore into meter_data (id, meter_code, read_time, insert_time, meter_data, i, u, p) values " batch_vals ";" >> "'"$output"'";
}
}
' $input >> $output
# 写入事务结束和配置恢复
echo "commit;" >> $output
echo "set foreign_key_checks=1; set unique_checks=1;" >> $output
echo "select count(*) as '导入总行数' from meter_data;" >> $output
# 验证生成结果
insert_count=$(grep -c "insert ignore into" $output)
total_rows=$(grep -o "(" $output | wc -l)
echo "✅ 批量 sql 生成完成！文件路径：$output"
echo "📝 批量insert数量：$insert_count 条（每$batch_size行1条）"
echo "📊 预估数据总行数：$total_rows 行"
3.2 执行转换脚本
# 添加执行权限 chmod +x /root/binlog2sql.sh # 运行脚本 /root/binlog2sql.sh
执行成功后，会在 /root 目录下生成 meter_data_restore.sql 文件。
步骤4：批量导入恢复数据
4.1 执行恢复 sql
# 基础批量导入（通用） mysql -u root -p < /root/meter_data_restore.sql # 直接恢复指定时间前的binlog数据（无需生成中间sql） mysqlbinlog --stop-datetime="2025-12-05 10:00:00" /var/lib/mysql/binlog.000032 | mysql -u root -p
输入 root 密码后，等待导入完成。
4.2 验证恢复结果
执行以下命令，验证数据是否恢复成功：
# 查看恢复总行数 mysql -u root -p -e "use labeems; select count(*) as '实际恢复行数' from meter_data;" # 验证指定数据行 mysql -u root -p -e "use labeems; select id, meter_code, meter_data from meter_data where id in (415455,415456);"
- 正常结果示例
+----------------+ | 实际恢复行数 | +----------------+ | 830000 | +----------------+
步骤5：异常处理（常见错误及解决方案）
| 错误代码 | 错误描述 | 解决方案 |
|---|---|---|
| 1062 | duplicate entry ‘xxx’ for key ‘primary’ | 使用 insert ignore 替代 insert，跳过重复主键行；批量替换：sed -i 's/insert into/insert ignore into/g' 恢复.sql |
| 1136 | column count doesn’t match value count | 脚本中已增加字段值非空校验，跳过缺失值的行；定位错误行：awk -f'[(),]' '/insert/ {if(nf%8!=0) print nr,$0}' 恢复.sql |
| 1290 | –secure-file-priv option 限制 | 将 sql/csv 文件移动到 secure_file_priv 指定目录（如 /var/lib/mysql-files/） |
| 权限不足 | permission denied | 执行 chown mysql:mysql /var/lib/mysql/数据记录.txt 赋予文件权限 |
步骤6：兜底方案（逐条插入，适配极端场景）
若批量导入持续报错，改用逐条插入方式（牺牲速度，保证成功率）：
# 生成逐条 insert ignore 语句
awk '
begin {
print "use labeems; set foreign_key_checks=0; set unique_checks=0;"
vals[1] = ""; vals[2] = ""; vals[3] = ""; vals[4] = "";
vals[5] = ""; vals[6] = ""; vals[7] = ""; vals[8] = "";
}
/### @1=/ { vals[1] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[1]); }
/### @2=/ { vals[2] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[2]); }
/### @3=/ { vals[3] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[3]); }
/### @4=/ { vals[4] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[4]); }
/### @5=/ { vals[5] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[5]); }
/### @6=/ { vals[6] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[6]); }
/### @7=/ { vals[7] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[7]); }
/### @8=/ {
vals[8] = substr($0, index($0,"=")+1); gsub(/ /,"",vals[8]);
if (vals[1] != "" && vals[2] != "" && vals[3] != "" && vals[4] != "" &&
vals[5] != "" && vals[6] != "" && vals[7] != "" && vals[8] != "") {
printf "insert ignore into meter_data (id, meter_code, read_time, insert_time, meter_data, i, u, p) values (%s, %s, %s, %s, %s, %s, %s, %s);\n",
vals[1], vals[2], vals[3], vals[4], vals[5], vals[6], vals[7], vals[8];
}
}
end {
print "set foreign_key_checks=1; set unique_checks=1; select count(*) from meter_data;"
}
' /var/lib/mysql/数据记录.txt > /root/meter_data_single.sql
# 逐条导入（83 万条约 20 分钟）
mysql -u root -p < /root/meter_data_single.sql
五、预防措施（关键！避免再次误删）
开启 binlog 并配置合理策略
编辑 /etc/my.cnf 文件，添加以下配置：
[mysqld] log_bin = /var/lib/mysql/mysql-bin # 开启 binlog server_id = 1 # 必须配置，唯一值 binlog_format = row # 行模式，恢复精度最高 expire_logs_days = 7 # 自动清理 7 天前的 binlog
配置完成后，重启 mysql 服务：systemctl restart mysqld
定期备份
每日执行全量备份，结合 binlog 实现增量恢复：
# 全量备份脚本示例 mysqldump -u root -p --all-databases > /backup/mysql_full_$(date +%y%m%d).sql
限制高危操作权限
普通用户禁止授予 truncate/drop 权限，仅 dba 可执行高危操作。
操作前校验
执行 truncate/delete 前，先执行 select 确认数据范围，避免误删。
使用软删除
业务层面用 is_delete 字段替代物理删除，例如：
update meter_data set is_delete=1 where id=xxx;
六、附录
1. binlog 常用命令
# 查看所有 binlog 文件 mysql -u root -p -e "show binary logs;" # 解析指定时间范围的 binlog mysqlbinlog --start-datetime="2025-12-01 00:00:00" --stop-datetime="2025-12-10 23:59:59" /var/lib/mysql/binlog.000032 > /root/binlog_range.sql # 恢复指定时间前所有数据（直接执行） mysqlbinlog --stop-datetime="2025-12-05 10:00:00" /var/lib/mysql/binlog.000032 | mysql -u root -p # 多binlog文件按时间恢复 mysqlbinlog --stop-datetime="2025-12-05 10:00:00" /var/lib/mysql/binlog.000030 /var/lib/mysql/binlog.000031 /var/lib/mysql/binlog.000032 | mysql -u root -p
2. 数据备份/恢复常用工具
| 工具 | 说明 | 适用场景 |
|---|---|---|
mysqldump | 逻辑备份工具 | 中小型数据库全量备份 |
xtrabackup | 物理备份工具（percona） | 大型数据库增量备份 |
mysqlbinlog | binlog 解析工具 | 误删数据增量恢复/指定时间恢复 |
3. 紧急联系
若恢复过程中遇到无法解决的问题，可：
- 查看 mysql 错误日志：
tail -f /var/log/mysqld.log - 联系 dba 或数据库运维人员
- 提交 mysql 官方社区工单：https://forums.mysql.com/
七、mysql 误删数据恢复速查命令清单
1. binlog 基础操作命令
| 操作目的 | 执行命令 | 说明 |
|---|---|---|
| 查看 binlog 状态 | mysql -u root -p -e "show variables like 'log_bin%';" | 检查是否开启及存储路径 |
| 列出所有 binlog 文件 | mysql -u root -p -e "show binary logs;" | 查看文件列表及大小 |
| 解析指定 binlog | mysqlbinlog --base64-output=decode-rows --verbose 文件名 > 输出.txt | 解码为可读文本 |
| 按时间筛选解析 | mysqlbinlog --start-datetime="2025-12-01 00:00:00" --stop-datetime="2025-12-10 23:59:59" 文件名 > 输出.txt | 精准定位误删时间范围 |
| 恢复指定时间前所有数据 | `mysqlbinlog --stop-datetime=“2025-12-05 10:00:00” 文件名 | mysql -u root -p` |
| 按表筛选解析 | `mysqlbinlog 文件名 | grep -a 10 -b 5 ‘库名.表名’ > 输出.txt` |
2. 数据提取与转换命令
| 操作目的 | 执行命令 | 说明 |
|---|---|---|
| 生成批量恢复 sql | chmod +x /root/binlog2sql.sh && /root/binlog2sql.sh | 执行转换脚本生成批量 insert |
| 生成逐条恢复 sql | awk -f /root/single_sql.awk 数据记录.txt > 恢复.sql | 适配极端错误场景 |
| 检查 sql 语法正确性 | `head -100 恢复.sql | mysql -u root -p -v` |
| 统计恢复数据量 | `grep -o “(” 恢复.sql | wc -l` |
3. 数据导入与验证命令
| 操作目的 | 执行命令 | 说明 |
|---|---|---|
| 批量导入数据 | mysql -u root -p < /root/meter_data_restore.sql | 高效导入大批量数据 |
| 逐条导入数据 | mysql -u root -p < /root/meter_data_single.sql | 牺牲速度保证成功率 |
| 验证恢复总行数 | mysql -u root -p -e "use 库名; select count(*) from 表名;" | 确认整体恢复情况 |
| 验证指定数据 | mysql -u root -p -e "use 库名; select * from 表名 where id in (xxx,xxx);" | 精准校验关键数据行 |
| 检查重复数据 | mysql -u root -p -e "use 库名; select id,count(*) from 表名 group by id having count(*)>1;" | 排查主键重复问题 |
4. 异常处理命令
| 错误类型 | 执行命令 | 说明 |
|---|---|---|
| 1062 主键重复 | sed -i 's/insert into/insert ignore into/g' 恢复.sql | 全局替换跳过重复行 |
| 1136 列数不匹配 | awk -f'[(),]' '/insert/ {if(nf%8!=0) print nr,$0}' 恢复.sql | 定位错误行并修正 |
| 权限不足 | chown mysql:mysql 数据文件.txt && chmod 644 数据文件.txt | 赋予文件正确权限 |
| 查看错误日志 | tail -f /var/log/mysqld.log | 实时监控导入错误详情 |
5. 预防操作命令
| 操作目的 | 执行命令 | 说明 |
|---|---|---|
| 开启 binlog 配置 | echo -e "[mysqld]\nlog_bin=/var/lib/mysql/mysql-bin\nserver_id=1\nbinlog_format=row" >> /etc/my.cnf | 配置行模式 binlog |
| 重启 mysql 生效 | systemctl restart mysqld && systemctl enable mysqld | 重启服务并设置开机自启 |
| 全量备份数据库 | mysqldump -u root -p --all-databases > /backup/mysql_$(date +%y%m%d).sql | 每日定时执行全量备份 |
| 限制高危权限 | mysql -u root -p -e "revoke truncate,delete on *.* from '普通用户'@'localhost';" | 回收普通用户高危权限 |
以上就是mysql误删数据恢复的操作指南的详细内容，更多关于mysql误删数据恢复操作的资料请关注代码网其它相关文章！
发表评论