Suppressing CREATE DATABASE Statements in mysqldump

The mysqldump command is used to create backups of MySQL databases. When using the option -n or --no-create-db, it suppresses the inclusion of CREATE DATABASE statements in the output of the dump. This can be useful in scenarios where you want to import the dump into an existing database without creating a new database.

Here are some examples of how to use mysqldump with the --no-create-db option:

Example 1: Dumping a single database without CREATE DATABASE statements:

mysqldump -u username -p --no-create-db dbname > dump.sql

This command exports the database dbname to dump.sql without including CREATE DATABASE statements. To verify, you can open dump.sql and check for the absence of CREATE DATABASE statements.

Example 2: Dumping multiple databases and suppressing CREATE DATABASE statements:

mysqldump -u username -p --no-create-db --databases db1 db2 db3 > dump.sql

Here, databases db1, db2, and db3 are dumped into dump.sql without CREATE DATABASE statements. Verify the output file for confirmation.

Example 3: Dumping all databases except one with suppressed CREATE DATABASE statements:

mysqldump -u username -p --no-create-db --all-databases --ignore-database=db_to_ignore > dump.sql

This command dumps all databases except db_to_ignore into dump.sql without including CREATE DATABASE for each database. Check dump.sql to ensure the exclusion of CREATE DATABASE statements for db_to_ignore.

Example 4: Dumping specific tables from a database with suppressed CREATE DATABASE statements:

mysqldump -u username -p --no-create-db dbname table1 table2 > dump.sql

Here, table1 and table2 from database dbname are dumped without CREATE DATABASE statements into dump.sql. Verify the file to confirm the absence of CREATE DATABASE commands.

Example 5: Dumping with extended insert statements and no CREATE DATABASE:

mysqldump -u username -p --no-create-db --extended-insert dbname > dump.sql

This command uses extended insert statements while dumping dbname into dump.sql, omitting CREATE DATABASE statements. Check the dump file for accurate execution.

Example 6: Dumping with gzip compression and no CREATE DATABASE:

mysqldump -u username -p --no-create-db dbname | gzip > dump.sql.gz

Here, the dump of dbname is compressed using gzip without CREATE DATABASE statements. To verify, unzip dump.sql.gz and inspect the contents.

Example 7: Dumping a database and excluding triggers with suppressed CREATE DATABASE:

mysqldump -u username -p --no-create-db --skip-triggers dbname > dump.sql

This command exports dbname excluding triggers and without CREATE DATABASE statements into dump.sql. Confirm the absence of triggers and CREATE DATABASE commands in the output file.

Example 8: Dumping with custom SQL syntax and no CREATE DATABASE:

mysqldump -u username -p --no-create-db --complete-insert dbname > dump.sql

This command uses complete insert syntax while dumping dbname into dump.sql, excluding CREATE DATABASE statements. Verify the dump file for accurate syntax usage.

Example 9: Dumping and ignoring views with suppressed CREATE DATABASE:

mysqldump -u username -p --no-create-db --ignore-table=dbname.view_name dbname > dump.sql

Here, the view view_name from dbname is ignored during dump, and no CREATE DATABASE statements are included in dump.sql. Check the dump file for exclusion of views and CREATE DATABASE commands.

Example 10: Dumping with row locking and without CREATE DATABASE:

mysqldump -u username -p --no-create-db --lock-tables dbname > dump.sql

This command locks tables during dump of dbname and excludes CREATE DATABASE statements from dump.sql. Verify the dump file for correct table locking and absence of CREATE DATABASE commands.

Also check similar articles.

Disabling Autocommit in mysqldump Output
Setting Network Buffer Length in mysqldump
Setting Maximum Allowed Packet Size in mysqldump
Appending Binary Log Position in mysqldump Output (Deprecated)
Appending Binary Log Position in mysqldump Output