Mysqldump add drop table?

2 Views

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!

Read More
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

Post Views: 2

Related posts

Leave a Reply

4 comments

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

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

  4. 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);

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

    2. 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,

    1. it would create the table
    2. insert the records in the newly created table.
    3. 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.