Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I use mysqldump to export only the CREATE TABLE commands?

I'm trying to use mysqldump to export only the DB schema -- no data, no additional SQL comments, just the CREATE TABLE commands. Here's what I've got so far:

mysqldump -h localhost -u root -p --no-data --compact  some_db 

It almost achieves what I want, but I'd like to eliminate the "character set" lines (those like the first 3 lines in the example output below). Is there a mysqldump option to do that?

/*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client     = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `foo` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `bar_id` int(11) DEFAULT NULL,   `bazz` varchar(255) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=369348 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client     = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `bar` ( ...etc. 

Here's my version info, in case that matters:

mysqldump Ver 10.13 Distrib 5.1.34, for Win32 (ia32) mysql Ver 14.14 Distrib 5.1.34, for Win32 (ia32) 
like image 426
FMc Avatar asked Dec 03 '09 18:12

FMc


People also ask

How do I export a specific table?

Open the MySQL database wizard & select the table you want to export. Right-click on the table name & select the table data export wizard option.


2 Answers

This uses grep as well, but it seems to work:

mysqldump -d --compact --compatible=mysql323 ${dbname}|egrep -v "(^SET|^/\*\!)" 

I'm using:

Ver 10.11 Distrib 5.0.51a, for debian-linux-gnu (x86_64)

like image 59
AJ. Avatar answered Sep 23 '22 11:09

AJ.


Here is the command to dump the schema without the character set and AUTO_INCREMENT.

mysqldump -h localhost -u root -p --no-data  YOUR_DATABASE_HERE |egrep -v "(^SET|^/\*\!)" | sed 's/ AUTO_INCREMENT=[0-9]*\b//' 

Here is the command to dump the schema without the character set, AUTO_INCREMENT and the comments

mysqldump -h localhost -u root -p --no-data --compact YOUR_DATABASE_HERE |egrep -v "(^SET|^/\*\!)" | sed 's/ AUTO_INCREMENT=[0-9]*\b//' 
like image 42
Xavier John Avatar answered Sep 23 '22 11:09

Xavier John