How can I get rid of these comments in a MySQL dump?

Question

I'm trying to create a simple structure only dump of my database. Using mysqldump gives me a result like:

/*!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 */;

DROP TABLE IF EXISTS `foo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

No matter what I try, I just can't seem to get rid of those comments.

I'm currently using: mysqldump -p -d --add-drop-table --skip-tz-utc --skip-set-charset -h 127.0.0.1 -u foo bar --result-file=dumpfile.sql

Edit: I do however wish to retain other comments, such as -- MySQL dump 10.13 Distrib 5.1.41, for Win32 (ia32)

Answer 1

Have you tried the shortcut option --compact?

Information here.

Answer 2

Try --skip-comments ?

Thanks

Edit:

I see .. Try this

--skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset

Play around to remove some of the options till you get the desired result, basically this is same as --compact without --skip-comments

--skip-comments removes the comments relating to version and stuff ..

Answer 3

Probably running a regex on it to remove lines that contain 40014 or 40111 etc.

Answer 4

Not a direct answer - but I have ditched straight mysqldump entirely for mk-parallel-dump - it's faster (spawns multiple processes) and depending on what you are going to do with the dump output, more flexible as it effectively encapsulates mysqldump and 'select into outfile' syntax together.

Answer 5

Since you are on Windows, if no-one finds a better solution then you could use a Python script instead:

import re, sys
sql = sys.stdin.read()
regex = re.compile(r'/\*![^\n]* \*/;\n', re.M)
print regex.sub('', sql)

Usage from command line:

python program.py < your.sql > output.sql

It removes all lines like this:

/*!....... */;

Answer 6

Technically the lines you are trying to get rid of are not comments. They temporarily modify some variables at the beginning, and then reset them to the previous value at the end.

They're not very useful (but they're also harmless) in your case, since you're using --no-data, but I thought it worth mentioning that the lines do serve a purpose, and are not just comments.

Answer 7

WHOA! These aren't really comments even though they look that way. They are conditional-execution tokens.

Take this line:

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

If the version of mySQL is 4.00.14 or higher, then the mySQL server will run this statement.

You probably don't want to get rid of this stuff.