一、误删除的数据恢复
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)
