如何使用mysqldump命令备份和还原MySQL数据库

In this article, I am going to explain different ways to generate the backup in the MySQL database server. As we know, data is a valuable asset to the organization. As a database administrator, it is our primary and crucial job to keep the data available and safe. If the system or data center fails, database corruption, and data loss, we must be able to recover it within the defined SLA.

在本文中，我将解释在MySQL数据库服务器中生成备份的不同方法。 众所周知，数据是组织的宝贵资产。 作为数据库管理员，保持数据的可用性和安全性是我们的首要任务。 如果系统或数据中心发生故障，数据库损坏和数据丢失，我们必须能够在定义的SLA中恢复它。

Different database platforms provide various methods to generate the backup and restore the database. Many vendors provide state of the art software and hardware solutions that can help to back up the database, and it can restore the database within the defined RTO and RPO.

不同的数据库平台提供了各种生成备份和还原数据库的方法。 许多供应商提供了可以帮助备份数据库的最新软件和硬件解决方案，并且可以在定义的RTO和RPO中还原数据库。

Here, we are not going to discuss any third-party vendor’s backup solutions. I am going to explain about the native methods that are used to generate the backup of the database. We can generate the backup of the MySQL database using any of the following methods:

在这里，我们将不讨论任何第三方供应商的备份解决方案。 我将解释有关用于生成数据库备份的本机方法。 我们可以使用以下任何一种方法来生成MySQL数据库的备份：

1. Generate the backup using mysqldump utility
   使用mysqldump实用程序生成备份
2. Generate Incremental backups using Binary Log
   使用二进制日志生成增量备份
3. Generate backups using the Replication of Slaves
   使用从站复制生成备份

In this article, I am going to explain how we can use mysqldump to generate the backup of the MySQL database.

在本文中，我将解释如何使用mysqldump生成MySQL数据库的备份。

使用mysqldump实用程序生成备份 (Generate backup using mysqldump utility)

Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. The command can also be used to generate the output in the XML, delimited text, or CSV format.

Mysqldump是一个命令行实用程序，用于生成MySQL数据库的逻辑备份。 它产生可用于重新创建数据库对象和数据SQL语句。 该命令还可以用于生成XML，定界文本或CSV格式的输出。

This command is easy to use, but the only problem that occurs while restoring the database. As I mentioned, when we generate a backup of the MySQL database, it creates a backup file that contains SQL commands that necessary to rebuild or restore the database. Now, when we restore the database, the command executes all the SQL Statements to create tables and insert the data. If you have a large database, then the restoration process takes a long time to complete.

该命令易于使用，但唯一的问题是还原数据库时发生的。 如前所述，当我们生成MySQL数据库的备份时，它将创建一个备份文件，其中包含重建或还原数据库所必需SQL命令。 现在，当我们还原数据库时，该命令将执行所有SQL语句以创建表并插入数据。 如果您的数据库很大，则还原过程需要很长时间才能完成。

Note: By default, mysqldump command does not dump the information_schema database, performance_schema, and MySQL Cluster ndbinfo database.

注意：默认情况下，mysqldump命令不转储information_schema数据库， performance_schema和MySQL Cluster ndbinfo数据库。

If you want to include the information_schema tables, you must explicitly specify the name of the database in the mysqldump command, also include the —skip-lock-tables option.

如果要包括information_schema表，则必须在mysqldump命令中显式指定数据库的名称，还包括— skip-lock-tables选项。

There are lots of options and features that can be used with mysqldump. You can view the complete list of options here. I am going to some of the basic features. Following is the syntax of the mysqldump utility.

有很多可以与mysqldump一起使用的选项和功能。 您可以在此处查看选项的完整列表 。 我将介绍一些基本功能。 以下是mysqldump实用程序的语法。

mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

mysqldump -u [用户名] –p [密码] [选项] [数据库名] [表名]> [dumpfilename.sql]

The parameters are as following:

参数如下：

1. -u [user_name]: It is a username to connect to the MySQL server. To generate the backup using mysqldump, ‘Select‘ to dump the tables, ‘Show View‘ for views, ‘Trigger‘ for the triggers. If you are not using —single-transaction option, then ‘Lock Tables‘ privileges must be granted to the user
   -u [用户名]：它是连接到MySQL服务器的用户名。 要使用mysqldump生成备份，请选择 “ 选择 ”以转储表，“ 显示视图 ”为视图，“ 触发器 ”为触发器。 如果您不使用“ 单一交易”选项，则必须向用户授予“ 锁定表 ”特权
2. -p [password]: The valid password of the MySQL user
   -p [密码]： MySQL用户的有效密码
3. [option]: The configuration option to customize the backup
   [option]：用于自定义备份的配置选项
4. [database name]: Name of the database that you want to take backup
   [数据库名称]：要备份的数据库的名称
5. [table name]: This is an optional parameter. If you want to take the backup specific tables, then you can specify the names in the command
   [表名]：这是一个可选参数。 如果要获取备份特定表，则可以在命令中指定名称
