完整的mysqldump指南（附示例）

数据库是收集和存储海量数据的基本工具。所有复杂的应用程序都使用它们来存储信息。有很多数据库解决方案，但MySQL是最流行的解决方案之一。使用MySQL对于保持这些数据库的安全和平稳运行至关重要。

管理和备份服务器和数据库都可以在MySQL中完成。为了帮助这些复杂的过程顺利运行，开发了实用程序。Mysqldump是那些让开发人员的生活更轻松的工具之一。

在本指南中，我们将引导您了解什么是mysqldump，如何使用它，确定一些常见错误，并提供一些清晰的示例，以便您能够有效地使用mysqldump。

什么是mysqldump？

MySQL是一种已经存在多年的数据库系统，它仍然是网站最流行的选择之一。它是开源和敏捷的。开发人员可以使用这些数据库来存储网站可能需要的任何内容。在线数据库中存储的信息包括消费者信息、简单文本、图片库和网络信息。

Mysqldump是MySQL的关系数据库包的一部分。它用于将数据库中的所有数据备份到单个文本文件中。可以为单个数据库或它们的集合生成这些文件或“转储”。文件中的文本显示为一组SQL语句，以后可以将其重新构造为原始状态。

此工具的用途是将数据库导出到备份文件或将数据库移动到另一个web主机。其他分隔文本格式（如XML和CSV）也可以使用mysqldump生成。这些简单的查询使备份过程更容易。

备份数据的重要性

希望平稳运行的公司需要在不同时间点提供原始数据副本。如果没有备份策略，在发生灾难时就没有什么可以保护它们。由于数据很容易被破坏或随着时间的推移而丢失，因此数据可能永远丢失，这一点很难处理。恶意意图和自然灾害不是最坏情况发生的必要条件。

定期备份使公司能够通过重新加载以前的数据库来倒回时钟。如果有什么东西坏了或出了故障，这将成为系统的生命线。该公司还提供数据版本控制。可以返回到数据库和产品的不同版本。后来证明会破坏系统的关键更改可以撤消，然后您可以恢复旧版本而不会出现问题。

通过备份所有内容，可以迁移到新服务器或开发环境，而不用担心数据丢失。

如何使用mysqldump

通过使用mysqldump，开发人员可以获得。作为整个数据库备份的sql文件。要使用该工具，开发人员需要访问运行MySQL实例的服务器。必须授予导出任何内容所需的特权。还需要数据库的用户凭据，包括用户名和密码。

mysqldump用于导出数据的三种方式包括：

1. 在MySQL数据库中导出特定表
2. 导出单个数据库
3. 导出整个MySQL服务器

通过mysqldump导出MySQL表

确保您在安装了MySQL的计算机上。您还需要一个至少具有完全读取访问权限的有效数据库用户。这对于基本选项应该可以，但是更高级的命令可能需要额外的权限。按照顺序，启动一个终端，在那里您将发送备份表的命令。

匹配输入以符合以下mysqldump命令结构：

mysqldump [options] db_name [table_name ...]

对于live命令，将[options]替换为有效的选项名称或标志。其中最有可能包括-u和-p，它们代表用户和密码。MySQL。com有一个包含mysqldump支持的所有选项的综合表。当使用多个选项时，请注意它们的列出顺序，因为它们将按照从头到尾的顺序进行处理。此处，[table_name…]将替换为您正在导出的表的名称。不同的表必须用空格分隔。下面的示例用于备份名为“神秘”和“现金”的表，数据库的名称为db_cooper。

mysqldump -u username -p db_cooper mystery cash > file_name.sql

然后，您将为数据库用户提供密码，因为它不会与-p标志一起传递。>字符表示将创建转储文件的输出重定向。在这里，文件名是最终文件的名称。

导出MySQL数据库

导出数据库的步骤与导出表的步骤非常接近。命令的格式只做了一点小小的更改。您将需要相同的服务器访问权限和凭据。

匹配输入以符合以下mysqldump命令结构：

mysqldump -u username -p --databases db_larry db_curly db_moe > file_name.sql

您将导出的数据库位于–databases选项之后。空格字符分隔多个数据库。

导出MySQL服务器

整个服务器的命令也非常类似。

匹配输入以符合以下mysqldump命令结构：

mysqldump -u username -p --all-databases

