从MySQL全库备份中恢复某个库和单张表

环境：

mysql版本:5.6.x

利用Mysqldump官方工具全库备份，如何做到只恢复单个库呢？

全库备份

[root@mysql ~]# mysqldump -uroot -p --single-transaction -A --master-data=2 >auto.sql

只恢复某个库的数据如何操作呢？比如恢复xxx库数据

[root@mysql ~]#mysql -uroot -p xxx --one-databases < auto.sql

可以看出主要是用到mysql命令中的--one-database简写-o的参数，极大方便了我们恢复数据时的灵活变化。

-o, --one-database Ignore statements except those that occur while the default database is the one named at the command line.

如何从全库备份中提取某张表呢，全库恢复，再恢复某张表小库还可以，大库就很麻烦了，那我们可以利用正则表达式来进行快速提取，具体实现方法如下：

从全库备份中提取出'cms_cnodes'表结构

[root@mysql~]#sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `cms_cnodes`/!d;q'  auto.sql

DROP TABLE IF EXISTS cms_cnodes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cms_cnodes` (
`cnid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '节点编号',
`alias` varchar(50) NOT NULL COMMENT '节点名称',
`appurl` varchar(80) NOT NULL COMMENT '节点跳转URL',
`ename` varchar(50) NOT NULL COMMENT '节点名称',
`closed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否关闭',
`caid` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '栏目属性',
`cnlevel` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '交叉层数',
`needstatics` varchar(255) NOT NULL COMMENT '静态时间',
`ccid18` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '销售状态',
`ccid12` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '排量',
`tid` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '节点配置编号',
`keeptid` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '定制节点，保持tid',
`ccid13` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '年款',
`ccid1` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '车类目',
`ccid7` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '用途分类',
`ccid3` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '地铁',
`ccid8` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '车款级别',
`ccid16` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '车模图集',
`ccid17` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '新房价格区间',
`ccid19` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '(已不用)',
`ccid20` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '地区',
`ccid15` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '汽车主题',
`ccid24` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '(已不用)',
`ccid2` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '新车资讯',
`ccid10` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '新车价格',
`ccid11` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '二手车价',
`ccid23` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '车龄',
`ccid28` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '求购车价',
`ccid22` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '行驶里程',
`ccid51` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '等级推荐',
`ccid53` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '租车类型',
`ccid57` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '问题状态',
`ccid56` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '精华',
`ccid58` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '高额悬赏',
`ccid59` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '问答推荐',
`ccid60` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '问吧分类',
PRIMARY KEY (`cnid`),
UNIQUE KEY `enamesid` (`ename`)
) ENGINE=MyISAM AUTO_INCREMENT=3406 DEFAULT CHARSET=utf8 COMMENT='节点表';
/*!40101 SET character_set_client = @saved_cs_client */;

从全库备份中提取出'cms_cnodes'表数据

[root@mysql~]#grep -i 'INSERT INTO `cms_cnodes`' auto.sql

INSERT INTO `cms_cnodes` VALUES (1,'','','caid=8&ccid20=1296',0,8,2,'1413947415,',0,0,119,0,0,0,0,0,0,0,0,0,1296,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0),(2,'','','caid=8&ccid20=1297',0,8,2,'1413947415,',0,0,119,0,0,0,0,0,0,0,0,0,1297,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(3,'','','ca
id=8&ccid20=1302',0,8,2,'1413947415,',0,0,119,0,0,0,0,0,0,0,0,0,1302,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(4,'','','caid=8&ccid20=1304',0,8,
2,'1413947415,',0,0,119,0,0,0,0,0,0,0,0,0,1304,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(5,'','','caid=8&ccid20=1305',0,8,2,'1413947415,',0,0,11
9,0,0,0,0,0,0,0,0,0,1305,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(6,'','','caid=8&ccid20=1306',0,8,2,'1413947415,',0,0,119,0,0,0,0,0,0,0,0,0,13
06,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),(7,'','','caid=8&ccid20=1307',0,8,2,'1413947415,',0,0,119,0,0,0,0,0,0,0,0,0,1307,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0),(8,'',''...............

dump出来的sql文件被gzip压缩以后如何处理呢，其实也非常简单，只需要通过gunzip -c xxxx.gz，比如提取某个表的数据并重定向到sql文件中

[root@mysql~]#gunzip -c xxx-backup-2016-11-23.gz |grep -i  'insert into `ju_base`'>>/data/insert_ju_base.sql

提取表结构访问类似，请自行补充。