mysqldump with create database line

mysql database backup mysqldump
Vince picture Vince · May 9, 2013 · Viewed 99k times · Source

I'm in the process of moving my files onto another computer, and one thing I would like to do is to transfer the data in my MySQL database. I want to dump the databases into .sql files, but also have the create database db_name including in the file, that way I just have to import the file to mysql without having to manually create the databases. Is there a way to do that?

Answer

GregD picture GregD · May 9, 2013

By default mysqldump always creates the CREATE DATABASE IF NOT EXISTS db_name; statement at the beginning of the dump file.

[EDIT] Few things about the mysqldump file and it's options:

--all-databases, -A

Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line.

--add-drop-database

Add a DROP DATABASE statement before each CREATE DATABASE statement. This option is typically used in conjunction with the --all-databases or --databases option because no CREATE DATABASE statements are written unless one of those options is specified.

--databases, -B

Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. CREATE DATABASE and USE statements are included in the output before each new database.

--no-create-db, -n

This option suppresses the CREATE DATABASE statements that are otherwise included in the output if the --databases or --all-databases option is given.

Some time ago, there was similar question actually asking about not having such statement on the beginning of the file (for XML file). Link to that question is here.

So to answer your question:

  • if you have one database to dump, you should have the --add-drop-database option in your mysqldump statement.
  • if you have multiple databases to dump, you should use the option --databases or --all-databases and the CREATE DATABASE syntax will be added automatically

More information at MySQL Reference Manual

Related questions

Split up a mysqldump file with multiple databases, by database

I have a mysqldump file of multiple databases (5). One of the database takes a very long time to load, is there a way to either split the mysqldump file by database, or just tell mysql to load only one of …

Read More
MySQL Database backup automatically on a windows server

Is there a way to back up MySQL database automatically at certain times of the day for designated servers or send an email with an attachment.. Which ever do you think is the best and safest way to achieve this?

Read More
How to backup MySQL database on a remote server?

I have a MySQL database existing on a remote server. I only have sql connection privilege. I don't have FTP access to the server, and I need to do a complete dump of the database. I have tried mysqldump, but …

Read More