看到这个表刚开始就发生一次truncate, 那其实也可以说明我就恢复刚开始那个truncate到后来那个误操作的truncate table的语句之间的数据就是丢失的数据。
这个恢复可以从mysql-bin.000004 pos: 4到mysql-bin.000004 pos: 633 即：
| mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx |
恢复结果如下：
| mysql -S /tmp/mysql.sock re_wubx; mysql>select count(*) from tb_wubx; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.02 sec) mysql>select * from tb_wubx; +----+--------------+ | id | name | +----+--------------+ | 1 | 张三 | | 2 | 李四 | | 3 | 隔壁老王 | +----+--------------+ 3 rows in set (0.00 sec) mysql>insert into tb_wubx(name) select name from wubx.tb_wubx; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx; Query OK, 0 rows affected (0.04 sec) mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx; Query OK, 0 rows affected (0.03 sec) mysql> select * from wubx.tb_wubx; +----+--------------+ | id | name | +----+--------------+ | 1 | 张三 | | 2 | 李四 | | 3 | 隔壁老王 | | 4 | 老赵赵 | +----+--------------+ 4 rows in set (0.00 sec) mysql -S /tmp/mysql.sock re_wubx; mysql>select count(*) from tb_wubx; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.02 sec) mysql>select * from tb_wubx; +----+--------------+ | id | name | +----+--------------+ | 1 | 张三 | | 2 | 李四 | | 3 | 隔壁老王 | +----+--------------+ 3 rows in set (0.00 sec) mysql>insert into tb_wubx(name) select name from wubx.tb_wubx; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx; Query OK, 0 rows affected (0.04 sec) mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx; Query OK, 0 rows affected (0.03 sec) mysql> select * from wubx.tb_wubx; +----+--------------+ | id | name | +----+--------------+ | 1 | 张三 | | 2 | 李四 | | 3 | 隔壁老王 | | 4 | 老赵赵 | +----+--------------+ 4 rows in set (0.00 sec) |
恢复完成。
