linux备份某张表,从全备份的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)

成功恢复了

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