I notice in the Codex that the --add-drop-table option is displayed for backing up a database. Before I screw anything up, does this just mean that when the backup is eventually imported, the tables will overwrite if they exist in the destination db?

I don't want to drop the tables when I back them up!

user@linux:~/files/blog> mysqldump --add-drop-table -h mysqlhostserver
 -u mysqlusername -p databasename (tablename tablename tablename) | bzip2
 -c > blog.bak.sql.bz2

Enter password: (enter your mysql password)



It only affects the output of your MySQL dump in the file that is created.

It isn't necessary. It is just there so that if you import the created dump file into a database that already has a table with the same name, it will drop that table and then add the new table in its place. Otherwise you will get an error, and the dump file won't be imported.

It adds this line before the create table statement in the dump file:


If you plan to import the dump file into a fresh database, it won't matter.

| improve this answer | |
  • You know what's interesting is that even if I use the dump without the add-drop-table option on an existing WP database, it works fine as well. – AlxVallejo Sep 22 '12 at 17:33

That's because the overall syntax goes something like:

mysql>mysqldump --opt --all-databses > all.sql

--opt is enabled by default (which implies: --add-drop-table) as mentioned in MySQL documentation.

| improve this answer | |

When removing --add-drop-table in this syntax, --add-drop-table is used anyways, because --opt is default (unless you use --skip-opt) and --opt includes --add-drop-table. See here: https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_opt

And, as stated above, it doesnt mean that your tables are dropped when you backup, but that the commands for dropping tables are included in the sql file, because that is what you want for a dump, so you can import the dump without getting errors that the tables already exist.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.