6. “<” OR ”>”: This character indicates whether we are generating the backup of the database or restoring the database. You can use “>” to generate the backup and “<” to restore the backup
   “ <”或“>”：此字符表示我们是在生成数据库备份还是还原数据库。 您可以使用“ >”生成备份，使用“ < ”还原备份
7. [dumpfilename.sql]: Path and name of the backup file. As I mentioned, we can generate the backup in XML, delimited text, or SQL file so we can provide the extension of the file accordingly
   [dumpfilename.sql]：备份文件的路径和名称。 如前所述，我们可以生成XML，定界文本或SQL文件的备份，因此我们可以相应地提供文件的扩展名

生成单个数据库的备份 (Generate the backup of a single database)

For example, you want to generate the backup of the single database, run the following command. The command will generate the backup of the “sakila” database with structure and data in the sakila_20200424.sql file.

例如，您要生成单个数据库的备份，请运行以下命令。 该命令将使用sakila_20200424.sql文件中的结构和数据生成“ sakila ”数据库的备份。

mysqldump -u root -p sakila > C:\MySQLBackup\sakila_20200424.sql

mysqldump -u root -p sakila> C：\ MySQLBackup \ sakila_20200424.sql

When you run this command, it prompts for the password. Provide the appropriate password. See the following image:

当您运行此命令时，它会提示您输入密码。 提供适当的密码。 见下图：

Once backup generated successfully, let us open the backup file to view the content of the backup file. Open the backup location and double-click on the “sakila_20200424.sql” file.

备份成功生成后，让我们打开备份文件以查看备份文件的内容。 打开备份位置，然后双击“ sakila_20200424.sql ”文件。

As you can see in the above image, the backup file contains the various T-SQL statements that can be used to re-create the objects.

如上图所示，备份文件包含各种T-SQL语句，可用于重新创建对象。

生成多个数据库或所有数据库的备份 (Generate the backup of multiple databases or all the databases)

For example, you want to generate a backup of more than one database. You must add the —databases option in the mysqldump command. The following command will generate the backup of “sakila” and “employees” database with structure and data.

例如，您要生成一个以上数据库的备份。 您必须在mysqldump命令中添加— 数据库选项。 以下命令将生成具有结构和数据的“ sakila ”和“ employees ”数据库的备份。

mysqldump -u root -p –databases sakila employees > C:\MySQLBackup\sakila_employees_20200424.sql

mysqldump -u root -p –数据库sakila员工> C：\ MySQLBackup \ sakila_employees_20200424.sql

See the following image:

见下图：

Similarly, if you want to generate the backup of all the databases, you must use –all-databases option in the mysqldump command. The following command will generate the backup of all databases within MySQL Server.

同样，如果要生成所有数据库的备份，则必须在mysqldump命令中使用–all-databases选项。 以下命令将生成MySQL Server中所有数据库的备份。

mysqldump -u root -p –all-databases > C:\MySQLBackup\all_databases_20200424.sql

mysqldump -u root -p –所有数据库> C：\ MySQLBackup \ all_databases_20200424.sql

See the following image:

见下图：

生成数据库结构的备份 (Generate the backup of database structure)

If you want to generate the backup of the database structure, then you must use the –no-data option in the mysqldump command. The following command generates the backup of the database structure of the sakila database.

如果要生成数据库结构的备份，则必须使用–no-data mysqldump命令中的选项。 以下命令生成sakila数据库的数据库结构的备份。

mysqldump -u root -p –no-data sakila > C:\MySQLBackup\sakila_objects_definition_20200424.sql

mysqldump -u root -p –无数据sakila> C：\ MySQLBackup \ sakila_objects_definition_20200424.sql

See the following image:

见下图：

生成特定表的备份 (Generate the backup of a specific table)

If you want to generate the backup of a specific table, then you must specify the name of the tables after the name of the database. The following command generates the backup of the actor table of the sakila database.

如果要生成特定表的备份，则必须在数据库名称之后指定表名称。 以下命令生成sakila数据库的actor表的备份。

mysqldump -u root -p sakila actor payment > C:\MySQLBackup\actor_payment_table_20200424.sql

mysqldump -u root -p sakila actor支付> C：\ MySQLBackup \ actor_payment_table_20200424.sql

If you want to generate the backup of more than one tables, than you must separate the names of the tables with space, the following command generates the backup of actor and payment table of sakila database.

如果要生成多个表的备份，则必须用空格分隔表名，那么以下命令将生成sakila数据库的actor和付款表的备份。

生成数据库数据备份 (Generate the backup of database data)

If you want to generate the backup of the data without the database structure, then you must use the –no-create-info option in the mysqldump command. The following command generates the backup of data of the sakila database.

如果要在没有数据库结构的情况下生成数据备份，则必须使用–no-create-info mysqldump命令中的选项。 以下命令生成sakila数据库的数据备份。

mysqldump -u root -p sakila –no-create-info > C:\MySQLBackup\sakila_data_only_20200424.sql

