從全備份的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/，如需轉載，請註明出處，否則將追究法律責任。