命令本身非常基本，所有数据库都指示应该转储服务器上的所有内容。如果有特定的需求，那么命令的选项就在这里。添加-compatible将使导出的文件与旧的MySQL服务器或数据库系统兼容。

在Windows上使用PowerShell的开发人员需要包括-result文件作为选项。这将指定文件名，并确保输出为ASCII格式，以便稍后正确加载。

其他常见选项包括添加–no data将只备份数据库结构，使用–no create info备份没有任何结构的数据库。

导入mysqldump

导入一个。sql文件是直截了当的。唯一的问题是在导入任何内容之前确保目标服务器有一个空白数据库（请查看我们关于如何导入SQL文件的迷你指南）。

匹配输入以符合以下mysqldump命令结构：

mysql -u root -pmypassword wpdb < db_backup.sql

mysqlimport命令也适用于目标计算机上已存在的要还原的数据库：

mysqlimport -u root -pmypassword wpdb < wpdb_backup.sql

您还可以通过运行如下命令来导入所有数据库：

mysql -u root -pmypassword < alldb_backup.sql

--quick有什么作用？

Mysqldump可以通过以下两种方式之一运行。

1. 该工具可以一次抓取所有数据，将其缓冲在内存中，然后将其转储。
2. 它一行一行地转储表。

第二种方法在处理大型表时很重要。通过使用--quick标志，mysqldump可以读取大型数据库，而不需要大量的RAM将整个表放入内存中。这可确保在具有少量RAM和大型数据集的系统上正确读取和复制数据库。

不锁定表的转储和--skip lock tables标志

使用--skip lock tables可以防止在转储过程中锁定表。在备份无法在转储期间锁定的生产数据库时，这一点很重要。

通常建议在转储InnoDB表时使用--skip lock表。但是，对于MyISAM表，为了数据一致性，可能需要锁定表。

那么我应该使用——跳过锁表吗？

• 如果要备份InnoDB表，请选择“是”。将其与--单个事务相结合以获得最佳结果。

• 如果要在生产服务器上备份MyISAM表，请不要使用--skip lock tables，除非可以在备份过程中锁定数据库表。

  mysqldump -u root -pmypassword my_database --skip-lock-tables > my_database.sql

单事务标志的作用是什么？

简而言之，--single事务允许MySQL InnoDB转储具有数据库的一致状态。它告诉MySQL我们即将转储数据库，因此，表结构查询等破坏性更改将被阻止，以保持数据一致性。请注意，这仅适用于InnoDB表。

mysqldump -u root -pmypassword my_database --single-transaction > my_database.sql

注意：MyISAM表将不会受益于此标志，如果您希望保持其转储完整性，则应将其锁定。

如何转储大型表？

要转储大型表，可以将以下两个标志组合起来，-single transaction和--quick。

mysqldump -u root -pmypassword my_large_db --single-transaction --quick > my_large_db.sql

注意：这是InnoDB表的理想选择。因为它将使用更少的RAM，并且在不锁定表的情况下产生一致的转储。

如何使用mysqldump忽略表？

使用–-ignore table选项，可以在使用mysqldump时忽略表。

下面是一个只允许您忽略一个表的示例：

mysqldump -u root -pmypassword my_db –-ignore-table=my_db.table_to_ignore > my_db.sql

如您所见，格式如下：–-ignore table=DATABASE\u NAME。要忽略的表。

要忽略数据库中的所有表（或在转储所有数据库时忽略整个数据库），必须重复此参数以包含所有要忽略的表。

mysqldump -u root -pmypassword –-ignore-table="my_db.table1" –-ignore-table="my_db.table2" –-ignore-table="my_db.table3" > all_databases.sql

如何转储二进制BLOB数据？

有时，如果结果转储包含二进制数据，则可能会遇到问题。因此，在转储包含二进制数据的MySQL数据库时，可以使用以下mysqldump标志--hex blob。

在引擎盖下，它以十六进制格式转储它找到的二进制字符串（binary、VARBINARY、BLOB），以可靠的方式表示这些数据结构。

下面是一个正确转储二进制数据的mysqldump示例：

mysqldump -u root -pmypassword my_bin_db --hex-blob > my_bin_db.sql

“where”子句是否适用于mysqldump？

