mysql误删除数据恢复处理

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语句到主库上执行

检查是否还原数据

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