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)

Solution 1

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.

Solution 2

I know this is an ancient question, but here is an answer at least. I also couldn't find a flag in mysqldump to remove the conditional comments, or indeed a better option to set a minimum mysql version for these comments to appear. If you just want to nuke them all, you can do so using grep or sed (sed leaves blank lines, grep does not):

mysqldump ... | grep -v '^\/\*![0-9]\{5\}.*\/;$'
mysqldump ... | sed -e 's/^\/\*![0-9]\{5\}.*\/;$//g'

To answer my own wish of conditionally removing comments dependent on mysql version, use one of these (removes any comments for anything < mysql5):

mysqldump ... | grep -v '^\/\*![0-4][0-9]\{4\}.*\/;$'
mysqldump ... | sed -e 's/^\/\*![0-4][0-9]\{4\}.*\/;$//g'

Solution 3

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

Solution 4

Have you tried the shortcut option --compact?

Information here.

Solution 5

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.

View more solutions

Comments

[etheros]

I did, however it disables other comments I do want, such as -- MySQL dump 10.13 Distrib 5.1.41, for Win32 (ia32).

[etheros]

Unfortunately this removes all the comments I do want, leaving all of those I don't intact.

[plainjimbo]

What's with all the downvotes on the "strip the lines with a regexp" answers? These are totally valid. This one in particular is entirely applicable to my situation. +1's for all of you.

[Dan Nissenbaum]

Another question that specifically asks about comments in MySQL being treated as executable SQL (just posted by me): stackoverflow.com/questions/25614919/…

[varela]

There are some cases when removing comments can help. i.e. bugs.mysql.com/bug.php?id=48972 when --insert-ignore doesn't work as expected because of them

[mpen]

How can we "delete the comment part"? Is there a dump option for this? I don't want to go through a couple gig file by hand.

[Captain Hypertext]

+1 I never knew that. I thought they were just commands that mysql dump was running as it exported. Very enlightening answer.

[rubo77]

For rsnapshot backups it is useful to get rid of the last line, so a database, that didn't change results in the same file: mysqldump ... | grep -v '^-- Dump completed on .*$'

[JayRizzo]

yeah but the problem is you may lose the DROP DATABASE IF EXISTS OR IGNORING YOUR CURRENT SESSION VARIABLES . Unless you know what your doing: Don't remove them ESPECIALLY WHEN MIGRATING BETWEEN ENVIRONMENTS/HOSTS. as the resulted output may not be as expected, for any number of reasons. They were put there for your protection. but if you don't want to put your seatbelt on that is your choice.

[CMCDragonkai]

Suppose that should mean there should be compatibility expected options, that specify whether you expect to run the sql on older mysql versions, and if not, on need to wrap it in a conditional comment.

[Ben Johnson]

@etheros I concur, but we should elaborate. My use-case relates to source-controlled structural data. I don't want any non-essential chatter for every table. I like the SET NAMES call that --skip-set-charset, as suggested in this answer, removes; it happens only once at the beginning of the dump file, and could affect data restoration in a material way. I do like --skip-add-locks --skip-disable-keys for my use-case. But some of the conditional comments, such as /*!40101 SET character_set_client = @saved_cs_client */; and /*!40101 SET character_set_client = utf8 */... useful, or not?

[Slava]

@BenJohnson No they are not. character_set_client cannot be set in 5.6 due to a bug or something, so when you are mysqldumping for example utf8mb4 data, you get these conditional comments where you don't want them.

[par]

Actually it should be: If you try to run a "comfortable" script in a version older than the specified in the "comment", you will get an error.

[dehart]

@rubo77 You could use this mysql dump parameter as well: --skip-dump-date

[kungfooman]

Exactly what I was looking for, why people even downvote this? Big upvote from me.

[Trip]

Better than gsub'ing though, how can we just make sure our local DB is equal to that of our collaborators. Then you won't have to do all this..