如何使用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. mysqldumpmysqldump
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 usern