MySQL – Restoring Backup from mysqldump Without Removing Additions

backupMySQLrestore

I am restoring a backup from a mysqldump and noticing that if I made additions (in the form of new tables) to the database they persist after the restore. I can go back in and remove those tables to get back to the original state, but as I need to test migrations, I'd like to bring the DB back to its state during the dump without manually going in and removing tables.

I suppose I could do a DROP Database XXX before the restore, but will that hamper my current restoration methods?

Best Answer

mysqldump has an --add-drop-database option that will put those in the file for you.

It will add this, before each database in the file:

/*!40000 DROP DATABASE IF EXISTS `accounting`*/;

For the benefit of anyone unfamiliar with magic comments, this isn't a comment. It's a statement that will be executed by MySQL 4.00.00 and higher, disguised as a comment for backwards-compatibility.

And, of course, this assumes your database, like mine, is called "accounting."

Note, though, that for --add-drop-database to work, you need to specify the database(s) to back up using either --all-databases or the --databases YOUR_DB_NAME command line option to mysqldump. You "can" make a backup of a single database with just the database name on the command line by itself, without the --databases option preceding it... that also sort of works but is really not a good way to go, unless you intend to restore the backup into a different database name than the one you got it from, which, thanks to the way that option works, you might do even if it wasn't your plan.

Using --databases YOUR_DB_NAME or --all-databases automatically adds CREATE DATABASE and USE your_db_name; statements to the top of each schema in the file. This means you don't have to specify it when you're restoring, and if you forget or get it wrong, the data still goes to the right place -- the "right" place being the place the file says it should go.

The --add-drop-database option adds the DROP statement to clear the way for the new one, immediately before the CREATE and USE statements.

Related Question