mysql 误删除后通过binlog快速恢复
MySQL的delete误操作的快速恢复方法
如果我们在数据库中不小心执行了类似“delete from t1”这样的不带where条件的语句，那么整张表的数据就全被删除了，如何在最短的时间恢复被删除的数据就显得十分关键。下面来演示如何通过binlog来快速恢复表数据。
（此方法也适用于表数据被部分删除的场景）
1. 前置条件
检查mysql数据库是否开启了binlog, 查看方法如下，登录mysql执行
show variables like '%log_bin%';执行结果如下，log_bin value ON是开启状态, log_bin_basename是binlog文件存放的根目录
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql_bin |
| log_bin_index | /var/lib/mysql/mysql_bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+确定binlog的模式是行模式
select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
2. 根据误操作时间定位删除语句的binlog位置
- 进入binlog文件存放目录，根据binglog文件时间初步定位删除语句所在的binlog文件
-rw-r-----. 1 mysql mysql 107 Dec 5 10:35 mysql_bin.00002
-rw-r-----. 1 mysql mysql 567 Dec 9 17:09 mysql_bin.00003
-rw-r-----. 1 mysql mysql 693 Dec 29 11:45 mysql_bin.00004如上图mysql_bin.000073文件最接近删除时间
- 使用mysqlbinlog 命令根据时间查询binlog内容
mysqlbinlog -vv --start-datetime='2019-12-29 10:35:00' mysql_bin.00004|more
查询结果如下
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#700101 8:00:00 server id 1 end_log_pos 120 CRC32 0x199f2da4 Start: binlog v 4, server v 5.6.42-log created 700101 8:00:
00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
AAAAAA8BAAAAdAAAAHgAAAABAAQANS42LjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaQt
nxk=
'/*!*/;
# at 120
#181104 15:18:37 server id 1 end_log_pos 192 CRC32 0x2224f8de Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1541315917/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 192
#181104 15:18:37 server id 1 end_log_pos 252 CRC32 0x65fbbe3b Table_map: `test`.`person` mapped to number 85
# at 252
#181104 15:18:37 server id 1 end_log_pos 435 CRC32 0x005d1b39 Delete_rows: table id 85 flags: STMT_END_F
BINLOG '
TZ3eWxMBAAAAPAAAAPwAAAAAAFUAAAAAAAEABHRlc3QABnBlcnNvbgAGAw/+DwMDBgoA/gEKAD47
vvtl
TZ3eWyABAAAAtwAAALMBAAAAAFUAAAAAAAEAAgAG/8ABAAAAAmppATEDd2VyqEA0AGpqyADAAgAA
AANsdW8BMQN3ZXKcQjQAJ2zZAMADAAAAAnl1ATAEamVndfombwA3JyEBwAQAAAACZGEBMAdiZWlq
aW5nOgZQAFfZQADABQAAAAJnagEwB2JlaWppbmcSBlAAh+jOAMAGAAAABHlhbmcBMAduYW5qaW5n
cg5MAIdSqwo5G10A
'/*!*/;
### DELETE FROM `test`.`person`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='ji' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='1' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='wer' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=3424424 /* INT meta=0 nullable=1 is_null=0 */
### @6=13134442 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='luo' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='1' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='wer' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=3424924 /* INT meta=0 nullable=1 is_null=0 */
### @6=14248999 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='yu' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='jegu' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=7284474 /* INT meta=0 nullable=1 is_null=0 */
### @6=18949943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='da' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='beijing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=5244474 /* INT meta=0 nullable=1 is_null=0 */
### @6=4249943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='gj' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='beijing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=5244434 /* INT meta=0 nullable=1 is_null=0 */
### @6=13559943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2='yang' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='nanjing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=4984434 /* INT meta=0 nullable=1 is_null=0 */
### @6=178999943 /* INT meta=0 nullable=1 is_null=0 */
# at 435
#181104 15:18:37 server id 1 end_log_pos 466 CRC32 0xab85d971 Xid = 197
COMMIT/*!*/;
# at 466
#181104 15:19:38 server id 1 end_log_pos 538 CRC32 0x8b9e1093 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1541315978/*!*/;
BEGIN
/*!*/;
# at 538
#181104 15:19:38 server id 1 end_log_pos 598 CRC32 0xb1679f78 Table_map: `test`.`person` mapped to number 85
# at 598
#181104 15:19:38 server id 1 end_log_pos 656 CRC32 0xa5d7a2d6 Write_rows: table id 85 flags: STMT_END_F通过上面的命令可以比较方便地逐页寻找被删除的数据，我们要找到被删除的数据在binlog中的起始和终止位置点，例如上面的被删除数据位置点在192和435之间，这样我们可以保证在这两个位置之间只有我们需要的待恢复的数据，而没有其他数据。
BEGIN
/*!*/;
# at 192
....
# at 435
#181104 15:18:37 server id 1 end_log_pos 466 CRC32 0xab85d971 Xid = 197
COMMIT/*!*/;如果删除数据量太多或者mysql写入频繁，可以通过以下两个命令根据时间输出binlog的前N行和后N行，来找到起始和终止位置，这样可以大大节省时间。
mysqlbinlog -vv --start-datetime='2019-12-29 10:35:00' mysql_bin.00004| head -1000 |more
mysqlbinlog -vv --start-datetime='2019-12-29 10:35:00' --stop-datetime='2019-12-29 10:36:00' mysql_bin.00004| tail -1000 |more
3. 将binlog里的delete语句转化为insert语句
mysqlbinlog -vv --start-position=192 --stop-position=435 mysql_bin.00004 |grep ^"###" >/tmp/bin_data接下来就是处理/tmp/bin_data文本，将里面的delete语句转化为insert语句，可以通过下面的语句实现转化
cat /tmp/bin_data | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' >/tmp/person.sql
转化后确定sql语句无误，有部分出入可手动修改。
4. 将insert语句导入数据库中
Delete语句误操作只会删除表数据，而表结构还在。所以我们可以直接将文本里的insert语句导入到数据库中，即可完成数据恢复
mysql -h127.0.0.1 -P3306 -uroot -p123 < /tmp/person.sql1. 本站所有资源来源于用户上传和网络，如有侵权请及时联系删除，本站不承担任何法律责任！
2. 分享目的仅供大家学习和研究，您必须在下载后24小时内删除！
3. 不得使用于非法商业用途，不得违反国家法律。否则后果自负！
4. 本站提供的教程、源码等等其他资源，都不包含技术服务请大家谅解！
5. 如有链接无法下载、失效或广告，请联系管理员处理！
6. 本站资源售价只是赞助，收取费用仅维持本站的日常运营所需！
7. 如遇到加密压缩包，默认解压密码为"www.94zyw.com",如遇到无法解压的请联系管理员！
94资源网 » mysql 误删除后通过binlog快速恢复
2. 分享目的仅供大家学习和研究，您必须在下载后24小时内删除！
3. 不得使用于非法商业用途，不得违反国家法律。否则后果自负！
4. 本站提供的教程、源码等等其他资源，都不包含技术服务请大家谅解！
5. 如有链接无法下载、失效或广告，请联系管理员处理！
6. 本站资源售价只是赞助，收取费用仅维持本站的日常运营所需！
7. 如遇到加密压缩包，默认解压密码为"www.94zyw.com",如遇到无法解压的请联系管理员！
94资源网 » mysql 误删除后通过binlog快速恢复
