mysql binlog更新记录缺失_mysql 通过binlog来进行忘记where误操作(delete/update)的数据恢复...

通过binlog如何对误操作(update/delete)的数据来进行恢复

一、场景准备

1)以下数据恢复是针对delete忘记加where条件误删除恢复(binlog格式必须是row格式)

1.创建测试的数据表

mysql> create table t1(

-> id int not null auto_increment,

-> name char(20) not null,

-> sex enum('f','m') not null default 'm',

-> address varchar(30) not null,

-> primary key(id)

-> );

Query OK, 0 rows affected (0.22 sec)

2、插入测试数据

mysql> insert into t1 (name,sex,address)values('范冰冰','f','北京市朝阳区');

Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 (name,sex,address)values('赵四','m','河北省石家庄市');

Query OK, 1 row affected (0.00 sec)

3、删除数据

mysql> delete from t1;

Query OK, 2 rows affected (0.06 sec)

二、查询当前mysql的binlog文件

1、查询mysql所有的binlog文件

mysql> show master logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 177 |

| mysql-bin.000003 | 597 |

| mysql-bin.000004 | 217 |

| mysql-bin.000005 | 369 |

| mysql-bin.000006 | 3336 |

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

6 rows in set (0.00 sec)

2、查询当前mysql已经记录到哪个binlog子文件中

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000006 | 3336 | | mysql | 05abc6e5-f6c6-11e7-b4c7-00163e003b50:1,

66e6f5ed-3ee4-11e8-b871-0050562b0065:1-16 |

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

1 row in set (0.00 sec)

3、通过binlog文件来删除被恢复的数据

1)查看binlog的路径：

show variables like "log_%";

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

| Variable_name | Value |

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

| log_bin | ON |

| log_bin_basename | /home/data/mysql57/log/mysql-bin |

| log_bin_index | /home/data/mysql57/log/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_builtin_as_identified_by_password | OFF |

| log_error | /home/data/mysql57/log/mysqld.log |

| log_error_verbosity | 3 |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_slave_updates | ON |

| log_slow_admin_statements | OFF

2)通过mysqlbinlog命令打开binlog文件，并在mysql-bin.000006文件中查找“删除”记录

[root@master etc]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /home/data/mysql57/log//mysql-bin.000006 | grep -B 15 -A 15 'DELETE FROM'

# at 2971

#180413 21:23:04 server id 24 end_log_pos 3036 CRC32 0xc76df89d GTIDlast_committed=12sequence_number=13

SET @@SESSION.GTID_NEXT= '66e6f5ed-3ee4-11e8-b871-0050562b0065:16'/*!*/;

# at 3036

#180413 21:23:04 server id 24 end_log_pos 3108 CRC32 0x7b282a6f Querythread_id=3exec_time=0error_code=0

SET TIMESTAMP=1523625784/*!*/;

BEGIN

/*!*/;

# at 3108

#180413 21:23:04 server id 24 end_log_pos 3146 CRC32 0x2629a4e5 Rows_query

# delete from t1

# at 3146

#180413 21:23:04 server id 24 end_log_pos 3200 CRC32 0xd864e964 Table_map: `test`.`t1` mapped to number 244

# at 3200

#180413 21:23:04 server id 24 end_log_pos 3305 CRC32 0x2e77ce47 Delete_rows: table id 244 flags: STMT_END_F

### DELETE FROM `test`.`t1`

### WHERE

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

### @2='范冰冰' /* STRING(60) meta=65084 nullable=0 is_null=0 */

### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

### @4='北京市朝阳区' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

### DELETE FROM `test`.`t1`

### WHERE

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

### @2='赵四' /* STRING(60) meta=65084 nullable=0 is_null=0 */

### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

### @4='河北省石家庄市' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

# at 3305

#180413 21:23:04 server id 24 end_log_pos 3336 CRC32 0x8a4a7fb1 Xid = 106

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*