I want to create a dumpfile of the contents of a db- I am using the following:

`/usr/bin/mysqldump -t -n -u root -ppassword -B DB > /usr/local/restore/dumpfile_$unixtime`

This obviously produces a bunch of INSERT commands. But what I want is for the dumpfile to prepend the insert statements with statements that delete from each of the tables in that database (so that the inserts are always run on a fresh table).

Any ideas what flag I should use? I am using mysql 3.23.56-1.9.

Thanks
Pali

I think the add-drop-table option will help you:

--add-drop-table    Add a 'drop table' before each create.


Graham,
I am specifically using the -t option to avoid producing all the create table syntax, which I don't need. Thus the -add-drop-table option has no effect, since it only does anything if it finds the create keyword.
Also i am only interested in deleting the contents of the table, not the table itself.
Thanks


Hmm - it's going to be much less efficient to clear out all the data in a table and repopulate it that to simply recreate the table. It's rather as if you want to rub out all the pencil lines on a piece of paper so that you have something to draw on when you could simply throw the old piece away and pick up a new one.  

Perhaps there's something I'm missing - is the structure of the tables on the system that you're dumping off different to the new structure that you want to re-populate - perhaps some extra fields, for example?

The structure of the tables is in fact exactly the same. I guess, its just paranoia / added risk of dropping a table alltogether and then recreating it.
Thanks Graham.

---