如何备份MySQL数据库

备份站点文件相对简单。本质上，您将它们全部放在一个存档中，您可以稍后提取和恢复所有内容。然而，当谈到生成MySQL 数据库的备份时，事情就有点复杂了。今天，我们将研究不同类型的数据库备份，我们将看到一些最流行的创建和恢复网站数据安全副本的技术。

MySQL 备份的类型

MySQL 是一个 关系数据库管理系统。信息存储在表中，具有不同数据集之间的关系。实际上，这些关系构建了数据库的结构。数据库的工作备份必须能够按照最初组织的方式重新创建表和它们之间的关系。否则，您的网站将无法运行。

MySQL 中有两种主要的数据库备份类型：

逻辑备份

逻辑备份将数据和数据结构导出（或转储）到 SQL 文件。SQL 文件本身包含重建数据库所需的SQL 语句（例如，CREATE DATABASE、CREATE TABLE等）。因为SQL 语句往往是通用的，逻辑备份通常用于将数据库从一台主机移动到另一台主机。

物理备份

物理备份是数据库的datadir 目录的副本。所有表格和其中的数据都以其原始文件格式复制，并且只要保持结构不变 ，就可以在功能齐全的数据库中恢复。物理备份速度更快，但只有当它们从相同MySQL 版本的相同数据库引擎恢复时才能工作。因此，它们并不总是适合将数据库从一个环境移动到另一个环境。您的备份策略应由许多不同的因素决定，包括主机设置、数据库大小和您的需求。

逻辑备份往往更常见，因为我们很快就会发现，可以创建它们的工具通常很容易获得。此外，逻辑备份可以复制部分数据库，从而在处理数据库特定部分的数据损坏问题时为您提供更大的灵活性和更快的解决方案。总的来说，虽然物理备份在一些特定情况下确实有意义，但对于普通网站所有者来说，逻辑备份更容易设置和管理。这就是为什么今天的文章将重点关注它们。

通过命令行创建数据库备份

如果您使用命令行，您将使用mysqldump 实用程序导出 MySQL 数据库 。缺少图形用户界面意味着许多人可能更喜欢其他方法之一。但是，如果您决定试一试，您会发现这个过程并没有太复杂。

您的第一项工作是通过 SSH 访问您的主机帐户。现代操作系统支持开箱即用的协议。Windows 计算机通过PowerShell 或命令提示符工具连接到远程服务器，而基于 Unix 的系统使用终端来连接。有些人更喜欢 PuTTY 等专用 SSH 客户端，如果您有 SPanel 服务器，则可以直接通过用户界面主页上提供的 SSH 终端打开 shell。

在继续之前，请确保您有权访问对要备份的数据库具有特权的MySQL 用户帐户。如果您有权访问root或任何其他具有 SYSTEM_USER权限的帐户，则可以从您想要的任何数据库中导出数据。值得一提的是，如果其中一个表损坏，mysqldump 将无法备份数据库。为确保一切顺利，您可以使用mysqlcheck命令行实用程序来检查错误。

该命令如下所示：

$ mysqlcheck [数据库名称] -u [你的 MySQL 用户名] -p

提供用户帐户密码后，mysqlcheck 将扫描所有表以查找数据损坏。理想情况下，输出将如下所示：

要检查所有数据库是否有错误，请添加–all-databases选项。扫描数据库中的错误后，您可以继续进行备份。

mysqldump 实用程序涵盖了很多场景。以下是最常见的：

通过命令行备份单个数据库。

要备份您帐户中的其中一个数据库，您需要一个如下所示的命令：

$ mysqldump -u [您的 MySQL 帐户的用户名] -p [数据库的名称] > [要将数据导出到的文件的名称]

使用上述命令，用户test_user将数据库test_database 导出 到文件backup.sql。您需要做的就是输入用户帐户的密码。如果backup.sql不存在，MySQL 会自动创建它。

从选定的数据库备份特定表

mysqldump 实用程序也可以只备份特定的表。只需在数据库名称后添加表的名称。这是一个例子：

在上面的屏幕截图中，test_user正在将表wp_posts从test_database2 备份到posts.sql文件。要将多个表转储到同一个文件，只需添加它们的名称并用空格分隔即可。例如：

$ mysqldump -u test_user -p test_database2 wp_posts wp_options wp_users wp_links > wordpress.sql

备份多个数据库

您可以使用 mysqldump将多个数据库导出到同一个SQL 文件。为此，请使用上面的命令，添加–databases选项和以空格分隔的数据库名称。例如：

