主页 / dba / 问题 / 269206
Accepted
Dawoodjee
Asked: 2020-06-16 09:54:25 +0800 CST

如何在 mysqldump 文件中列出数据库名称？

  • 772

我有一个很大的 mysqldump，我需要在一个一个导入之前创建数据库。如何使用 linux 或 mysql 终端仅搜索和列出数据库名称？

mysql mariadb

2 个回答

  1. Best Answer

    提议的解决方案

    DUMPFILE=mydump.sql
grep "^USE " ${DUMPFILE} | sed 's/;//' | sed 's/`//g' | awk '{print $2}'

    例子

    # grep "^USE " dump.sql | sed 's/;//' | sed 's/`//g' | awk '{print $2}'
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
redwards
scmp_lab
topup_lab
wsms2_lab
zenith_lab
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
redwards
scmp_lab
topup_lab
wsms2_lab
zenith_lab
#

    您可以将它们收集在一个变量中（注意：您必须转义反引号）

    # DBLIST=`grep "^USE " dump.sql | sed 's/;//' | sed 's/\`//g' | awk '{print $2}'`
# echo ${DBLIST}
CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine redwards scmp_lab topup_lab wsms2_lab zenith_lab CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine redwards scmp_lab topup_lab wsms2_lab zenith_lab
#

    试试看 ！！！

    • 5

  2. 您可以这样做（请参阅下面的 test.sql 示例文件）：

    grep 'CREATE TABLE' my_dump_file_name.sql | wc

    结果：

    2       8      54

    （|管道）字符将 grep 的输出wc（而不是wc！）输入（管道）！

    从man grep

    grep, egrep, fgrep - print lines that match patterns

    会给你表格的行

    CREATE TABLE `film_text` (

    从man wc

    wc - print newline, word, and byte counts for each file

    所以，第一个数字是转储文件中表的数量。

    您可能有兴趣打印出没有所有附带内容的实际语句 - 您可以执行以下操作：

    示例文件 - test.sql

    -- 
-- more cruft...
--
-- blah, blah, blah, blah, blah, blah, blah, blah, blah, blah, blah, 
--
--

DROP TABLE IF EXISTS `film_text`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `film_text` (
  `film_id` smallint NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`film_id`),
  FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `inventory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `inventory` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`),
  CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES
`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES
`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8;

    然后从命令行运行：

    awk '/CREATE TABLE/ {p=1}; p; /CHARSET=utf8;/ {p=0};' test.sql

    结果：

    CREATE TABLE `film_text` (
  `film_id` smallint NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`film_id`),
  FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `inventory` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`),
  CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES
`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES
`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8;

    这是你的表声明，没有额外的不必要的东西。

    如果我这样做，我会进一步将其输出输入sed如下：

    awk '/CREATE TABLE/ {p=1}; p; /CHARSET=utf8;/ {p=0}' test.sql | \
sed 's/`//g' | \
sed 's/DEFAULT CHARSET=utf8;/DEFAULT CHARSET=utf8;\n/g' > final.sql

    感谢这里和这里_

    从手册（man sed）：

    sed - stream editor for filtering and transforming text

    这是做什么的：

    • 该awk命令打印出CREATE TABLEAND之间的所有行CHARSET=utf8;

    • 第一个sed命令删除了那些烦人的（至少我是这样认为的）反引号（`）字符，这些字符在最新版本的 MySQL 中不再需要！

    • 第二个sed命令在之后放置换行符 ( \n)CHARSET=utf8;以使文件更清晰 - 如果您手动执行此操作，这应该会有所帮助！

    CREATE TABLE边界小样本：

      PRIMARY KEY (film_id),
  FULLTEXT KEY idx_title_description (title,description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   ------- blank line here ---------   
CREATE TABLE inventory (
  inventory_id mediumint unsigned NOT NULL AUTO_INCREMENT,
    • 1

相关问题