Mysql通过mysqlbinlog工具恢复误删除数据

一、误删除的数据恢复

1、配置/etc/my.cnf,添加如下参数打开binlog

server_id=181

log_bin=binlog

binlog_format=ROW

2、创建表及测试数据

mysql> create table test(id int(4),name varchar(22));

Query OK, 0 rows affected (0.07 sec)

insert into test values(1,'a');

insert into test values(2,'c');

insert into test values(3,'d');

mysql> select * from test;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | c    |

|    3 | d    |

+------+------+

3 rows in set (0.00 sec)

3、现在将表数据及表删除

mysql> delete from test;

Query OK, 3 rows affected (0.02 sec)

mysql> drop table test;

Query OK, 0 rows affected (0.04 sec)

4、挖掘binlog日志

报错：

[root@ttt data]# mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /mysql/data/binlog.000001 >kkk.sql

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 31, event_type: 35

解决：指向绝对路径可以

/mysql/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /mysql/data/binlog.000001 >kkk.sql

binlog日志:

# at 219

#171016 22:49:16 server id 181  end_log_pos 336 CRC32 0x379b4da4 Query thread_id=3 exec_time=0 error_cod

e=0

use `bhs`/*!*/;

SET TIMESTAMP=1508165356/*!*/;

SET @@session.pseudo_thread_id=3/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

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/*!*/;

create table test(id int(4),name varchar(22))

/*!*/;

# at 336

#171016 22:50:16 server id 181  end_log_pos 401 CRC32 0xbd9a58a5 Anonymous_GTID last_committed=1 sequence_

number=2

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 401

#171016 22:50:16 server id 181  end_log_pos 472 CRC32 0x1a8c6154 Query thread_id=3 exec_time=0 error_cod

e=0

SET TIMESTAMP=1508165416/*!*/;

BEGIN

/*!*/;

# at 472

#171016 22:50:16 server id 181  end_log_pos 521 CRC32 0x99399010 Table_map: `bhs`.`test` mapped to number 219

# at 521

#171016 22:50:16 server id 181  end_log_pos 563 CRC32 0xb3366604 Write_rows: table id 219 flags: STMT_END_F

### INSERT INTO `bhs`.`test`

### SET

###   @1=1 /* INT meta=0 nullable=1 is_null=0 */

###   @2='a' /* VARSTRING(22) meta=22 nullable=1 is_null=0 */

# at 563

#171016 22:50:16 server id 181  end_log_pos 594 CRC32 0xb69dc2ef Xid = 10

COMMIT/*!*/;

# at 594

#171016 22:50:16 server id 181  end_log_pos 659 CRC32 0x981028de Anonymous_GTID last_committed=2 sequence_

number=3

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 659

#171016 22:50:16 server id 181  end_log_pos 730 CRC32 0x9aaee2fb Query thread_id=3 exec_time=0 error_cod

e=0

SET TIMESTAMP=1508165416/*!*/;

BEGIN

/*!*/;

# at 730

#171016 22:50:16 server id 181  end_log_pos 779 CRC32 0xaf1b75d4 Table_map: `bhs`.`test` mapped to number 219

# at 779

#171016 22:50:16 server id 181  end_log_pos 821 CRC32 0x31527c07 Write_rows: table id 219 flags: STMT_END_F

### INSERT INTO `bhs`.`test`

### SET

###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

###   @2='c' /* VARSTRING(22) meta=22 nullable=1 is_null=0 */

# at 821

#171016 22:50:16 server id 181  end_log_pos 852 CRC32 0x5f635042 Xid = 11

COMMIT/*!*/;

# at 852

#171016 22:50:21 server id 181  end_log_pos 917 CRC32 0x89e9ae79 Anonymous_GTID last_committed=3 sequence_

number=4

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 917

#171016 22:50:21 server id 181  end_log_pos 988 CRC32 0x347ed8f5 Query thread_id=3 exec_time=0 error_cod

e=0

SET TIMESTAMP=1508165421/*!*/;

BEGIN

/*!*/;

# at 988

#171016 22:50:21 server id 181  end_log_pos 1037 CRC32 0x8ebe7b63 Table_map: `bhs`.`test` mapped to number 219

# at 1037

#171016 22:50:21 server id 181  end_log_pos 1079 CRC32 0x132c2b82 Write_rows: table id 219 flags: STMT_END_F

### INSERT INTO `bhs`.`test`

### SET

###   @1=3 /* INT meta=0 nullable=1 is_null=0 */

###   @2='d' /* VARSTRING(22) meta=22 nullable=1 is_null=0 */

# at 1079

#171016 22:50:21 server id 181  end_log_pos 1110 CRC32 0xc676ebaa Xid = 12

COMMIT/*!*/;

# at 1110

#171016 22:52:10 server id 181  end_log_pos 1175 CRC32 0x2c454196 Anonymous_GTID last_committed=4 sequence_

number=5

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 1175

#171016 22:52:10 server id 181  end_log_pos 1246 CRC32 0xd494ad64 Query thread_id=5 exec_time=0 error_cod

e=0

SET TIMESTAMP=1508165530/*!*/;

BEGIN

/*!*/;

# at 1246

#171016 22:52:10 server id 181  end_log_pos 1295 CRC32 0x3bddb222 Table_map: `bhs`.`test` mapped to number 219

# at 1295

#171016 22:52:10 server id 181  end_log_pos 1351 CRC32 0x26842168 Delete_rows: table id 219 flags: STMT_END_F

### DELETE FROM `bhs`.`test`

5、开始恢复删除数据及表，挖掘binlog日志找到创建表的位置及delete删除前的位置，通过mysqlbinlog命令在数据库内重放

mysqlbinlog --start-position=219 --stop-position=1246 /mysql/data/binlog.000001 |mysql -uroot -p bhs

6、查看验证：数据已成功恢复

mysql> use bhs;

Database changed

mysql> show tables;

+---------------+

| Tables_in_bhs |

+---------------+

| test          |

+---------------+

1 row in set (0.00 sec)

mysql> 

mysql> select * from test;

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | c    |

|    3 | d    |

+------+------+

3 rows in set (0.00 sec)