记一次mysql修复错误数据问题
问题描述
之前做的一个服务端配合硬件端的项目,今天突然在微信上跟我讲在管理后台订单列表里的设备编号看不见后3位数字。
原因查找
看了下数据库表发现原因是因为订单表的设备编号长度限制了16位(开发时跟硬件端开发人员约定16位长度),但是实际插入时的是19位,所以导致后3位没写入表。
问题解决
1.mysql的binlog
首先想到的是利用mysql的binlog日志来恢复正常插入的数据库
2.确定出问题数据的时间段和数量
// 查询第一条问题数据的时间点 (2019-01-02 11:41:05)
select create_time from orders where terminal_sn = '0891184753923260' limit 1;
// 查询最后一条问题数据的时间点 (2019-02-19 00:54:56)
select create_time from orders where terminal_sn = '0891184753923260' order by id desc limit 1;
// 查询错误数据量 (4331条)
select count(*) from orders where terminal_sn = '0891184753923260';
时间段为2019-01-02 11:41:05~2019-02-19 00:54:56
3.导出需要修改的数据
首先需要从binlog日志过滤出需要修改的数据,因为这次只影响了一个orders订单表,所以只需要筛选出针对这张表的增删改操作即可
// 查看当前的binlog文件
MySQL> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 | 2535552 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
// 查看binlog日志位置
MySQL> show variables like'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/mysql-bin |
| log_bin_index | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)
现在知道了当前binlog日志文件是/data/mysql/mysql-bin.000010,接下来记得使用MySQL>flush logs;刷新一下日志,这样在我们处理时不会影响线上业务
下面来导出orders表的相关sql语句到文件 待修复数据.sql
// 1.仅查看test库的binlog日志
mysqlbinlog --no-defaults --start-datetime="2019-01-02 11:40:00" --stop-datetime="2019-02-19 01:00:00" --database=test /data/mysql/mysql-bin.000010
// 2.使用管道符再次筛选出test库下orders表的sql记录并保存到文件
mysqlbinlog --no-defaults --start-datetime="2019-01-02 11:40:00" --stop-datetime="2019-02-19 01:00:00" --database=test /data/mysql/mysql-bin.000010|grep --ignore-case -E 'insert|update|delete' -A2 -B2|grep orders > 待修复数据.sql
导出后的sql文件行末没有结束符;,所以自己查找替换了一下字符串,insert替换成;insert,update替换成;update,这样sql就没有语法错误了。
数据恢复
1.把待修复数据.sql文件下载到本地,然后在本地数据库里恢复一下数据表,然后删除terminal_sn!='0891184753923260'的数据后查看orders表记录数也是4331条,说明sql语句没问题。
2.接下了我用php把拼接了一下更新的字符串,如下:
UPDATE `orders` SET `terminal_sn`= '0891184753923260001' WHERE `order_sn` = '1010211410529976';
UPDATE `orders` SET `terminal_sn`= '0891184753923260006' WHERE `order_sn` = '1010211560262172';
UPDATE `orders` SET `terminal_sn`= '0891184753923260001' WHERE `order_sn` = '1010212471941149';
最后一步
最后就是在生产环境执行更新语句就行啦O(∩_∩)O~~
