By default, mysqldump writes information to standard output as SQL statements. You can save the output in a file:

shell> mysqldump [arguments] > file_name

To dump all databases, call mysqldump with the –all-databases option:

shell> mysqldump --all-databases > dump.sql

 

To dump only a specific database, name it on the command line and use the –databases option:

shell> mysqldump --databases db1 db2 db3 > dump.sql

The –databases option causes all names on the command line to be treated as database names. Without this option, mysqldump treats the first name as the database name and the latter as the table name.

Using –all-databases or –databases, mysqldump writes the CREATE DATABASE and USE statements before the dump output for each database. This ensures that when the dump file is reloaded, it creates each database (if it does not exist) and makes it the default database, so the database contents are loaded into the same database from which they are derived. Use the –add-drop-database option if you want to force the dump file to delete each database before re-creating it. In this case, mysqldump writes a DROP DATABASE statement before each CREATE DATABASE statement.

To dump an individual database, name it at the command line:

shell> mysqldump --databases test > dump.sql

In the case of a single database, you can omit the – databases option:

shell> mysqldump test > dump.sql

The difference between the two commands above is that, without –databases, the dump output does not contain a CREATE DATABASE or USE statement. This has several meanings:

  • When you reload the dump file, you must specify the default database name so that the server knows which database to reload.
  • For reloading, you can specify a different database name from the original name, which allows you to reload the data into other databases.
  • If the database to be reloaded does not exist, you must create it first.
  • Because the output will not contain the CREATE DATABASE statement, the –add-drop-database option does not work. If you use it, it does not generate a DROP DATABASE statement.

To dump only a specific table from the database, name them in the command line after the database name:

shell> mysqldump test t1 t3 t7 > dump.sql