mysqldump -u root -p sakila –no-create-info> C：\ MySQLBackup \ sakila_data_only_20200424.sql

See the following image.

参见下图。

Let us view the content of the backup file.

让我们查看备份文件的内容。

As you can see in the above screenshot, the backup file contains the various T-SQL statements that can be used to insert data in the tables.

从上面的屏幕快照中可以看到，备份文件包含各种T-SQL语句，可用于在表中插入数据。

恢复MySQL数据库 (Restore the MySQL Database)

Restoring a MySQL database using mysqldump is simple. To restore the database, you must create an empty database. First, let us drop and recreate the sakila database by executing the following command.

使用mysqldump还原MySQL数据库很简单。 要还原数据库，必须创建一个空数据库。 首先，让我们通过执行以下命令删除并重新创建sakila数据库。

mysql> drop database sakila;
Query OK, 24 rows affected (0.35 sec)
mysql> create database sakila;
Query OK, 1 row affected (0.01 sec)
mysql>

mysql>删除数据库sakila;
查询正常，受影响的24行（0.35秒）
mysql>创建数据库sakila;
查询正常，受影响的1行（0.01秒）
mysql>

When you restore the database, instead of using mysqldump, you must use mysql; otherwise, the mysqldump will not generate the schema and the data. Execute the following command to restore the sakila database:

还原数据库时，必须使用mysql ，而不要使用mysqldump 。 否则， mysqldump将不会生成模式和数据。 执行以下命令以还原sakila数据库：

mysql -u root -p sakila < C:\MySQLBackup\sakila_20200424.sql

mysql -u root -p sakila <C：\ MySQLBackup \ sakila_20200424.sql

Once command executes successfully, execute the following command to verify that all objects have been created on the sakila database.

命令成功执行后，执行以下命令以验证是否已在sakila数据库上创建了所有对象。

mysql> use sakila;
Database changed
mysql> show tables;

mysql>使用sakila;
数据库已更改
mysql>显示表格；

See the following image:

见下图：

恢复数据库中的特定表 (Restore a specific table in the database)

For instance, someone dropped a table from the database. Instead of restoring the entire database, we can restore the dropped table from the available backup. To demonstrate, drop the actor table from the sakila database by executing the following command on the MySQL command-line tool.

例如，某人从数据库中删除了一个表。 除了还原整个数据库，我们还可以从可用备份中还原删除的表。 为了演示，通过在MySQL命令行工具上执行以下命令，从sakila数据库中删除actor表。

mysql> use sakila;
Database changed
mysql> drop table actor;

mysql>使用sakila;
数据库已更改
mysql> drop table actor;

To restore the actor table, perform the following step by step process.

要还原参与者表，请执行以下逐步过程。

Step 1 :

第1步 ：

Create a dummy database named sakila_dummy and restore the backup of the sakila database on it. Following is the command.

创建一个名为sakila_dummy的虚拟数据库，并在其上还原sakila数据库的备份。 以下是命令。

mysql> create database sakila_dummy;
mysql> use sakila_dummy;
mysql> source C:\MySQLBackup\sakila_20200424.sql

mysql>创建数据库sakila_dummy;
mysql>使用sakila_dummy;
mysql>源C：\ MySQLBackup \ sakila_20200424.sql

Step 2:

第2步：

Backup the actor table to sakila_dummy_actor_20200424.sql file. Following is the command

将角色表备份到sakila_dummy_actor_20200424.sql文件。 以下是命令

C:\Users\Nisarg> mysqldump -u root -p sakila_dummy actor > C:\MySQLBackup\sakila_dummy_actor_20200424.sql

C：\ Users \ Nisarg> mysqldump -u root -p sakila_dummy actor> C：\ MySQLBackup \ sakila_dummy_actor_20200424.sql

Step 3:

第三步：

Restore the actor table from the “sakila_dummy_actor_20200424.sql” file. Following is the command on the MySQL command-line tool.

从“ sakila_dummy_actor_20200424.sql ”文件恢复actor表。 以下是MySQL命令行工具上的命令。

mysql> source C:\MySQLBackup\sakila_dummy_actor_20200424.sql

mysql>源C：\ MySQLBackup \ sakila_dummy_actor_20200424.sql

Execute the following command to verify the table has been restored successfully.

执行以下命令以验证表已成功还原。

mysql> use sakila;
Database changed
mysql> show tables;

mysql>使用sakila;
数据库已更改
mysql>显示表格；

See the following image:

见下图：

摘要 (Summary)

In this article, I have explained how we can use the mysqldump command-line utility to generate the following:

在本文中，我解释了如何使用mysqldump命令行实用程序生成以下内容：

1. The backup of MySQL database, table, or the structure of the database
   MySQL数据库，表或数据库结构的备份
2. Restore the MySQL database or table from the backup
   从备份还原MySQL数据库或表

翻译自: https://www.sqlshack.com/how-to-backup-and-restore-mysql-databases-using-the-mysqldump-command/