Complete database reset for MySQL dump?

Question

This may seem like a very dumb question but I didn't learn it in any other way and I just want to have some clarification.

I started to use MySQL a while ago and in order to test various scenarios, I back up my databases. I used MySQL dump for that:

Export:

mysqldump -hSERVER -uUSER -pPASSWORD --all-databases > filename.sql

Import:

mysql -hSERVER -uUSER -pPASSWORD < filename.sql

Easy enough and it worked quite well up until now, when I noticed a little problem with this "setup": It does not fully "reset" the databases and tables. If, for example, there is an additional table added AFTER a dump file has been created, that additional table will not disappear if you import the same dump file. It essentially only "corrects" tables already there and recreates any databaes or tables missing, but does not remove any additional tables, which happen to have names that are not in the dump file.

What I want to do is to completely reset all the databases on a server when I import such a dump file. What would be the best solution? Is there a special import function reserved for that purpose or do I have to delete the databases myself first? Or is that a bad idea?

Answer 1

You can use the parameter --add-drop-database to add a "drop database" statement to the dump before each "create database" statement.

e.g.

mysqldump -hSERVER -uUSER -pPASSWORD --all-databases --add-drop-database >filename.sql

see here for details.