是的，此子句适用于命令行。这使得对需要从数据库转储的数据设置条件变得容易。如果有一家经营数十年的大型企业希望在2017年4月27日之后获取信息，那么本条款允许这种情况发生。where子句传递条件的字符串，并获取请求的特定记录。

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2017-04-27'" > wpdb_myrecord.sql

常见错误的故障排除

在此过程中，您可能会遇到一些MySQL常见错误，这些错误在某种程度上很容易缓解。下面我们将分享其中一些错误以及如何解决它们。

错误2013：转储表时在查询过程中与mysql服务器失去连接

要解决这个问题，您需要进入MySQL配置文件并增加一些值。添加这些内容后，保存并关闭文件，然后重新启动MySQL以使更改生效。

需要调整的值包括：

• 允许的最大数据包数
• 网络写入超时
• 网络读取超时
• innodb\u缓冲区\u池\u大小

对文件的调整将在[mysqld]和[mysqldump]部分下进行，如下所示：

[mysqld]
innodb_buffer_pool_size=100M
max_allowed_packet=1024M

[mysqldump]
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600

错误2020:转储表时获取的数据包大于“最大允许数据包”字节

如果需要备份的数据库很大，并且文件大小最终大于允许的最大数据包大小，则会弹出此错误。

这个错误可以通过进入MySQL配置文件并增加[mysqld]和[mysqldump]部分中的max_allowed_数据包值来修复。完成后保存并关闭文件，然后重新启动MySQL以使更改生效。

更改如下所示：

[mysqld]
max_allowed_packet=desired-value

[mysqldump]
max_allowed_packet=desired-value

表不存在（1146），无法执行“显示创建表x”

备份过程中可能会删除表。如果是这种情况，可以使用--ignore table选项从mysqldump命令中限制某些表。要标识表，必须同时声明数据库和表名。

mysqldump -u root -pmypassword example_db --ignore-table=name_of_table > db_backup.sql

通过多次列出该选项，可以忽略多个表：

mysqldump -u root -pmypassword example_db --ignore-table=table --ignore-table=tableaux > db_backup.sql

选择数据库将返回“未知数据库”

当您在命令行中将-p标志与密码一起使用并且-p和mypassword之间有一个空格时，最常发生此错误。如果在使用“root”作为密码为“base face”的用户时发生这种情况，则会出现一个错误，说明“未知数据库base face”

正确的输入如下所示：

mysqldump -u root -pbase-face wpdb > wpdb_backup.sql

选择数据库时出现错误1044

如果尝试进行转储的用户没有访问数据库所需的权限，则会发生此错误。登录MySQL并将这些权限分配给用户将解决此问题。输入命令：

然后输入正确的密码，然后继续向所选用户授予权限。

GRANT ALL PRIVILEGES ON wpdb.* TO 'not_a_hacker'@'inconspicuous_host';

之后，刷新特权并通过输入以下命令退出MySQL：

尝试连接到mysqldump时拒绝用户访问

这个错误有几个可能的原因。这里有三个最常见的问题原因。

错误的mysqldump命令

如果使用了错误的命令，则会出现此错误。该命令可能基本正确，但在mysqldump格式中缺少一个关键元素。基本命令如下所示：

mysqldump -u user -pmypasword database > database.sql

如果您未能指定用户名或密码，则它将返回以下消息：

mysqldump: Got error: 1045: "Access denied for user 'user' @ 'localhost' (using password: NO)" when trying to connect

不允许远程主机连接到数据库

如果试图在远程服务器上执行备份，则会出现此错误。MySQL的配置设置为不允许外部连接。在这里，本地主机是唯一允许进行备份的主机。这是一个安全措施，所以这是一个很好的措施，但如果需要更改，请转到配置并更改MySQL以允许从远程主机进行连接。

错误的用户凭据

如果在连接数据库时尝试使用错误的用户名和密码组合，则会发生此错误。MySQL无法验证请求是否真实，并返回错误。您必须使用正确的凭据再次发出请求，确保原始命令中没有任何拼写错误，因为这是最容易犯的错误。

结论

Mysqldump是一个非常有用的工具，可以用最少的命令来帮助备份数据库。一个命令允许将整个数据库输出到单个文本文件中。该工具用途广泛，足以备份所需的数据库部分，并提供多种选项来更改需要保存的数据。