通过binlog恢复误删除的数据(一)

1.1 故障说明

01：生产环境上,使用delete命令删除了一条记录(数据),条件指定错球了;
02：生产环境上是开启了binlog日志的,模式为row模式;
03：需要马上进行数据的恢复;

1.2 数据库环境

## 数据库版本、binlog的配置
mysql> select @@version,@@global.log_bin,@@global.log_bin_basename,@@binlog_format;
+------------+------------------+--------------------------------------+-----------------+
| @@version  | @@global.log_bin | @@global.log_bin_basename            | @@binlog_format |
+------------+------------------+--------------------------------------+-----------------+
| 5.7.28-log |                1 | /mysql/logs/3306/binlog/21_mysql_bin | ROW             |
+------------+------------------+--------------------------------------+-----------------+
1 row in set (0.00 sec)


## binlog的其它配置
mysql> select @@global.binlog_row_image,@@binlog_rows_query_log_events;
+---------------------------+--------------------------------+
| @@global.binlog_row_image | @@binlog_rows_query_log_events |
+---------------------------+--------------------------------+
| FULL                      |                              1 |
+---------------------------+--------------------------------+
1 row in set (0.00 sec)


## 特别说明
binlog_row_image 
  # 默认值是full,针对row模式的binlog,对inset\update\delete语句进行详细的记录
  
binlog_rows_query_log_events
  # 默认值是off,针对row模式,可在线设置,若为on可以在mysql中用show binlog events in "二进制日志文件名";
  # 命令查看二进制日志文件中记录的DML语句和其它信息;


## 当前binlog日志的状态
mysql> show master status\G
*************************** 1. row ***************************
             File: 21_mysql_bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

1.3 源数据模拟

## 创建lili库,并进入到lili库下面
create database if not exists lili character set utf8 collate utf8_general_ci;
use lili;

## 创建test1表
create table if not exists test1(
  id int unsigned not null auto_increment comment"序列",
  name varchar(20) not null comment"姓名",
  sex varchar(10) not null comment"性别",
  age tinyint unsigned not null comment"年龄",
  primary key(id)
)engine=innodb character set utf8 collate utf8_general_ci comment"测试表1";

    ## 注意test1表的id字段是auto_increment(自增)的,且有主键索引;
  
## 往test1表中插入几条数据
insert into test1(name,sex,age) values
("chenliang01","男",21),
("chenliang02","男",22),
("chenliang03","男",23),
("chenliang04","男",24),
("chenliang05","男",25),
("chenliang06","男",26),
("chenliang07","男",27),
("chenliang08","男",28),
("chenliang09","男",29),
("chenliang10","男",30);
commit;

## 查看test1表中的数据
select * from test1;

1.4 故障模拟

删除test1表中id等于10的记录

mysql> select * from test1 where id=10;   # 查看test1表中id等于10的记录
+----+-------------+-----+-----+
| id | name        | sex | age |
+----+-------------+-----+-----+
| 10 | chenliang10 | 男  |  30 |
+----+-------------+-----+-----+
1 row in set (0.00 sec)

mysql> delete from test1 where id=10;      # 删除test1表中id等于10的记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1 where id=10;    # 查看test1表中id等于10的记录是否删除成功
Empty set (0.00 sec)

1.5 解决思路

01：在得知使用delete语句误删除了数据,这里是一条记录哈;需要做的事情：
    A：使用show master status;命令查看看当前binlog是哪个文件,并记录下来;
    B：执行flush log命令重新生成新的binlog日志文件来记录sql语句产生的记录;
    C：确定删除数据的命令是什么,这里是：delete from test1 where id=10;
    
02：要不要考虑锁表呢,这里就不用了,这里因为是只删除了一条数据而已;

03：对01阶段中的A步骤中的binlog日志做备份(复制一份到其它目录下,防止损坏源binlog文件);

04：在mysql中使用"show binlog events in "01阶段中A步骤记录的binlog文件名";"命令找到
    delete from test1 where id=10；这条语句(事务)的起始pos点和结束pos点;
    
05：用mysqlbinlog命令结合04阶段找到的起始pos点和结束pos点对03步骤复制的binlog文件
    进行解释并生成新的文件;
    
06：在05阶段中新生成的文件中进行处理,这里要结合到test1表的表结构来进行处理哈;  ****这里最重要了,要多想

1.6 故障处理步骤

解决思路中的01阶段

## 查看当前binlog日志文件是哪个
mysql> show master status\G
*************************** 1. row ***************************
             File: 21_mysql_bin.000001     # 当前的binlog日志文件
         Position: 1967                    # 当前binlog日志文件的pos点
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

## 使用flush logs命令重新生成新的binlog命令
mysql> show master status\G
*************************** 1. row ***************************
             File: 21_mysql_bin.000002
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

解决思路中的03阶段

[[e-mail] ~]# cp -a /mysql/logs/3306/binlog/21_mysql_bin.000001 /tmp/
[[e-mail] ~]# ll /tmp/21_mysql_bin.000001 
-rw-r----- 1 mysql mysql 2017 3月   3 19:22 /tmp/21_mysql_bin.000001

解决思路中的04阶段

最终的结果：起始pos点1699；结束pos点1967

解决思路中的05阶段

## 进入到之前备份binlog文件的目录
[[e-mail] tmp]# pwd
/tmp
[[e-mail] tmp]# ll 21_mysql_bin.000001 
-rw-r----- 1 mysql mysql 2017 3月   3 19:22 21_mysql_bin.000001

