87

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?

GregD
  • 2,797
  • 3
  • 28
  • 39
Vince
  • 2,596
  • 11
  • 43
  • 76

4 Answers4

106

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

Community
  • 1
  • 1
GregD
  • 2,797
  • 3
  • 28
  • 39
  • 2
    That's what was happening with me. I wasn't using --databases or --all-databases. I just put the database name, which wouldn't create the drop database if exists and create database line. – Vince May 09 '13 at 01:25
  • Yes, then in case of one database, add that `--add-drop-database` option and everything should be fine. Remember though, that importing the dump file will actually drop that database and everything included inside, if such is present in the computer. – GregD May 09 '13 at 01:36
  • 4
    What version was this for? mysqldump Ver 10.11 Distrib 5.0.51a, for debian-linux-gnu (i486) mysqldump --add-drop-database test | grep DROP only outputs DROP TABLE lines for the test database. I have to add --databases before I get a commented out DROP DATABASE IF EXISTS `test` – jla Jan 07 '15 at 20:28
  • @jla - The MySQL version was 5.1 at the time of writing the answer. I assume this from the link to the Reference Manual. – GregD Dec 07 '15 at 09:08
  • Note that while using the `--all-databases` option is similar to using the `--databases` option and naming all the databases on the command line, it is not the same. `--all-databases` omits the `information_schema` and `performance_schema` tables. Normally, there is not a need to dump these, but if you want them, you must explicitly call them out with the `--databases` option. See http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_databases – Clandestine Nov 22 '16 at 20:33
  • 2
    Fun fact: the `--tables` option will remove the `CREATE DATABASE` statement again, even if you have used `--databases`. Happened for me with `mysqldump Ver 10.18 Distrib 10.5.8-MariaDB, for osx10.15 (x86_64)` – Richard Kiefer Apr 21 '21 at 08:39
  • 1
    @RichardKiefer I ran into the same, turns out per the [documentation](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_tables) using `--tables` completely overrides the `--databases` option – jlaitio Jan 05 '23 at 11:21
41

The simplest solution is to use option -B or --databases.Then CREATE database command appears in the output file. For example:

mysqldump -uuser -ppassword -d -B --events --routines --triggers database_example > database_example.sql

Here is a dumpfile's header:

-- MySQL dump 10.13  Distrib 5.5.36-34.2, for Linux (x86_64)
--
-- Host: localhost    Database: database_example
-- ------------------------------------------------------
-- Server version       5.5.36-34.2-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `database_example`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `database_example` /*!40100 DEFAULT CHARACTER SET utf8 */;
Adiii
  • 54,482
  • 7
  • 145
  • 148
Baruch Lvovsky
  • 419
  • 4
  • 2
4

Here is how to do dump the database (with just the schema):

mysqldump -u root -p"passwd" --no-data --add-drop-database --databases my_db_name | sed 's#/[*]!40000 DROP DATABASE IF EXISTS my_db_name;#' >my_db_name.sql

If you also want the data, remove the --no-data option.

Bruno Peres
  • 15,845
  • 5
  • 53
  • 89
linuxaos
  • 87
  • 1
4

--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.

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_databases

Basically use -B in front of the (single) database to have CREATE DATABASE statament.

  • short: mysqldump -B <database>

  • long: mysqldump --databases <database>

If on MariaDB, see also MariaDB's mysqldump's --system=user option for keeping the user grants as well.

Elvin
  • 166
  • 7