mysql没开启binlog恢复删除表_mysql通过binlog恢复删除数据

删除误操作有时会意外出现，如果你有备份表数据的好习惯，那么至少你可以追回备份前的那些数据。如果我们打开了mysql的binlog，那么可以通过它的增量操作日志来恢复数据。怎么打开binlog前篇已有说明(参见windows下打开binlog)，这里举例说明如何通过binlog进行恢复：

1、看下当前的binlog位置，这里称为位置1：

mysql>show master status;+---------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-binlog.000001 | 529 | | | |

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

1 row in set (0.01 sec)

2、执行DML操作，创建一个测试表，插入2条数据后误操作删除了其中一条：

mysql> create tablet_test(-> name varchar(50) default null);

Query OK,0 rows affected (0.06sec)

mysql> insert into t_test values('wlf');

Query OK,1 row affected (0.01sec)

mysql> insert into t_test values('wms');

Query OK,1 row affected (0.01sec)

mysql> delete from t_test where name = 'wlf';

Query OK,1 row affected (0.01 sec)

3、再看现在binlog的位置，这里称为位置2：

mysql>show master status;+---------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-binlog.000001 | 1496 | | | |

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

1 row in set (0.00 sec)

4、我们如果要恢复那条删除的数据，那么应该知道截至到删除前的那一刻所在的位置是多少，而这个位置必然位于位置1(即529)和2(即1496)之间：

mysql> show binlog events in 'mysql-binlog.000001' from 529;+---------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

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

| mysql-binlog.000001 | 529 | Anonymous_Gtid | 1 | 594 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-binlog.000001 | 594 | Query | 1 | 719 | use `test`; create tablet_test(

namevarchar(50) default null) |

| mysql-binlog.000001 | 719 | Anonymous_Gtid | 1 | 784 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-binlog.000001 | 784 | Query | 1 | 856 | BEGIN |

| mysql-binlog.000001 | 856 | Table_map | 1 | 907 | table_id: 109 (test.t_test) |

| mysql-binlog.000001 | 907 | Write_rows | 1 | 947 | table_id: 109 flags: STMT_END_F |

| mysql-binlog.000001 | 947 | Xid | 1 | 978 | COMMIT /*xid=11*/ |

| mysql-binlog.000001 | 978 | Anonymous_Gtid | 1 | 1043 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-binlog.000001 | 1043 | Query | 1 | 1115 | BEGIN |

| mysql-binlog.000001 | 1115 | Table_map | 1 | 1166 | table_id: 109 (test.t_test) |

| mysql-binlog.000001 | 1166 | Write_rows | 1 | 1206 | table_id: 109 flags: STMT_END_F |

| mysql-binlog.000001 | 1206 | Xid | 1 | 1237 | COMMIT /*xid=12*/ |

| mysql-binlog.000001 | 1237 | Anonymous_Gtid | 1 | 1302 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-binlog.000001 | 1302 | Query | 1 | 1374 | BEGIN |

| mysql-binlog.000001 | 1374 | Table_map | 1 | 1425 | table_id: 109 (test.t_test) |

| mysql-binlog.000001 | 1425 | Delete_rows | 1 | 1465 | table_id: 109 flags: STMT_END_F |

| mysql-binlog.000001 | 1465 | Xid | 1 | 1496 | COMMIT /*xid=13*/ |

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

17 rows in set (0.02 sec)

我们看到594到719是建表，719到978是第一条插入语句，978到1237是第二条插入语句，1237到1465是误操作删除第一条插入数据的语句。那这会儿我们已经知道增量日志用于恢复删除操作的截至位置了，很明显就是1237，那么开始位置呢？执行恢复前，我们确认下目前t_test只有一条数据：

mysql> select * fromt_test;+------+

| name |

+------+

| wms |

+------+

1 row in set (0.01 sec)

然后我们先看从594开始恢复会是啥结果：

D:\Dev\mysql\mysql-5.7.26-winx64\data>mysqlbinlog mysql-binlog.000001 --start-position 594 --stop-position 1237 | mysql -u root -p test

Enter password: *********ERROR1050 (42S01) at line 25: Table 't_test' already exists

报错了，因为t_test表我们已经存在了，重复建表当然报错了。那么恢复的开始位置我们应该跳过建表语句，可以从719开始，再来一次：

D:\Dev\mysql\mysql-5.7.26-winx64\data>mysqlbinlog mysql-binlog.000001 --start-position 719 --stop-position 1237 | mysql -u root -p test

Enter password: *********D:\Dev\mysql\mysql-5.7.26-winx64\data>

这次没有报错了，我们去数据库看下数据是否恢复了：

mysql> select * fromt_test;+------+

| name |

+------+

| wms |

| wlf |

| wms |

+------+

3 rows in set (0.00 sec)

呵呵，不仅恢复了被误删的第一条数据，还重复插入了第二条数据。怎么解决呢？有两个办法：第一个是直接把t_test也drop掉，然后执行我们的一次恢复，将执行从建表到第一、第二次插入；第二个办法是我们只恢复第一次插入，也就是只执行719到978。但如果是大量数据被我们误删了，那么就无法精确的知道该恢复从哪个位置开始到哪个位置结束的操作，没关系，我们可以根据时间来恢复，把--start-position和--stop-positon改成--start-datetime和--stop-datetime。

上述mysqlbinlog命令是在binlog日志(如例子中的mysql-binlog.000001)所在目录执行的，后面的test是我的数据库名。最后我们再来按办法一操作一次：

先drop表

mysql> drop tablet_test;

Query OK,0 rows affected (0.05 sec)

再从头594到1237恢复：

D:\Dev\mysql\mysql-5.7.26-winx64\data>mysqlbinlog mysql-binlog.000001 --start-position 594 --stop-position 1237 | mysql -u root -p test

Enter password: *********D:\Dev\mysql\mysql-5.7.26-winx64\data>

现在数据已经恢复正常：

mysql> select * fromt_test;+------+

| name |

+------+

| wlf |

| wms |

+------+

2 rows in set (0.00 sec)