环境:
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
提取表结构访问类似,请自行补充。
您可以选择一种方式赞助本站
支付宝转账赞助
