Mysqldump add drop table?

Question

Mysqldump add drop table?

AlxVallejo

2021年2月16日 06:40

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

Topic mysql backup Wordpress

Category Web

Adeel Raza Azeemi · Accepted Answer · 2021年2月16日 06:40

Yes this would do exactly what it said i.e. It would delete the table if it already existed. Please remember that you are creating a dump that would be imported (with two scenarios);

if in case of fresh database (or table) creation it make no difference by the drop table statement, because there is no table to drop in the first place.
But to skip deletion of the table already created; use --skip-add-drop-table instead. (Also use --no-create-info with --skip-add-drop-table because you also wouldn't want create table statement as well) i.e.

mysqldump --skip-add-drop-table --no-create-info -h mysqlhostserver -u mysqlusername -p databasename tablename | bzip2 -c > blog.bak.sql.bz2

FYI: You can't export more than one table using (tablename, tablename, tablename). Either you export all tables don't use any tablename after databasename OR use only one tablename.

Edit

Please note that if your table is huge then its is wise to export it in parts. i.e. create a dumps files of 80k (eighty thousand) records (roughly equal to 10 Mb) using the following command; FYI:I had split the export using the where to mysqldump.

mysqldump -h mysqlhostserver -u mysqlusername -p --add-drop-table --add-drop-trigger --skip-triggers --dump-date --single-transaction --where='1 limit 0,80000' databasename tablename > /home/blog1.bak.sql
mysqldump mysqlhostserver -u mysqlusername -p --skip-add-drop-table --no-create-info --dump-date --single-transaction --where='1 limit 80000,999999' databasename tablename > /home/blog2.bak.sql

The above command first creates first 80K record file blog1.bak.sql file (note that i had skipped trigger(s) in this dump. I found that later because it had made me crazy that if the first dump had any associated triggers on insert or update. then when you would be importing this first dump file,

it would create the table
insert the records in the newly created table.
create triggers associated with this table and enforced them

So When you would be importing the other dump file after first; it would enforce the triggers (no way to disable triggers for now; except to rewrite those triggers and add complicated code to disable triggers for debuging Or in our case importing). Usually trigger had code with joins to others tables; that might not be imported before this import and thus cause failure to import.

Markus · Accepted Answer · 2018年2月7日 10:00

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.

user2063478 · Accepted Answer · 2013年4月13日 23:39

1

user2063478 answered at 2013年4月13日 23:39

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.

Jake · Accepted Answer · 2012年8月23日 01:17

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.

Mysqldump add drop table?

Edit

About