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?

  • Can't you create a tiny test database and check your issues? I've been caught out before with the database not existing and having to create it manually.
    – Vérace
    Sep 24, 2015 at 5:31

1 Answer 1

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

This gives you dump with top most statement

    /*!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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.