mysqldump with create database line

Question

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

User · Accepted Answer

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

User · Answer

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     gt my db name sql   If you also want the data  remove the --no-data option

User · Answer

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

[mysql] mysqldump with create database line

Examples related to mysql

Examples related to database

Examples related to backup

Examples related to mysqldump