从全备份的SQL语句中恢复某张表 [原创]

今天有朋友问,如何从如何从全备份的SQL语句中恢复某张表，比如我我删除了一张表，我现在只想恢复这一张表.
做以下测试,但结果不是很理想.

目前认为,这样的恢复要分两种情况.

1.要恢复的表在导出的时候是第一个被导出的表
在这种情况下,直接导入就可以了,其他表已存在,不会导入
mysql> use db1;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
+---------------+
3 rows in set (0.00 sec)
mysql> select * from t1; select * from t2; select * from t3;
+------+-------+
| id   | name  |
+------+-------+
|    1 | yxyup |
|    1 | yxyup |
+------+-------+
2 rows in set (0.00 sec)

+------+-------+
| id   | name  |
+------+-------+
|    1 | yxyup |
|    1 | yxyup |
+------+-------+
2 rows in set (0.00 sec)

+------+-------+
| id   | name  |
+------+-------+
|    1 | yxyup |
|    1 | yxyup |
+------+-------+
2 rows in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t2            |
| t3            |
+---------------+
2 rows in set (0.00 sec)

[mysql@QANEW mysql]$ mysql -uroot -pabc123 -D db1 < db1.dump
ERROR 1050 at line 28: [color=Red]Table 't2' already exists[/color]

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
+---------------+
3 rows in set (0.00 sec)

2. 如果在恢复的表,不是第一个被导出的,那么是没有办法被恢复的(至少我现在不知道恢复).
我一般采用的恢复方式是.将mysqldump导出的dump文件进行抽表,把要恢复的表结构和记录抽取出来.

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
+---------------+
3 rows in set (0.00 sec)

mysql> drop table t3;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
+---------------+
2 rows in set (0.00 sec)

[mysql@QANEW mysql]$ mysql -uroot -pabc123 -D db1 < db1.dump
ERROR 1050 at line 11: Table 't1' already exists

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
+---------------+
2 rows in set (0.00 sec)

----可以看出如果不是第一个被导出的表是无法恢复的.

用抽取方法试一下

[mysql@QANEW mysql]$ vi db1_t3.dump

--
-- Table structure for table `t3`
--

CREATE TABLE t3 (
  id int(11) default NULL,
  `name` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t3`
--

INSERT INTO t3 VALUES (1,'yxyup');
INSERT INTO t3 VALUES (1,'yxyup');

[mysql@QANEW mysql]$ mysql -uroot -pabc123 -D db1 < db1_t3.dump

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
+---------------+
3 rows in set (0.01 sec)

mysql> desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from t3;
+------+-------+
| id   | name  |
+------+-------+
|    1 | yxyup |
|    1 | yxyup |
+------+-------+
2 rows in set (0.00 sec)

成功恢复了

当然,如果全库导出的文件很大,在抽取时会很困难.

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7364032/viewspace-417508/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7364032/viewspace-417508/