How to get all full CREATE TABLE statements from a mysql dump

Question

I got this so far:

$ bzcat mysqldump.pplware.sql.bz2 | grep -A 10 "^CREATE TABLE.*"
CREATE TABLE `wp_ak_twitter` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tw_id` varchar(255) NOT NULL,
  `tw_text` varchar(255) NOT NULL,
  `tw_reply_username` varchar(255) DEFAULT NULL,
  `tw_reply_tweet` varchar(255) DEFAULT NULL,
  `tw_created_at` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tw_id` (`tw_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
--
CREATE TABLE `wp_audit_trail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operation` varchar(40) NOT NULL DEFAULT '',
  `user_id` int(11) NOT NULL,
  `ip` int(11) unsigned NOT NULL DEFAULT '0',
  `happened_at` datetime NOT NULL,
  `item_id` int(11) DEFAULT NULL,
  `data` longtext,
  `title` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9442523 DEFAULT CHARSET=latin1;

..and so on. But the 10 line limit is arbitrary, and some of the CREATE TABLES are shorter and some are longer. How can I get all full CREATE TABLE definitions?

Possible duplicate of [How to select lines between two patterns?](https://stackoverflow.com/questions/38972736/how-to-select-lines-between-two-patterns) — Sundeep, Dec 05 '17 at 15:55

score 1 · Accepted Answer · answered Dec 05 '17 at 15:57

1

How about printing lines between patterns: CREATE TABLE and ; semi-colon:-

bzcat mysqldump.pplware.sql.bz2 | awk '/CREATE TABLE/{f=1}/\;/{f=0}f||/\;/'

OR

bzcat mysqldump.pplware.sql.bz2 | awk '/CREATE TABLE/,/\;/'

answered Dec 05 '17 at 15:57

Yoda

435
2
7

score 0 · Answer 2 · answered Dec 07 '17 at 02:55

0

How about just getting the definitions, without the data:

mysqldump ... --no-data ...

(Sure, this does not answer the question as asked, but it may provide an easy solution in some cases.)

answered Dec 07 '17 at 02:55

Rick James

135,179
13
127
222

How to get all full CREATE TABLE statements from a mysql dump

2 Answers2