Mysql 简明教程

MySQL - Copy Database

在现代，公司依靠数据库来存储诸如财务交易、客户资料和员工记录等关键信息。定期维护数据库副本非常重要，因为供电过压和磁盘崩溃始终可能导致数据丢失。因此，定期备份数据库对于有效的数据管理至关重要。

In modern times, companies rely on databases to store crucial information like financial transactions, customer profiles, and employee records. It is very important to maintain regular copies of databases there can always be a chance of data loss from power surges and disk crashes. Therefore, regular backups of databases are crucial for effective data management.

Copy Database in MySQL

在 MySQL 中，复制数据库涉及创建现有数据库的确切副本，包括其架构和数据。这几乎类似于拥有数据库的备份。重要的是要确保在复制后对原始数据库所做的任何更改也反映在已复制的数据库中（如果需要的话）。

In MySQL, copying a database involves creating an exact duplicate of an existing database, including its schema and data. This is almost similar to having a backup of a database. It is important to ensure that any changes made to the original database after the copy is made are also reflected in the copied database, if necessary.

为了创建数据库副本，SQL Server 提供了 Copy Database 语句。但是，MySQL 中不可用。因此，为了创建数据库副本，我们需要手动将一个数据库的内容转储到另一个数据库。

To create a copy of a database SQL Server provides the Copy Database statement. But, this is not available in MySQL. Therefore, to create copy of a database we need to dump the contents of one database to other manually.

以下三个步骤涉及复制数据库 −

The following are three steps that involve in copying a database −

  1. First of all, we need to create a new database.

  2. Then, we need to export the original database using mysqldump.

  3. Finally, importing the exported data into the new database.

Example

首先，让我们使用以下查询在 MySQL 服务器中创建一个数据库 −

First of all, let us create a database in the MySQL server using the following query −

CREATE DATABASE testdb;

我们可以使用 SHOW DATABASES 语句验证是否创建了数据库 testdb 。

We can verify whether the database testdb is created or not using the SHOW DATABASES statement.

SHOW DATABASES;

如我们从以下输出中看到的，testdb 数据库已成功创建。

As we can see the output below, the testdb database has been created successfully.

数据库成功创建后，我们需要使用 USE 语句将当前数据库更改为 'testdb' ，以便我们执行的任何操作，例如创建表，都将存储在此数据库中。

Once the database is created successfully, we need to change the current database to 'testdb', using the USE statement so that any operations we perform such as creating a table will be stored in this database.

USE testdb;

现在，让我们使用 CREATE 查询按照如下方式创建一个名为 CUSTOMERS 的表 −

Now, let us create a table named CUSTOMERS using the CREATE query as follows −

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)
);

以下查询将 7 条记录插入到上述创建的表中：

The following query inserts 7 records into the above-created 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 );

使用以下查询，我们可以验证是否在“testdb”数据库中创建了 CUSTOMERS 表 −

Using the following query, we can verify whether the table CUSTOMERS is created in 'testdb' database or not −

SHOW TABLES;

该表已成功创建于 testdb 数据库中。

The table is successfully created in the testdb database.

Creating Copy database (Manually)

如前所述，在 MySQL 中，为了创建现有数据库的副本，我们需要创建一个单独的数据库并将它的内容手动转储到新创建的数据库中。

As discussed earlier in MySQL to create a copy of an existing database we need to create a separate database and dump the contents of it to the newly created database manually.

以下语句创建一个名为 testdb_copy 的数据库（我们需要将以上创建的 testdb 数据库的内容复制到这里）。

Following statement creates a database named testdb_copy (to which we need to copy the contents of the testdb database created above).

CREATE DATABASE testdb_copy;

一旦我们的源数据库（testdb）和目标数据库（testdb_copy）都已经准备就绪，我们需要按照以下步骤手动将数据从一个数据库复制到另一个数据库。

Once both our source (testdb) and destination (testdb_copy) databases are ready, we need to follow the steps given below to manually copy the data from one to another.

Step1 − 打开 command prompt ，浏览 MySQL 服务器的 bin 文件夹。例如，我们已在 C\ Program Files 目录中安装了 MySQL，因此，以下命令将带我们进入 bin 文件夹 −

Step1 − Open the command prompt, browse through the bin folder of the MySQL server. For instance, we have installed MySQL in the C\ Program Files directory so, the following command will take us to the bin folder −

C:\> CD C:\Program Files\MySQL\MySQL Server 8.0\bin

Step 2 − 使用 mysqldump 工具，我们可以将数据库对象和数据复制到 .sql 文件中。此处，我们将 testdb 数据库的内容导出到“D:\Database_backup”中名为 "testdb.sql" 的文件中。

Step 2 − Using the mysqldump tool, we can copy the database objects and data into a .sql file. Here, we are exporting the contents of the testdb database to a file named "testdb.sql" located at "D:\Database_backup".

Note −（>）操作用于将数据库从一个位置导出到另一个位置。

Note − The (>) operator is used for exporting the database from one location to another.

mysqldump -u root -p testdb > D:\database_backup\testdb.sql

Step 3 − 将“testdb.sql”文件的内容导入到目标数据库（在本例中为 "testdb_copy" ）。

