mysqldump with create database line
Solution 1
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 added automatically
More information at MySQL Reference Manual
Solution 2
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 */;
Solution 3
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.
Vince
Updated on July 05, 2022Comments
-
Vince almost 2 years
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?
-
Vince almost 11 yearsThat'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.
-
GregD almost 11 yearsYes, 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. -
jla over 9 yearsWhat 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
-
GregD over 8 years@jla - The MySQL version was 5.1 at the time of writing the answer. I assume this from the link to the Reference Manual.
-
Clandestine over 7 yearsNote 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 theinformation_schema
andperformance_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 dev.mysql.com/doc/refman/5.7/en/… -
Richard Kiefer about 3 yearsFun fact: the
--tables
option will remove theCREATE DATABASE
statement again, even if you have used--databases
. Happened for me withmysqldump Ver 10.18 Distrib 10.5.8-MariaDB, for osx10.15 (x86_64)