恢复删除的表

环境:

OS:Windows 2003

DB:Mysql 5.5

 

1.连接数据库
>mysql -h localhost -uroot -pmysql -P3306

 

2.指定数据库并创建测试表
mysql> use hxl;
Database changed
mysql> create table tb_reco_test(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into tb_reco_test values(1,'aa');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_reco_test values(1,'bb');
Query OK, 1 row affected (0.02 sec)

mysql> insert into tb_reco_test values(1,'cc');
Query OK, 1 row affected (0.00 sec)

 

3.产生第二个binlog
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tb_reco_test values(2,'dd');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_reco_test values(2,'ee');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_reco_test values(2,'ff');
Query OK, 1 row affected (0.00 sec)

 

4.产生第三个binlog
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_reco_test values(3,'jj');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_reco_test values(3,'kk');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_reco_test values(3,'hh');
Query OK, 1 row affected (0.00 sec)

 

5.删除表表并产生产生第四个binlog
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table tb_reco_test;
Query OK, 0 rows affected (0.02 sec)

6.分析前3个binlog日志
mysqlbinlog "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\master-bin.000001">c:\01.log
mysqlbinlog "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\master-bin.000002">c:\02.log
mysqlbinlog "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\master-bin.000003">c:\03.log

7.找到创建表的pos开始恢复
# at 2834122
#121226 11:25:46 server id 1  end_log_pos 2834234  Query thread_id=15 exec_time=0 error_code=0
SET TIMESTAMP=1356492346/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=33/*!*/;
create table tb_reco_test(id int,name varchar(20))

 

mysqlbinlog --start-position=2834122 --stop-position=2834986 "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\master-bin.000001" |mysql -h localhost -uroot -pmysql -P3306
mysqlbinlog --start-position=107 --stop-position=859 "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\master-bin.000002" |mysql -h localhost -uroot -pmysql -P3306
mysqlbinlog --start-position=107 --stop-position=859 "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\master-bin.000003" |mysql -h localhost -uroot -pmysql -P3306

 

8.数据已经恢复
mysql> use hxl;
Database changed
mysql> select * from tb_reco_test;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    1 | bb   |
|    1 | cc   |
|    2 | dd   |
|    2 | ee   |
|    2 | ff   |
|    3 | jj   |
|    3 | kk   |
|    3 | hh   |
+------+------+
9 rows in set (0.00 sec)

-- The End --