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)
user@linux~/files/blog>

http://codex.wordpress.org/Backing_Up_Your_Database#Using_Straight_MySQL_Commands

up vote 5 down vote accepted

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:

DROP TABLE IF EXISTS `tablename`;

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

  • 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.

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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