mysql误删除数据恢复处理

谁践踏了优雅 2022-08-20 15:06 273阅读 0赞

1.事故  
后台操作权限较高人员执行错误的删除语句：mysql> delete from order where order\_id=1;  
2.事故影响  
用户看不到这个定单，且这个定单是活跃的定单  
3.是故时间  
4.恢复处理流程  
保留现场。  
mysql> delete from order where order\_id=4;  
Query OK, 1 row affected (0.00 sec)  
记录误操作语句。  
delete from order where order\_id=1;  
评估受影响数据库，表，和记录数。  
weshop\_pure,order,1行记录被删除  
拷贝最近备份文件和从备份时间到当前的binlog日志文件到测试机  
2016-02-29.3:18:48.db3.weshop\_pure.sql.gz  
mysql-bin.000039：包含从2016-02-29.3:18:48到当前的二进制日志  
查看二进制中误操作语句执行的位置点  
mysqlbinlog   ./mysql-bin.000039 >./mysqlbinlog.tmp  
view ./mysqlbinlog.tmp  
/\*!\*/;  
\# at 21729  
\#160229 11:23:03 server id 1  end\_log\_pos 21860 CRC32 0xab8e98fc        Query   thread\_id=29217 exec\_time=0     error\_code=0  
SET TIMESTAMP=1456716183/\*!\*/;  
delete from order where order\_id=4  
/\*!\*/;  
解压数据备份文件  
gunzip 2016-02-29.3:18:48.db3.weshop\_pure.sql.gz  
在测试库中执行数据备份文件，恢复数据库到初始时间点  
mysql -udba -p -h127.0.0.1 < ./2016-02-29.11:18:48.db3.weshop\_pure.sql  
执行binlog日志恢复到误操作之前  
mysqlbinlog  --stop\_position=21729 ./mysql-bin.000039 |mysql -udba -p123456 -h127.0.0.1  
查看数据是否恢复  
mysql> SELECT \* FROM \`order\`;  
\+----------+------------------+------------------+----------+--------------+----------+------------+--------------+------------+--------------+--------------+---------------+--------------+--------------+------------+-----------+---------------+--------------+------------------+-------------+--------------+------------+--------------+---------------+------------+------------+---------------+  
| order\_id | order\_sn         | pay\_sn           | store\_id | store\_name   | buyer\_id | buyer\_name | buyer\_email  | add\_time   | payment\_code | payment\_time | finnshed\_time | goods\_amount | order\_amount | rcb\_amount | pd\_amount | rebate\_amount | shipping\_fee | evaluation\_state | order\_state | refund\_state | lock\_state | delete\_state | refund\_amount | delay\_time | order\_from | shipping\_code |  
\+----------+------------------+------------------+----------+--------------+----------+------------+--------------+------------+--------------+--------------+---------------+--------------+--------------+------------+-----------+---------------+--------------+------------------+-------------+--------------+------------+--------------+---------------+------------+------------+---------------+  
|        2 | 8000000000050501 | 6005100574235001 |        1 | \*\*联盟     |      363 | crj        |\*\*\*\*\*\*3.com | 1456713480 | predeposit   |   1456713480 |    1456715975 |      1000.00 |      1000.00 |    1000.00 |      0.00 |          0.00 |         0.00 |                0 |          40 |            0 |          0 |            0 |          0.00 | 1456713523 |          1 | NULL          |  
|        3 | 8000000000050601 | 5105100600824001 |        1 | \*\*联盟     |      363 | crj        | \*\*\*\*\*\*3.com | 1456716008 | offline      |            0 |             0 |      1000.00 |      1000.00 |       0.00 |      0.00 |          0.00 |         0.00 |                0 |           0 |            0 |          0 |            0 |          0.00 |          0 |          1 |               |  
|        4 | 8000000000050701 | 9205100601392001 |        1 | \*\*联盟     |      363 | crj        | \*\*\*\*\*\*3.com | 1456716107 | predeposit   |   1456716107 |             0 |      1000.00 |      1000.00 |    1000.00 |      0.00 |          0.00 |         0.00 |                0 |          30 |            0 |          0 |            0 |          0.00 | 1456716134 |          1 | NULL          |  
\+----------+------------------+------------------+----------+--------------+----------+------------+--------------+------------+--------------+--------------+---------------+--------------+--------------+------------+-----------+---------------+--------------+------------------+-------------+--------------+------------+--------------+---------------+------------+------------+---------------+  
3 rows in set (0.00 sec)  
导出误删数据  
mysqldump -udba -p -h127.0.0.1 weshop\_pure --tables order --extended-insert=false --complete-insert --where='order\_id=4'  
......  
LOCK TABLES \`order\` WRITE;  
/\*!40000 ALTER TABLE \`order\` DISABLE KEYS \*/;  
INSERT INTO \`order\` (\`order\_id\`, \`order\_sn\`, \`pay\_sn\`, \`store\_id\`, \`store\_name\`, \`buyer\_id\`, \`buyer\_name\`, \`buyer\_email\`, \`add\_time\`, \`payment\_code\`, \`payment\_time\`, \`finnshed\_time\`, \`goods\_amount\`, \`order\_amount\`, \`rcb\_amount\`, \`pd\_amount\`, \`rebate\_amount\`, \`shipping\_fee\`, \`evaluation\_state\`, \`order\_state\`, \`refund\_state\`, \`lock\_state\`, \`delete\_state\`, \`refund\_amount\`, \`delay\_time\`, \`order\_from\`, \`shipping\_code\`) VALUES (4,8000000000050701,9205100601392001,1,'\*\*联盟',363,'crj','123E@123.com',1456716107,'predeposit',1456716107,0,1000.00,1000.00,1000.00,0.00,0.00,0.00,0,30,0,0,0,0.00,1456716134,1,NULL);  
/\*!40000 ALTER TABLE \`order\` ENABLE KEYS \*/;  
UNLOCK TABLES;  
......  
拷贝insert语句到主库上执行

检查是否还原数据

![20160303171624824][]  
5.备注  
本次案例展示了delete误删除生产数据，利用dump备份和binlog日志恢复数据，其实所有的误操作都可以通过此方式恢复  
应该严格控制数据库权限，最大限度降低误操作概率  
养成好习惯，危险操作（delete，update，DDL）之前一定要先备份数据

[20160303171624824]: /images/20220731/3ccac3913afc4bbaac7348a9d85c343c.png