MySQL mysqldump 与创建数据库行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16452523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysqldump with create database line
提问by Vince
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?
我正在将我的文件移动到另一台计算机上，我想做的一件事是传输我的 MySQL 数据库中的数据。我想将数据库转储到 .sql 文件中，但还要在文件中包含 create database db_name，这样我只需将文件导入 mysql 而不必手动创建数据库。有没有办法做到这一点？
回答by GregD
By default mysqldumpalways creates the CREATE DATABASE IF NOT EXISTS db_name;statement at the beginning of the dump file.
默认情况下，mysqldump始终CREATE DATABASE IF NOT EXISTS db_name;在转储文件的开头创建语句。
[EDIT] Few things about the mysqldumpfile and it's options:
[编辑] 关于mysqldump文件及其选项的内容很少：
--all-databases, -A
--all-databases, -A
Dump all tables in all databases. This is the same as using the --databasesoption and naming all the databases on the command line.
转储所有数据库中的所有表。这与使用该--databases选项并在命令行上命名所有数据库相同。
--add-drop-database
--add-drop-database
Add a DROP DATABASEstatement before each CREATE DATABASEstatement. This option is typically used in conjunction with the --all-databasesor --databasesoption because no CREATE DATABASEstatements are written unless one of those options is specified.
DROP DATABASE在每个CREATE DATABASE语句之前添加一个语句。此选项通常与--all-databasesor--databases选项结合使用，因为CREATE DATABASE除非指定了这些选项之一，否则不会编写任何语句。
--databases, -B
--databases, -B
Dump several databases. Normally, mysqldumptreats 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 DATABASEand USEstatements are included in the output before each new database.
转储多个数据库。通常，mysqldump将命令行上的第一个名称参数视为数据库名称，将后面的名称视为表名称。使用此选项，它将所有名称参数视为数据库名称。CREATE DATABASE和USE语句包含在每个新数据库之前的输出中。
--no-create-db, -n
--no-create-db, -n
This option suppresses the CREATE DATABASEstatements that are otherwise included in the output if the --databasesor --all-databasesoption is given.
CREATE DATABASE如果给出了--databasesor--all-databases选项，则此选项会禁止包含在输出中的语句。
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.
前段时间，有类似的问题实际上询问在文件的开头（对于XML文件）没有这样的声明。该问题的链接是here。
So to answer your question:
所以要回答你的问题：
- if you have one database to dump, you should have the
--add-drop-databaseoption in yourmysqldumpstatement. - if you have multiple databases to dump, you should use the option
--databasesor--all-databasesand theCREATE DATABASEsyntax will be added automatically
- 如果您有一个要转储的数据库，则您
--add-drop-database的mysqldump语句中应该有该 选项。 - 如果你有多个数据库要转储，你应该使用选项
--databasesor--all-databases并且CREATE DATABASE语法将自动添加
More information at MySQL Reference Manual
MySQL 参考手册中的更多信息
回答by Baruch Lvovsky
The simplest solution is to use option -B or --databases.Then CREATE database command appears in the output file. For example:
最简单的解决方案是使用选项 -B 或 --databases。然后 CREATE database 命令出现在输出文件中。例如：
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 */;
回答by linuxaos
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-dataoption.
如果您还需要数据，请删除该--no-data选项。