Step 3 − Import the contents of the "testdb.sql" file into the destination database (in our case "testdb_copy").

Note −（<）操作用于将数据库从一个位置导入到另一个位置。

Note − The (<) operator is used for importing the database from one location to another.

mysql -u root -p testdb_copy < D:\database_backup\testdb.sql

Verification

为了确认数据和数据库对象是否已导入到 testdb_copy 数据库，我们首先需要使用 MySQL Command Line Client 中的以下查询使用当前数据库 −

To verify whether the data and database object is imported into the testdb_copy database, first, we need to use the current database using the following query in the MySQL Command Line Client −

USE testdb_copy;

如果 testdb 的内容已成功复制到 testdb_copy 中，我们应该能够在表的列表中找到 customers 表（该表是以前创建的）。

If the contents of the testdb are copied in to the testdb_copy successfully, we should be able to find the customers table in the list of tables (which is created earlier).

因此，让我们使用以下查询确认 “testdb”数据库的数据是否已复制到 “testdb_copy” 数据库 −

Therefore, let us verify whether the data from the "testdb" database have been copied to the "testdb_copy" database or not using the following query −

SHOW TABLES;

正如我们在下面的列表中看到的，所有数据库对象和数据已经成功复制。

As we can see in the list below, all the database objects and data have been successfully copied.

Copy Database Without MySQLdump

如果我们想在不使用 mysqldump 工具的情况下复制数据库，我们必须手动在目标数据库中创建每个表，并从当前数据库中现有的表中复制所有数据。这是一个重复的过程，需要对需要复制的每个表执行此过程。

If we want to copy a database without using the mysqldump tool, we must manually create each table in the destination database and copy all the data from the tables present in the current database. This is a repitetive process that should be done for each table that needs to be copied.

Example

让我们使用以下查询在 MySQL 服务器中创建一个新数据库 −

Let us create a new database in the MySQL server using the following query −

CREATE DATABASE Tutorials;

我们可以使用以下查询确认数据库 Tutorials 是否已创建 −

We can verify whether the database Tutorials is created or not using the following query −

SHOW DATABASES;

正如我们在下面的输出中看到的，'Tutorials' 数据库已成功创建。

As we can see the output below, the 'Tutorials' database has been created successfully.

现在，我们正在将当前数据库切换到 Tutorials ，以便我们执行的任何操作（例如创建表）都会存储在此数据库中。

Now, we are switching the current database to Tutorials, so that any operations we perform such as creating a table will be stored in this database.

USE Tutorials;

一旦我们切换完毕，使用以下查询创建一个名为 CUSTOMERS 的表 −

Once we have switched, create a table named CUSTOMERS using the following query −

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)
);

此处，我们使用以下 INSERT INTO 语句将一些记录插入到表中 −

Here, we are inserting some records into the table using the INSERT INTO statement below −

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 );

使用以下查询，让我们创建另一个数据库 Tutorials_copy ，我们希望将 Tutorials 数据库的所有数据对象和数据复制到这里。

Using the below query, let us create another database Tutorials_copy, where we want to copy all the data objects and data of the Tutorials database.

CREATE DATABASE Tutorials_copy;

我们可以使用以下查询验证数据库 Tutorials_copy 是否已创建：

We can verify whether the database Tutorials_copy is created or not, using the following query −

SHOW DATABASES;

该数据库已创建。

The database has been created.

现在，使用以下查询将当前数据库切换到“tutorials_copy”：

Now, switch the current database to 'tutorials_copy' using the following query −

USE Tutorials_copy;

在这里，我们正在“Tutorials_copy”数据库中创建一个名为“CUSTOMERS”的空表，其模式与“Tutorials”中的原始“CUSTOMERS”表相同：

Here, we are creating an empty table named "CUSTOMERS" in the 'Tutorials_copy' database with the same schema as the original 'CUSTOMERS' table in the 'Tutorials' −

CREATE TABLE Tutorials_copy.customers LIKE Tutorials.customers;

此查询将所有数据从“Tutorials”数据库中的原始“customers”表插入到“tutorials_copy”数据库中的新“customers”表中。

This query inserts all the data from the original 'customers' table in the 'Tutorials' database into the new 'customers' table in the 'tutorials_copy' database.

INSERT Tutorials_copy.customers SELECT * FROM Tutorials.customers;

我们可以验证数据库对象和数据是否已从“Tutorials”数据库复制到“Tutorials_copy”数据库。

We can verify whether the database objects and data from the 'Tutorials' database have been copied to the 'Tutorials_copy' database or not.

SHOW TABLES;

Output

正如我们在下面的列表中看到的那样，该表已成功复制：

As we can see in the list below, the table has been successfully copied −

让我们还要检索“Tutorials_copy”数据库中 CUSTOMERS 表的记录，以验证记录是否已复制：

Let us also retrieve the records of CUSTOMERS table in 'Tutorials_copy' database to verify whether the records have been copied or not −

Select * from CUSTOMERS;

正如我们在下面的“Tutorials_copy”数据库中看到的 CUSTOMERS 表中，记录已成功复制：

As we can see the CUSTOMERS table in 'Tutorials_copy' database below, the records have been successfully copied −