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

user_193018 0
Viewed 55079

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)

mysqldump export mysql
14 Answers

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.

This magic comment syntax is documented in the Comment Syntax section of the manual.

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

user_2056080

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

user_1754210
user_1858820

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.

user_2331960

It's really important to keep the conditional-execution comments. But if you absolutely know that the MySQL version that will load the dump is greater or equal to the one that creates it, you can remove the "comment" part with this:

sed -r  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g

It will convert lines such as

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

to 

SET SQL_MODE=@OLD_SQL_MODE ;

Because this line must run on any MySQL >= 4.1.1

Note that this will not remove multi-line conditional-execution comments, such as when dumping a trigger.

Since it's impossible to predict the future, it's better to store the dump with the comments on, and only remove them when you want to visualize it.

mysqldump ... > dump.sql
cat dump.sql | sed -E  s'#/\*![0-9]{5} ?([^*]*)\*/#\1#'g > dump.no-comments.sql
user_333640

Use --dump-date=FALSE

Does exactly what OP asks for. (not exactly, I see)

Source: mysqldump option summary

Edit: Just after a minute I realized, this is what me was looking for not the OP, but leaving here... in hope someone can use it: This date line which ruins source control, because it always a change...

user_11578140

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

user_2323610

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:

/*!....... */;
user_619740
Related