$ mysqldump -u test_user -p –databases test_database test_database2 test_database3 > backup.sql

备份所有可访问的数据库

如果要备份您有权访问的所有数据库，请将数据库名称替换为–all-databases选项。例如：

这将转储除information_schema、performance_schema和任何其他默认MySQL 模式之外的所有数据库。要将它们包含在备份中，请添加--skip-lock-tables选项。

仅备份数据库结构

有时，您可能需要生成数据库结构的备份，但其中没有存储数据。如果包含–no-data选项，mysqldump 实用程序可以执行此操作。该命令如下所示：

备份没有结构的数据

反过来也是可能的——你可以备份没有结构的数据。选项是 - no-create-info，命令如下所示：

mysqldump 实用程序不会打印一条消息告诉您它已成功备份您的数据库，因此您可以使用ls命令确认 SQL 文件已创建。

使用 mysqldump 转储大型数据库

在较大的数据库上使用mysqldump可能会更棘手。如果要备份大量 GB，服务器负载可能会增加，如果数据库非常大，整个操作可能会失败。第一个问题是SQL 文件占用的存储空间量。大型数据库会产生大量备份，但好消息是您可以通过gzip传输mysqldump 的输出并在将 SQL 文件写入磁盘之前对其进行压缩。

该命令将如下所示：

$ mysqldump -u [你的 mysql 帐户的用户名] -p [你的数据库的名称] | gzip > [文件名].sql.gz

一方面，这减少了生成备份的大小，另一方面，它减少了IO 负载。

mysqldump 实用程序还有几个可能也有帮助的其他选项。–opt选项包含几个优化转储操作的不同命令参数。请记住，它们会使备份更难被其他数据库系统理解，因此如果您想将数据库迁移到新主机，这可能不是最佳方法。

–quick选项也可以工作。默认情况下，mysqldump 在将每个表行转储到 SQL 文件之前将其存储到内存中。使用–quick 选项，数据直接传输到备份。

如果大型备份操作失败，可以尝试增加MySQL配置文件（my.cnf文件通常存放在/etc）中的max_allowed_pa​​cket参数。这通常只有在您尝试转移数十 GB 的信息时才有必要，而且不能保证成功。

使用 mysqldump 备份这么多数据绝非易事，许多专家建议，在这种情况下，最好使用物理备份解决方案，如MySQL Enterprise Backup或Percona XtraBackup。

使用 phpMyAdmin 创建数据库备份

许多共享和托管主机帐户都预装了 phpMyAdmin。它的默认登录 URL 是https://[你的服务器的 IP]/phpmyadmin（一些主机出于安全原因更改它），尽管它通常也可以从控制面板访问。它具有易于使用的图形界面，这意味着您无需学习任何新命令即可管理您的数据库。您还可以通过单击几下鼠标将数据导出到 SQL 文件。这是如何做的。

备份单个数据库

打开 phpMyAdmin 后，您会看到主机帐户上所有数据库的列表。

单击要备份的文件，然后转到“导出”选项卡。

您可以选择导出方法和格式。最好保留SQL 格式，因为这会在您需要恢复数据库时提供更多选择。至于方法，默认选择Quick，如果您就这样保留它，phpMyAdmin会将整个数据库（数据和结构）转储到SQL 文件中。如果您选择自定义单选按钮，您会看到更多选项。首先，您可以选择要转储的表。您可以备份结构、数据或两者。

再往下，您可以让phpMyAdmin在导出时自动重命名数据库、它的表和/或列。这也是您指定文件名模板的地方。默认情况下，它设置为“@ DATABASE@ ”，这意味着 phpMyAdmin 将以您的数据库命名 SQL 文件。您可以锁定数据库的表或将它们导出为单独的文件。此部分还提供了导出文件的编码和压缩选项。您可以跳过超过一定大小的表并以文本形式查看输出，而不是将其转储到 SQL 文件中。

在Format-specific options部分，您决定是否在 SQL 文件中包含注释元数据和其他格式化元素。您可以让phpMyAdmin 将数据库导出为事务，禁用外键检查，并将视图转储为表。还有一个下拉菜单可让您使备份向后兼容许多旧系统。

