How can I list database names within a mysqldump file?

Question

I have a large mysqldump and I need to create the databases before importing them one-by-one. How can I search and list only the database names using the linux or mysql terminal?

Answer 1

PROPOSED SOLUTION

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

EXAMPLE

# 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
#

You could collect them in a variable (Note: You must escape the backquote)

# 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
#

GIVE IT A TRY !!!

Answer 2

You could do this (see test.sql sample file below):

grep 'CREATE TABLE' my_dump_file_name.sql | wc

Result:

2       8      54

The | (pipe) character feeds (pipes) the output of the grep into wc (not the wc!)!

From man grep

grep, egrep, fgrep - print lines that match patterns

will give you lines of the the form

CREATE TABLE `film_text` (

From man wc

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

So, the first number is the count of the number of tables in the dump file.

You might be interested in printing out the actual statements without all of the accompanying cruft - you can do something like this:

Sample 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;

then run from the command line:

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

Result:

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;

Which is your table declarations without the extra uneccessary stuff.

If I was doing this, I would further feed the output of this into sed as follows:

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

Thanks to here and here for this

From the manual (man sed):

sed - stream editor for filtering and transforming text

What this does:

• the awk command prints out all the lines between CREATE TABLE AND CHARSET=utf8;

• the first sed command removes those annoying (at least I find them so) backtick (`) characters which are no longer necessary in recent versions of MySQL!

• the second sed command puts newlines (\n) after CHARSET=utf8; in order to make the file more legible - which, if you're doing this by hand, should be helpful!

Small sample at CREATE TABLE boundary:

  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,