mysql数据库中,通过mysqldump工具仅将某个库的所有表的定义进行转储
需求描述:
在研究mysqldump工具的使用,想的是如何将某个库下的,或者某个表的表的定义(表结构创建语句)进行转储
操作过程:
1.通过--no-data参数,就可以将某个库的表定义进行转储
[mysql@redhat6 MysqlDb_Backup]$ mysqldump -uroot -p --no-data top_server > Db_Top_server_OnlyTableDefinitions_2018-04-10.sql Enter password: [mysql@redhat6 MysqlDb_Backup]$ ls -ltr total 58676 -rw-rw-r-- 1 mysql mysql 12658 Apr 10 10:00 Tables_Backup_2018-04-10.sql -rw-rw-r-- 1 mysql mysql 60014479 Apr 10 11:11 FullBackup_2018-04-10.sql -rw-rw-r-- 1 mysql mysql 53164 Apr 10 11:27 Db_Top_server_OnlyTableDefinitions_2018-04-10.sql
2.查看转储出来的SQL文件
-- -- Table structure for table `flow_node_info` -- DROP TABLE IF EXISTS `flow_node_info`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `flow_node_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `flow_id` int(11) DEFAULT NULL, `node_name` varchar(64) DEFAULT NULL, `node_code` varchar(64) DEFAULT NULL, `role_id` int(11) DEFAULT NULL, `audit_user` varchar(254) DEFAULT NULL COMMENT '测试', `creat_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, `node_sort` int(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8 COMMENT='测试'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `goods_alert_info` -- DROP TABLE IF EXISTS `goods_alert_info`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `goods_alert_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `goods_type` varchar(64) DEFAULT NULL, `goods_attr` varchar(64) DEFAULT NULL, `goods_num` int(11) DEFAULT NULL, `total_time` datetime DEFAULT NULL, `alert_line` int(11) DEFAULT NULL, `alert_mode` char(1) DEFAULT NULL COMMENT '系统测试', `alert_object` varchar(64) DEFAULT NULL, `alert_freq` int(11) DEFAULT NULL, `alert_time` datetime DEFAULT NULL, `alert_type` char(1) DEFAULT NULL COMMENT '测试', `creat_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, `store_id` int(11) DEFAULT NULL, `stock_id` int(11) DEFAULT NULL, `alert_up` int(11) DEFAULT NULL, `alert_down` int(11) DEFAULT NULL, `alert_status` char(1) DEFAULT NULL, `alert_set` char(1) DEFAULT NULL, `type_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='测试\r\n'; /*!40101 SET character_set_client = @saved_cs_client */;
备注:都是各个表的删除语句,创建表语句,各个表之间没有INSERT语句.
3.对某个库下的某几个表的定义进行转储
[mysql@redhat6 MysqlDb_Backup]$ mysqldump -uroot -p --no-data top_server supplier_goods_type store_goods_rela > TableDefinition_2018-04-10.sql Enter password: [mysql@redhat6 MysqlDb_Backup]$ ls -ltr total 58680 -rw-rw-r-- 1 mysql mysql 12658 Apr 10 10:00 Tables_Backup_2018-04-10.sql -rw-rw-r-- 1 mysql mysql 60014479 Apr 10 11:11 FullBackup_2018-04-10.sql -rw-rw-r-- 1 mysql mysql 53164 Apr 10 11:27 Db_Top_server_OnlyTableDefinitions_2018-04-10.sql -rw-rw-r-- 1 mysql mysql 2566 Apr 10 16:02 TableDefinition_2018-04-10.sql
备注:标记为红色的部分是top_server中的表
4.查看生成的SQL转储文件
-- MySQL dump 10.13 Distrib 5.5.57, for linux-glibc2.12 (x86_64) -- -- Host: localhost Database: top_server -- ------------------------------------------------------ -- Server version 5.5.57-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `supplier_goods_type` -- DROP TABLE IF EXISTS `supplier_goods_type`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `supplier_goods_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sup_id` int(11) DEFAULT NULL, `type_id` int(11) DEFAULT NULL, `goods_price` decimal(16,4) DEFAULT NULL, `creat_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, `is_child` char(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4338 DEFAULT CHARSET=utf8 COMMENT='测试表01'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `store_goods_rela` -- DROP TABLE IF EXISTS `store_goods_rela`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `store_goods_rela` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `order_id` bigint(20) DEFAULT NULL, `creat_time` datetime DEFAULT NULL, `store_price` decimal(16,4) DEFAULT NULL, `goods_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=449 DEFAULT CHARSET=utf8 COMMENT='测试表02'; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2018-04-10 16:02:40
备注:发现转储出来的都是表的创建语句.
文档创建时间:2018年4月10日15:59:41