接下来，您有对象创建选项部分。它主要处理将要添加到 SQL 文件中的语句，它们涉及 SQL 文件重建数据库的方式。例如，当您需要将数据恢复到新的空数据库时， CREATE DATABASE 和 USE语句可以为您节省一些时间。另一方面，如果您计划将数据恢复到现有数据库中，则可以让 DROP 语句覆盖旧数据。如果启用了IF NOT EXISTS选项，您的备份将在尝试创建它们之前检查匹配表，并且使用AUTO_INCREMENT 复选框，您可以将备份数据附加到现有表。

在数据创建选项部分，您会找到更多与数据恢复方式相关的设置。在某些情况下可能需要调整一些高级选项。但是，对于大多数网站所有者而言，默认配置应该可以正常工作。

调整完设置后，单击“Go ”生成 SQL 文件。

备份多个数据库

备份多个数据库的步骤与转储单个数据库的步骤几乎相同。不同的是，您不需要从左侧的菜单中选择数据库，而是需要直接从主页转到“导出”选项卡。

如果您想一次备份所有数据库，请将导出方法设置为Quick并将格式设置为SQL，然后单击Go让 phpMyAdmin 生成备份。如果选择自定义方法，则可以选择要备份的数据库。单击取消全选，然后我们使用Ctrl/Cmd键标记您要备份的内容。

再往下，在Format-specific options部分，您可以决定是否转储所选数据库中的数据，仅转储其结构，或同时转储两者。

其余设置与导出单个数据库时看到的设置相同。

使用 MySQL Workbench 创建数据库备份

MySQL Workbench 是另一个免费的数据库管理工具。您可以在家用计算机上进行设置并远程控制您的数据库。您首先需要连接到您的托管帐户。主页上的MySQL 连接部分有一个+按钮。该对话框询问您连接的类型和详细信息。最好的选择是通过 SSH 使用标准 TCP/IP 协议连接到您的服务器。这样，通信将被加密，您的数据将是安全的。您必须提供SSH 登录凭据以及您的MySQL 用户帐户的凭据。填写所有必填字段后，单击测试连接以确保一切正常。如果是这样，请点击确定以保存连接。

连接到服务器后，打开Navigator部分中的Administration 选项卡并单击Data Export。

在下一个窗口中，您将看到您的用户有权访问的数据库和不同的导出选项。

您可以使用旁边的复选框选择您想要的数据库。当您单击一个数据库时，您将看到它的所有表，因此您可以从备份中排除特定的表。

在数据库和表的列表下方，您有一个下拉菜单，可让您决定是否要备份数据库、结构或两者。准备好配置后，单击“开始导出” 。

使用 SPanel 创建数据库备份

SPanel 力求涵盖现代网站建设和开发的各个方面。这包括数据库管理，这就是每个SPanel 服务器都预装了 phpMyAdmin的原因。但是，我们希望确保您对站点数据有更多的控制权，这就是为什么您在SPanel 的用户界面内的MySQL 数据库部分拥有相当多的工具。

要访问用户界面，您可以在https://[帐户的域名]/spanel/使用帐户的登录凭据，或者登录到服务器的管理区域，然后从帐户旁边的操作下拉菜单中选择管理。访问MySQL 数据库部分后，您将看到您帐户中所有数据库的列表。找到您要备份的数据库，打开操作下拉菜单，然后选择导出和下载数据库。

SPanel 会将数据放入 SQL 文件并将其保存在帐户的主目录中。然后，弹出窗口让您下载本地存储的文件并将其从服务器中删除。

如您所见，在对站点进行任何更改之前备份数据库只需单击几下。然而，即使您忘记这样做，SPanel 仍然可以满足您的要求。ScalaHosting 的每个 SPanel 客户每天都会获得服务器上所有文件和数据库的备份 。备份存储在远程位置。它们与您的生产站点不在同一个数据中心，因此即使发生事故，您也有更好的机会恢复数据。说到恢复数据，让我们看看从备份重建损坏的数据库需要做什么。

恢复 MySQL 数据库

同样，您有多种方法可供选择，选择哪一种取决于很多因素，包括您的托管平台、您的技术技能和您的需求。

通过命令行恢复数据库

要从命令行恢复数据库，您首先需要将备份文件上传到您的主机账户。最简单的方法是通过 FTP 客户端或从控制面板的文件管理器。您可以将 SQL 文件放在您想要的任何文件夹中，但为了使命令简单，最好将其保存在主目录中。还原数据库后，您可以删除备份文件以释放空间。接下来，创建一个新的空数据库和一个可以访问它的 MySQL 用户帐户。您还可以使用具有 SYSTEM_USER 权限的帐户。

