MySQL DROP TABLE statements only

a perl command

[coopster]

I recently dumped a very large database and did not include the DROP TABLE statements. I didn't want to run the dump command and transfer this giant file again so I decided to run the dump just to create all the DROP TABLE statements. Yeah, right. If you can tell me how to do so using just the mysqldump utility, I'm forever grateful. Otherwise I came up with a quick command using mysqldump and perl to get what I needed:

mysqldump -u username -p database --skip-opt --add-drop-table --skip-comments --no-data ¦ perl -pe "s/^([^D]¦DROP\S)[^\n]*\n//ig" > test.sql

The forum breaks the pipe symbol, don't forget to replace with full pipe

[whoisgregg]

I believe that adding --no-create-info will do the trick. :)

[coopster]

Oh yes, I believed that too -- you're going to have to give me a little more credit than that, whoisgregg :)

That was one of the first things I tried, to no avail. Add that switch and the dump will remove any and all

DROP TABLE

statements, even if you put the

--add-drop-table

switch back in after the

--no-create-info

. I tried all kinds of variations. Tested on 5.0.7 and 5.1.31.

However, I do agree -- one would think that would be the answer and the way it should work! I didn't file a bug report, just created a workaround. Perhaps I should ask the developers ...

[whoisgregg]

I figured you had tried, but I don't like to skip the obvious stuff. It's the obvious stuff that *I* always overlook. ;)

So the drop table info is part of the create info block. Definitely sounds like a bug to me.

Added Even if it's intentional, the documentation [dev.mysql.com] should be updated.

[coopster]

I figured you had tried, but I don't like to skip the obvious stuff.

No worries, I ask the obvious quite often to be certain nothing was overlooked too.

Regarding the documentation -- agreed.