binlog恢复被删除数据

1.查看当前数据库的bin-log是否开启，如果没有开启，就恢复不了，需要去开启binlog

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.02 sec)

mysql> 

2.查看当前binlog名称

mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| LAPTOP-HOGFIMN0-bin.000090 |     1231 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

mysql> 

3.查询binlog详情

mysql>  show binlog events in 'LAPTOP-HOGFIMN0-bin.000090';
+----------------------------+------+----------------+-----------+-------------+--------------------------------------------------+
| Log_name                   | Pos  | Event_type     | Server_id | End_log_pos | Info                                             |
+----------------------------+------+----------------+-----------+-------------+--------------------------------------------------+
| LAPTOP-HOGFIMN0-bin.000090 |    4 | Format_desc    |         1 |         124 | Server ver: 8.0.18, Binlog ver: 4                |
| LAPTOP-HOGFIMN0-bin.000090 |  124 | Previous_gtids |         1 |         155 |                                                  |
| LAPTOP-HOGFIMN0-bin.000090 |  155 | Anonymous_Gtid |         1 |         232 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| LAPTOP-HOGFIMN0-bin.000090 |  232 | Query          |         1 |         334 | use `workdb`; truncate table testmm /* xid=74 */ |
| LAPTOP-HOGFIMN0-bin.000090 |  334 | Anonymous_Gtid |         1 |         413 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| LAPTOP-HOGFIMN0-bin.000090 |  413 | Query          |         1 |         490 | BEGIN                                            |
| LAPTOP-HOGFIMN0-bin.000090 |  490 | Table_map      |         1 |         554 | table_id: 87 (workdb.testmm)                     |
| LAPTOP-HOGFIMN0-bin.000090 |  554 | Write_rows     |         1 |         600 | table_id: 87 flags: STMT_END_F                   |
| LAPTOP-HOGFIMN0-bin.000090 |  600 | Xid            |         1 |         631 | COMMIT /* xid=81 */                              |
| LAPTOP-HOGFIMN0-bin.000090 |  631 | Anonymous_Gtid |         1 |         710 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| LAPTOP-HOGFIMN0-bin.000090 |  710 | Query          |         1 |         787 | BEGIN                                            |
| LAPTOP-HOGFIMN0-bin.000090 |  787 | Table_map      |         1 |         853 | table_id: 97 (workdb.customer)                   |
| LAPTOP-HOGFIMN0-bin.000090 |  853 | Delete_rows    |         1 |         901 | table_id: 97 flags: STMT_END_F                   |
| LAPTOP-HOGFIMN0-bin.000090 |  901 | Xid            |         1 |         932 | COMMIT /* xid=381 */                             |
| LAPTOP-HOGFIMN0-bin.000090 |  932 | Anonymous_Gtid |         1 |        1011 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| LAPTOP-HOGFIMN0-bin.000090 | 1011 | Query          |         1 |        1088 | BEGIN                                            |
| LAPTOP-HOGFIMN0-bin.000090 | 1088 | Table_map      |         1 |        1154 | table_id: 97 (workdb.customer)                   |
| LAPTOP-HOGFIMN0-bin.000090 | 1154 | Delete_rows    |         1 |        1200 | table_id: 97 flags: STMT_END_F                   |
| LAPTOP-HOGFIMN0-bin.000090 | 1200 | Xid            |         1 |        1231 | COMMIT /* xid=382 */                             |
| LAPTOP-HOGFIMN0-bin.000090 | 1231 | Anonymous_Gtid |         1 |        1310 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| LAPTOP-HOGFIMN0-bin.000090 | 1310 | Query          |         1 |        1387 | BEGIN                                            |
| LAPTOP-HOGFIMN0-bin.000090 | 1387 | Table_map      |         1 |        1453 | table_id: 97 (workdb.customer)                   |
| LAPTOP-HOGFIMN0-bin.000090 | 1453 | Write_rows     |         1 |        1504 | table_id: 97 flags: STMT_END_F                   |
| LAPTOP-HOGFIMN0-bin.000090 | 1504 | Xid            |         1 |        1535 | COMMIT /* xid=511 */                             |
| LAPTOP-HOGFIMN0-bin.000090 | 1535 | Anonymous_Gtid |         1 |        1614 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'             |
| LAPTOP-HOGFIMN0-bin.000090 | 1614 | Query          |         1 |        1691 | BEGIN                                            |
| LAPTOP-HOGFIMN0-bin.000090 | 1691 | Table_map      |         1 |        1757 | table_id: 97 (workdb.customer)                   |
| LAPTOP-HOGFIMN0-bin.000090 | 1757 | Delete_rows    |         1 |        1808 | table_id: 97 flags: STMT_END_F                   |
| LAPTOP-HOGFIMN0-bin.000090 | 1808 | Xid            |         1 |        1839 | COMMIT /* xid=520 */                             |
+----------------------------+------+----------------+-----------+-------------+--------------------------------------------------+
29 rows in set (0.09 sec)

mysql>

4.找出删除操作的开始结束位置，可以从3看出Write_rows 是从1387到1691，所以恢复数据就得从这期间的操作日志操作，将文件导出到sql文件

mysqlbinlog LAPTOP-HOGFIMN0-bin.000090 --start-position=1387 --stop-position=1691>test.sql

然后登录数据库 mysql -u root -p

source C:\ProgramData\MySQL\MySQL Server 8.0\Data\test.sql

恢复删除数据