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='测试
';
/*!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