Use --databases on mysqldump or not?

Question

I have seen this two ways:

mysqldump -u username -ppassword --databases mydb >with.sql
mysqldump -u username -ppassword mydb >without.sql

I did a linux 'diff' on the two and the with.sql file has this:

< CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
< 
< USE `mydb`;

While the without.sql doesn't. I have used --databases on mysqldump but I have seen tutorials showing it without when doing a mysqldump on a single database.

Why are they different? Which one is most common to use? I'm preparing a mysqldump of a database for a client which might run it in Linux or Windows, so is there a concern of being more portable?

Answer 1

mysqldump -u username -ppassword --databases mydb >with.sql

This gives you dump with top most statement

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` 
    /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mydb`;

In this case client doesn't need to create a database prior to restoration and database will create with default properties .

So in this case you can't change the name of database at another end. Moreover, while using replication some filtering rules will not work.

mysqldump -u username -ppassword mydb >without.sql

In this case table will create in any database, so prior to restoration you need to select the database in which you want this dump to be restored.

This will facilitate you to restore the dump with database name of your choice.

If you will not execute

        use databasename;

prior to restoration, it will throw the error and dump not executed.

So I must say, difference lies on your requirement.