mysqldump with create database line
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 yourmysqldump
statement. - if you have multiple databases to dump, you should use the option
--databases
or--all-databases
and theCREATE DATABASE
syntax will be addedautomatically
More information at MySQL Reference Manual
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 */;
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.