Add DROP DATABASE Statements in mysqldump Output

The mysqldump command is used to create SQL dump files from MySQL databases, allowing for backup or transfer of database contents. One useful option is --add-drop-database, which includes DROP DATABASE statements in the output. This can be particularly handy when you want to ensure that existing databases are dropped before recreating them during the restoration process.

Here are some examples of how to use mysqldump with the --add-drop-database option:

Example 1: Dump a single database with drop database statements:

mysqldump --add-drop-database mydatabase > mydatabase_backup.sql

This command dumps the database mydatabase into a file named mydatabase_backup.sql and includes DROP DATABASE statements for mydatabase in the output.

Example 2: Dump multiple databases with drop database statements:

mysqldump --add-drop-database --databases db1 db2 db3 > multiple_databases_backup.sql

In this case, databases db1, db2, and db3 are dumped into multiple_databases_backup.sql, and DROP DATABASE statements are included for each database.

Example 3: Dump all databases with drop database statements:

mysqldump --add-drop-database --all-databases > all_databases_backup.sql

This command dumps all MySQL databases into all_databases_backup.sql, with DROP DATABASE statements included for each database.

Example 4: Include create database statement with drop database statements:

mysqldump --add-drop-database --add-drop-create-options mydatabase > mydatabase_backup.sql

Here, along with DROP DATABASE statements, the CREATE DATABASE statement for mydatabase is also included in the output file mydatabase_backup.sql.

Example 5: Dump with extended insert statements and drop database:

mysqldump --add-drop-database --extended-insert mydatabase > mydatabase_backup.sql

This command uses extended insert statements for better efficiency and includes DROP DATABASE statements for mydatabase in the dumped file.

Example 6: Dump with triggers and events including drop database:

mysqldump --add-drop-database --triggers --events mydatabase > mydatabase_backup.sql

By specifying --triggers and --events, this command includes triggers and events definitions along with DROP DATABASE statements for mydatabase in the backup.

Example 7: Dump only schema (no data) with drop database:

mysqldump --add-drop-database --no-data mydatabase > mydatabase_schema_backup.sql

This command dumps only the schema (table structures) for mydatabase along with DROP DATABASE statements in mydatabase_schema_backup.sql.

Example 8: Dump with complete insert statements and drop database:

mysqldump --add-drop-database --complete-insert mydatabase > mydatabase_backup.sql

By using --complete-insert, this command generates complete insert statements and includes DROP DATABASE statements for mydatabase in the output.

Example 9: Dump with stored procedures and functions including drop database:

mysqldump --add-drop-database --routines mydatabase > mydatabase_backup.sql

This command includes stored procedures and functions definitions along with DROP DATABASE statements for mydatabase in the dumped SQL file.

Example 10: Dump with table lock option and drop database:

mysqldump --add-drop-database --lock-tables mydatabase > mydatabase_backup.sql

Using --lock-tables ensures that tables are locked before dumping, and DROP DATABASE statements for mydatabase are included in the output file.

To verify if the mysqldump command executed successfully, you can check the generated SQL dump file. Look for the presence of DROP DATABASE statements for the specified databases within the file. For example, open the .sql file using a text editor or use command-line tools to search for specific strings or statements.

Read Also

Omit Tablespace Information in mysqldump Output
Using Custom Configuration Files with mysqldump
Dump All Tablespaces with mysqldump
Dump All Databases Using mysqldump
How to Utilize Named Login Paths in mysqldump