Mysql 简明教程

MySQL - Database Export

MySQL 是用于存储和管理数据的最流行的关系数据库系统之一。它以表和视图的形式组织数据，以便数据处理变得更容易，使组织更喜欢使用 MySQL 来管理其公司的机密数据。由于他们的数据高度机密，因此必要时备份数据库并恢复它。因此，我们执行数据库导出。

MySQL is one of the most popular relational database systems used to store and manage data. It structures data in the form of tables and views so that data handling becomes easier making organizations prefer using MySQL to manage their company’s confidential data. Since their data is highly confidential, it becomes necessary to back up the database and restore it whenever necessary. Hence we perform database export.

Exporting a database 在 MySQL 中通常用于备份目的或在服务器之间传输数据。你可以导出整个数据库或其一部分。最简单的导出数据库的方法是使用 mysqldump 命令行工具。

Exporting a database in MySQL is commonly used for backup purposes or transferring data between servers. You can export entire database or just a portion of it. The simplest way of exporting a database is by using the mysqldump command-line tool.

Exporting Database using mysqldump

mysqldump 命令行工具用于在 MySQL 中创建数据库的备份。它可以用于备份整个数据库、特定表，甚至基于表的特定行。

The mysqldump command-line tool is used in MySQL to create backups of databases. It can be used to back up an entire database, specific tables, or even specific rows based of a table.

以下是导出数据库的 mysqldump 命令的语法 -

Following is the syntax of mysqldump command to export a database −

$ mysqldump -u username -p database_name > output_file_path

其中，

Where,

username: It is the MySQL username to use when connecting to the database.
database_name: It is the name of the database to be exported.
output_file_path: It is the path of the backup file. This is where the backup data will be stored.
>: This symbol exports the output of the mysqldump command into a file named output_file_path.

Example

首先，使用以下查询创建一个名为 TUTORIALS 的数据库 −

First of all, create a database named TUTORIALS using the following query −

Create database TUTORIALS;

执行以下查询，将当前数据库选择为 TUTORIALS −

Execute the below query to select the current database as TUTORIALS −

USE TUTORIALS;

我们还在上述创建的数据库中创建一个名为 CUSTOMERS 的表格 −

Let us also create a table named CUSTOMERS in the above-created database −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以下查询向 CUSTOMERS 表格中插入 7 条记录 −

The following query inserts 7 records into the CUSTOMERS table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

此处，我们使用 mysqldump 命令行语句将 TUTORIALS 数据库导出到“datadump.sql”文件。执行以下语句后，我们需要输入 MySQL 服务器密码。

Here, we are using the mysqldump command-line statement to export the TUTORIALS database to the 'datadump.sql' file. Once we execute the below statement, we need to enter our MySQL server password.

$ mysqldump -u root -p TUTORIALS > data-dump.sql

上述命令不会产生任何可视输出。相反，将把“data-dump.sql”文件保存在执行命令的命令提示符或终端的当前工作目录中。

The above command will not produce any visual output. Instead, the 'data-dump.sql' file will be saved in the current working directory of the command prompt or terminal where you executed the command.

Exporting only Specific Tables in Database

我们还可以使用 mysqldump 命令行工具仅导出数据库中的特定表格。为此，我们使用以下语法 −

We can also export only specific tables in a database using the mysqldump command-line tool. To do so, we use the following syntax −

mysqldump -u username -p database_name table1 table2 ... > output_file.sql

Example

在导出之前，让我们使用以下查询在上述创建的 TUTORIALS 数据库中创建两个新表格（ STUDENTS 和 EMPLOYEES ） −

Before exporting, let us create two new tables (STUDENTS and EMPLOYEES) in the above-created TUTORIALS database using the following query −

CREATE TABLE STUDENTS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   FEES DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

此处，我们正在创建 EMPLOYEES 表格 −

Here, we are creating the EMPLOYEES table −

CREATE TABLE EMPLOYEES (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARIES DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

让我们使用以下查询获取 TUTORIALS 数据库中的表格列表 −

Let us fetch the list of tables in the TUTORIALS database using the below query −

Show Tables;

正如我们在以下输出中看到的那样，我们有三个表存在于 TUTORIALS 数据库中 −

As we can in the output below, we have three tables present in TUTORIALS database −

现在，让我们将名为 EMPLOYEES 和 STUDENTS 的两个表导出到“datadump2.sql”中，如下所示 −

Now, let us export two tables named EMPLOYEES and STUDENTS into the 'datadump2.sql' as follows −

$ mysqldump -u root -p TUTORIALS employees students > output_file.sql

一旦我们执行了上述查询，它将不再显示任何输出。相反，它将两个表格的数据导出到指定的文件中。

Once we executed the above query, it wont display any ouptut. Instead, it exports the data of both the tables into the specified file.

Exporting all Databases in a Host

例如，您的主机中有 multiple databases ，并且您希望在单个查询中导出所有这些主机。在这种情况下，我们可以使用 mysqldump 命令的 "--all-databases" 选项。

For instance, you have multiple databases in your host and you want to export all of them in a single query. In such scenario, we can use the "--all-databases" option of mysqldump command.

Example

以下是使用 --all-databases 选项导出主机中所有数据库的查询 −

Following is the query to export all the databases in a host using the --all-databases option −

$ mysqldump -u root -p --all-databases > database_dump.sql

上述命令不会在屏幕上显示任何可见输出。相反，将把“database_dump.sql”文件保存在您运行命令的命令提示符或终端的当前工作目录中。

The above command won’t show any visible output on the screen. Instead, the 'database_dump.sql' file will be saved in the current working directory of the command prompt or terminal where you ran the command."