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位置

  1. 进入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文件最接近删除时间

  1. 使用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.sql
1. 本站所有资源来源于用户上传和网络,如有侵权请及时联系删除,本站不承担任何法律责任!
2. 分享目的仅供大家学习和研究,您必须在下载后24小时内删除!
3. 不得使用于非法商业用途,不得违反国家法律。否则后果自负!
4. 本站提供的教程、源码等等其他资源,都不包含技术服务请大家谅解!
5. 如有链接无法下载、失效或广告,请联系管理员处理!
6. 本站资源售价只是赞助,收取费用仅维持本站的日常运营所需!
7. 如遇到加密压缩包,默认解压密码为"www.94zyw.com",如遇到无法解压的请联系管理员!
94资源网 » mysql 误删除后通过binlog快速恢复

发表评论