通过 SSH 连接到您的主机帐户并输入以下命令：

$ mysql -u [你的用户名] -p [数据库名] < [备份文件名]

在上面的示例中，用户帐户test_user正在将数据从文件backup.sql恢复到数据库test_database2中。

使用 phpMyAdmin 恢复数据库

您可以使用phpMyAdmin 的导入功能从 SQL 文件恢复数据库。您需要记住，SQL 文件的大小通常是有限制的，因此如果您有一个更大的数据库，您可能需要使用其他方法之一。

要将数据导入现有数据库，请打开 phpMyAdmin 并从左侧菜单中选择要恢复的数据库。如果数据库不存在，您需要先创建它。转到“导入”选项卡，单击“选择文件”，然后从您的计算机中选择 SQL 备份。

此屏幕上最重要的选项是“格式”下拉菜单。您必须确保将其设置为SQL。默认配置可能适用于大多数网站所有者。单击Go将数据导入数据库。

使用 MySQL Workbench 恢复数据库

与我们今天讨论的其他实用程序一样，MySQL Workbench具有导出和导入功能。要恢复数据库，请启动 MySQL Workbench 并打开与服务器的连接。在Navigator部分，选择Administration选项卡并单击Data Import/Restore。

单击从独立文件导入单选按钮，然后从您的计算机中选择 SQL 备份文件。

最后，单击“开始导入”以恢复数据库。

使用 SPanel 恢复数据库

如果您有一个 SPanel 服务器，您可以使用我们目前为止描述的任何方法从 SQL 备份文件恢复您的数据库。但是，您也可以从SPanel 生成的每日自动备份之一中检索数据。

在ScalaHosting 设置新的 SPanel 服务器时，您可以选择在任何给定时间要存储的备份数量。默认情况下，您有一个保留 24 小时的每日备份。但是，我们也有三天和七天的选择。

可以通过SPanel 用户界面主页上的恢复备份部分访问备份。

首先，选择要还原的备份日期，然后单击浏览数据库以查看在选定日期备份的数据库。

找到您需要的数据库并打开还原下拉菜单。

您有以下三种选择：

• 下载带有数据库备份的 SQL 文件。
• 恢复原始数据库。
• 在另一个数据库中恢复数据。SPanel 将打开一个对话框，询问您要使用的数据库的名称。您可以恢复现有数据库中的数据或输入新数据库的名称。SPanel 会自动为您设置。

尝试备份 MySQL 数据库时的潜在问题

由于几个不同的原因，导出过程可能会失败并导致错误。幸运的是，解决问题通常非常简单。让我们来看看其中的一些。

 MySQL 服务不工作

首先，您需要确保MySQL 服务器正在您的主机账户上运行。您可以使用以下命令通过 SSH 执行此操作：

$ 服务 mysql 状态

如果您使用 SPanel，您还可以检查 MySQL 是否正在从管理界面运行。基本服务列表在“服务器状态”菜单中可用，如果MariaDB 数据库服务器由于某种原因关闭，您可以从“重启服务”部分将其恢复在线。

您的数据库中有损坏的表

正如我们已经提到的，您可以使用mysqlcheck 命令行实用程序来查看数据库中是否有损坏的表。如果由于行或表损坏而失败，它还可以修复错误。

您只需要-r选项：

$ mysqlcheck [数据库名称] -u [用户帐户的用户名] -p -r

也可以通过 phpMyAdmin 和 SPanel 解决错误。

在phpMyAdmin中，从左侧菜单中选择数据库，您将被直接重定向到Structure选项卡，您将在其中看到所有数据库表的列表。使用要修复的表旁边的复选框（如果您不确定错误在哪里，可以使用底部的全部选中选项）并从选择的菜单中选择修复表。

有了 SPanel，这个过程就更简单了。登录帐户的用户界面并转到MySQL 数据库。向下滚动到数据库列表，找到您想要的数据库，然后打开旁边的操作下拉菜单。选择Repair database后，SPanel 将查找并修复数据库表中可能存在的任何错误。

没有足够的磁盘空间

备份是在服务器上生成的，必然会占用一些存储空间。如果在备份数据库时超出了帐户的容量，则该过程将失败。您的第一个选择是通过 gzip 传输备份。这将减小其大小并且可能足以让您在存档中检索 SQL 文件。但是，您还应该考虑从您的帐户中删除任何不必要的信息，优化您的数据库，并可能升级您的帐户，这样您就可以拥有更多的存储空间。