## 对binlog日志进行解释并保存到a.txt文件中
[[e-mail] tmp]# mysqlbinlog --base64-output=decode-rows -vv --start-position=1699 --stop-position=1967 21_mysql_bin.000001 >a.txt

看一看a.txt这个文件,后面对这个文件一步一步的处理

解决思路中的06阶段

## 处理a.txt文件,生成b.txt文件
[[e-mail] tmp]# sed -n '/###/'p a.txt 
### DELETE FROM `lili`.`test1`
### WHERE
###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
###   @2='chenliang10' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3='男' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @4=30 /* TINYINT meta=0 nullable=0 is_null=0 */
[[e-mail] tmp]#
[[e-mail] tmp]# sed -n '/###/'p a.txt >b.txt


## 处理b.txt文件,生成c.txt文件
[[e-mail] tmp]# sed 's/### //g' b.txt 
DELETE FROM `lili`.`test1`
WHERE
  @1=10 /* INT meta=0 nullable=0 is_null=0 */
  @2='chenliang10' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
  @3='男' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
  @4=30 /* TINYINT meta=0 nullable=0 is_null=0 */
[[e-mail] tmp]#
[[e-mail] tmp]# sed 's/### //g' b.txt  >c.txt


## 处理c.txt文件生成d.txt文件
[[e-mail] tmp]# sed 's#/.*#,#g' c.txt 
DELETE FROM `lili`.`test1`
WHERE
  @1=10 ,
  @2='chenliang10' ,
  @3='男' ,
  @4=30 , 
[[e-mail] tmp]# 
[[e-mail] tmp]# sed 's#/.*#,#g' c.txt >d.txt


## 处理d.txt文件,生成e.txt
[[e-mail] tmp]# sed -r 's#(@4.*),#\1;#g' d.txt 
DELETE FROM `lili`.`test1`
WHERE
  @1=10 ,
  @2='chenliang10' ,
  @3='男' ,
  @4=30 ;    
[[e-mail] tmp]# 
[[e-mail] tmp]# sed -r 's#(@4.*),#\1;#g' d.txt  >e.txt


## 处理e.txt文件,生成f.txt文件
[[e-mail] tmp]# sed 's#DELETE FROM `lili`.`test1`#INSERT INTO lili.test1#g' e.txt 
INSERT INTO lili.test1
WHERE
  @1=10 ,
  @2='chenliang10' ,
  @3='男' ,
  @4=30 ;
[[e-mail] tmp]#
[[e-mail] tmp]# sed 's#DELETE FROM `lili`.`test1`#INSERT INTO lili.test1#g' e.txt >f.txt


## 处理f.txt文件,生成h.txt
[[e-mail] tmp]# sed 's#WHERE#SELECT#g' f.txt 
INSERT INTO lili.test1
SELECT
  @1=10 ,
  @2='chenliang10' ,
  @3='男' ,
  @4=30 ;
[[e-mail] tmp]# sed 's#WHERE#SELECT#g' f.txt >h.txt


## 查看test1表结构后,处理h.txt文件生成i.txt文件
mysql> desc test1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |   # 自增,主键,不能有重复值
| name  | varchar(20)         | NO   |     | NULL    |                |
| sex   | varchar(10)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

[[e-mail] tmp]# sed 's#INSERT INTO lili.test1#INSERT INTO lili.test1(name,sex,age)#g' h.txt 
INSERT INTO lili.test1(name,sex,age)
SELECT
  @1=10 ,
  @2='chenliang10' ,
  @3='男' ,
  @4=30 ;
[[e-mail] tmp]# 
[[e-mail] tmp]# sed 's#INSERT INTO lili.test1#INSERT INTO lili.test1(name,sex,age)#g' h.txt  >i.txt


## 处理i.txt文件生成j.txt文件
[[e-mail] tmp]# sed '/@1=.*/'d i.txt
INSERT INTO lili.test1(name,sex,age)
SELECT
  @2='chenliang10' ,
  @3='男' ,
  @4=30 ;
[[e-mail] tmp]# sed '/@1=.*/'d i.txt >j.txt


## 处理j.txt文件,生成aa.sql
[[e-mail] tmp]# sed 's#@[1-9]=##g' j.txt 
INSERT INTO lili.test1(name,sex,age)
SELECT
  'chenliang10' ,
  '男' ,
  30 ;
[[e-mail] tmp]# 
[[e-mail] tmp]# 
[[e-mail] tmp]# sed 's#@[1-9]=##g' j.txt >aa.sql


## 在aa.sql文件的尾部追加commit;内容
[[e-mail] tmp]# echo "commit;" >>aa.sql 
[[e-mail] tmp]# cat aa.sql 
INSERT INTO lili.test1(name,sex,age)
SELECT
  'chenliang10' ,
  '男' ,
  30 ;
commit;


## 将aa.sql文件中的语句在数据库中进行执行
mysql> INSERT INTO lili.test1(name,sex,age)
    -> SELECT
    ->   'chenliang10' ,
    ->   '男' ,
    ->   30 ;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lili.test1 where name="chenliang10";
+----+-------------+-----+-----+
| id | name        | sex | age |
+----+-------------+-----+-----+
| 11 | chenliang10 | 男  |  30 |
+----+-------------+-----+-----+
1 row in set (0.00 sec)