mysql dump 指定表_mysqldump恢复指定表

如果mysql服务器上不能随意安装软件，当需要从全备份中恢复单个表，怎么办？

1.mysqldump备份指定数据库，如ivr

[mysql@bjmysql01]$ mysqldump -utelivr -p123456 -F -R --events --databases ivr |gzip >ivr_$(date +%F).sql.gz

2. 确认备份文件已经生成

[mysql@bjmysql01]$ ll ivr*

ivr_2014-01-25.sql.gz

3.模拟删除atuo表

mysql> show tables;

+---------------+

| Tables_in_ivr |

+---------------+

| atuo |

| ivr |

+---------------+

2 rows in set (0.00 sec)

mysql> select * from atuo;

+----+------+

| id | name |

+----+------+

| 1 | a |

| 2 | b |

| 4 | c |

+----+------+

3 rows in set (0.00 sec)

mysql> drop table atuo;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+---------------+

| Tables_in_ivr |

+---------------+

| ivr |

+---------------+

1 row in set (0.00 sec)

4.从备份文件中找出需要恢复表的建表语句：

[mysql@bjmysql01]$ gunzip -c ivr_2014-01-25.sql.gz |sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `atuo`/!d;q'

DROP TABLE IF