Sign Up

SqlBak Blog

如何在Windows上备份MySQL数据库

by
Last updated on July 16, 2024

因为有几种方法可以在Windows上备份MySQL数据库，本文将回顾所有不同的选项，以便您选择适合您的方法。本文仅限于Microsoft Windows服务器上的MySQL数据库备份。如果您使用的是基于Linux的操作系统，请参阅如何在Linux中自动化MySQL数据库备份。


请注意，本文中描述的大多数方法都与MariaDB完全兼容，热物理备份除外。

MySQL备份类型

MySQL数据库支持以下类型的备份：

  • 逻辑备份 – 逻辑备份的结果是一个用于数据库重建的.sql脚本。此脚本是使用mysqldump实用程序创建的。这种备份的主要优点是它没有第三方依赖性，并且可以在任何MySQL服务器上恢复。
  • 物理备份 – 通过复制数据库文件创建此备份。创建和恢复此类备份比逻辑备份更快。我们建议用于大型数据库。
  • 热备份 – 如果MySQL服务器在InnoDB子系统上运行，那么您可以在不停止写入MySQL服务器的情况下创建事务一致的备份
  • 部分备份 – 备份不是为整个数据库管理系统创建的，而是为特定的数据库或表创建的。当对不同的数据应用不同的备份策略时，这是有用的。

如何创建逻辑MySQL备份

使用mysqldump简单备份MySQL数据库

安装MySQL服务器时，主要的备份工具会自动安装 – mysqldump。这个命令行工具可以创建一个备份文件，该文件包含一组可以用于重建数据库的命令。
从mysqldump创建的文件恢复MySQL数据库很容易。小型数据库可以通过将备份文件的内容复制到图形IDE的SQL编辑器（例如，MySQL Workbench）并在那里运行来恢复。然而，对于大型数据库，最好使用mysql命令行工具进行恢复。
通常，mysqldump位于MySQL服务器安装目录中。例如，对于MySQL 8.0，目录的路径是C:\Program Files\MySQL\MySQL Server 8.0\bin\。建议将此目录添加到全局PATH变量中。

要备份MySQL服务器上的所有数据库，请运行以下命令：

mysqldump --user root --password  --all-databases > all-databases.sql

要恢复数据，请使用以下命令：

mysql --user root --password mysql < all-databases.sql

通常，您可能需要备份的不是整个服务器上的所有数据库，而是特定的数据库。要转储特定的数据库，请使用数据库的名称代替–all-database参数。

mysql --user root --password [db_name] < [db_name].sql

以开放格式创建备份有两个重要的优点：

  1. 您可以在不同版本的服务器上恢复转储。因此，切换到另一个版本的MySQL可以在不担心备份的情况下进行。
  2. 如果您需要在恢复之前更改某些内容，可以手动编辑.sql文件。

有关更多信息，请参阅mysqldump文档。

使用MySQL Workbench备份

MySQL Workbench是一个用于视觉设计并与MySQL数据库一起工作的工具。此应用程序还提供了创建MySQL数据库逻辑备份的功能。
要使用MySQL Workbench创建备份，请按照以下步骤操作：

  1. 在导航面板（默认位于左侧）中，转到管理选项卡
  2. 选择数据导出
  3. 在数据导出选项卡的”导出表格”部分，选择您希望添加到备份文件的数据库和表格
  4. 在”导出选项”部分，选择您希望导出数据的格式。每个表格将被导出到一个单独的.sql文件，或者将创建一个公共的.sql文件。

如果您只需要恢复一些特定的表格，而不是整个数据库，那么将每个表格导出到单独的文件可能会有用。但是，通常情况下，这是不必要的，而且使用一个备份文件更容易。

  1. 按导出按钮创建备份文件。

导出部分实际上是mysqldump实用程序的图形用户界面。虽然您不能使用MySQL Workbench自动化创建过程，但这个工具对于手动创建备份和迁移数据很方便。
此外，您可以使用MySQL Workbench作为mysqldump的参数构造器。当您点击导出按钮时，将显示导出执行的日志，在其中将有一个带有在界面中指定的参数的mysqldump命令。

使用MySQL Workbench恢复转储

要恢复创建的备份，请按照以下步骤操作：

  1. 在导航面板（默认位于左侧）中，转到管理选项卡
  2. 选择数据导入\恢复
  3. 选择要恢复的源转储项目文件夹或自包含文件，这取决于您在备份阶段选择的内容
  4. 如果您使用的是转储项目文件夹，那么您可以选择需要恢复的数据库和表格。
  5. 如果您使用的是自包含文件（.sql），那么在恢复之前，您必须选择要恢复转储的模式。如果您将转储恢复到不存在所需模式的服务器，您可以通过点击”新建”按钮来创建。
  6. 按开始导入按钮。

使用SQLBackupAndFTP备份

SQLBackupAndFTP 是备份和恢复MySQL，SQL Server和PostgreSQL的流行实用程序。这个专门的实用程序设计用于自动创建备份并将它们发送到存储。
这个实用程序不仅简化了备份，而且还允许您执行一键恢复，以及自动化恢复，这对于同步数据或创建测试服务器非常有用。
使用SQLBackupAndFTP创建MySQL数据库备份，请按照以下步骤操作：

  1. 连接到您的MySQL服务器，选择连接类型为MySQL服务器（TCP/IP）或MySQL服务器（phpMyAdmin）
  2. 接下来，在”选择数据库”部分，选择您要备份的数据库
  3. 下一步是选择备份存储的位置。它可以是本地或网络文件夹，FTP，或者流行的云存储服务，如Dropbox，Google Drive，Amazon S3，Azure等。请注意，您可以指定将发送备份的几个地方。
  4. 在”计划备份”部分，创建备份计划
  5. 要接收关于备份完成的电子邮件通知，请设置”发送确认”选项

这些是基本设置，但您还可以调整备份的压缩级别，为它们设置密码（使用加密选项），指定一个临时文件夹进行工作，设置要运行的脚本等等。
恢复由SQLBackupAndFTP创建的MySQL数据库，只需按照以下步骤操作：

  1. 在应用程序的”历史和恢复”部分（位于右侧）找到您要恢复的备份
  2. 点击三个点图标并选择”从备份恢复…”
  3. 确认您选择了正确的备份，然后点击”恢复”按钮
  4. 指定您要恢复备份的数据库的名称和密码（如果适用），然后点击”恢复”按钮并确认启动

SQLBackupAndFTP的主要特点是，这个实用程序不仅提供了创建备份文件的良好界面，而且还允许您配置所有附带的步骤：压缩，加密，云存储和失败通知。

使用SqlBak进行备份

通常，数据库管理员需要维护多个数据库。对于在多个服务器上创建和维护备份，SqlBak是一个好的解决方案。
这是一个基于代理的应用程序，用于创建备份，压缩它们，然后将它们发送到存储。所有必要的设置都在浏览器中完成。
要使用SqlBak进行备份，请按照以下说明操作：

  1. 运行SqlBak应用程序并使用服务器类型MySQL服务器（TCP/IP）或MySQL服务器（phpMyAdmin）连接到您的MySQL服务器
  2. 转到仪表板页面，点击添加新任务按钮
  3. 在打开的窗口中，选择您需要的服务器，在任务类型字段中设置备份任务，然后点击创建任务
  4. 在选择DBMS连接部分，选择连接到您的MySQL服务器。如果只有一个连接在您的服务器上，系统将自动选择它。
  5. 在选择数据库部分，标记所有您想要备份的数据库
  6. 接下来，您需要设置备份存储的位置。这可以是本地或网络文件夹，FTP，或者如OneDrive，Amazon S3，S3 Compatible（如Google Cloud，Wasabi）等云存储服务。
  7. 创建一个开始任务的计划
  8. 指定您的电子邮件地址以接收任务完成或失败的通知
  9. 点击保存并退出按钮保存设置并开始任务

这些是基本设置，但您还可以配置备份的加密，压缩，运行脚本等等。
要使用SqlBak从备份恢复数据库，您需要执行以下操作：

  1. 转到仪表板页面，点击最后运行列中的恢复图标以恢复最后的备份。或者，转到备份任务设置页面，在备份历史部分找到您需要的备份，然后点击操作列中的恢复图标
  2. 选择您需要的备份，然后点击恢复按钮
  3. 检查设置并开始恢复

利用 PhpMyAdmin 进行备份

PhpMyAdmin 是一个能通过浏览器管理 MySQL 数据库的开源工具。若你已经在使用此工具，那么你只需简单地点击五次就能创建一个备份，而无需为了创建备份而专门安装 PhpMyAdmin。

  1. 在左侧面板中选择数据库
  2. 切换到导出（Export）选项卡
  3. 如果需要备份整个服务器，请选择快速（Quick）。如果你只想备份特定的数据库，那么必须选择自定义（Custom）来查看更多设置。
  4. 选择 SQL 格式，这是备份的首选格式。
  5. 点击开始（Go）。

很多时候，托管提供商不提供直接访问 MySQL 数据库的权限，而是通过 PhpMyAdmin 提供访问。然而，与 MySQL Workbench 相同，PhpMyAdmin 是一个数据库操作工具，并不支持创建常规备份的内置机制。在这种情况下，SqlBak 或者 SQLBackupAndFTP 可以通过 PhpMyAdmin 连接到 MySQL 数据库以提供帮助。

如何创建一个 物理 MySQL 数据库备份

最后，所有数据库最终都是以一种或一组文件的形式存储。将这些文件备份到另一个位置，就能在未来的某个时候用它们恢复数据。
与逻辑备份相比，物理备份的创建速度更快，因为它只涉及文件的复制。同样的，由于这个原因，恢复过程也更快。
但是，物理备份有两个主要缺点：

  1. 备份只能在具有相同或相似硬件特性的机器上进行迁移。
  2. 为小型企业创建实时备份的解决方案可能会过于昂贵，因为唯一能在不停止服务器的情况下创建物理备份的 Windows 工具是 MySQL Enterprise Backup。

通过复制数据文件手动创建 MySQL 服务器备份

创建备份的最简单方法就是简单地复制 MySQL 数据目录的内容。按照以下步骤手动创建基于文件的 MySQL 服务器备份：

  1. 找到 MySQL 服务器的数据目录。可以运行以下命令：
select @@datadir;

你可以通过 MySQL Workbench 来执行这个操作。

  1. 找到并停止 MySQL 服务器服务：
    • 按 Win+R
    • 输入 services.msc
    • 在服务列表中找到 MySQL 服务器服务
    • 右键单击它并选择停止
  1. 将 MySQL 服务器数据目录的内容复制或打包到另一个位置
  2. 通过在服务的右键菜单中点击“开始”，启动 MySQL 服务器。

使用批处理脚本通过复制数据文件创建 MySQL 服务器备份

在前一部分描述的所有操作都可以通过一个批处理脚本来执行。
首先，设定变量值 mysql_data_dir,backup_dir, mysql_service_name，然后运行脚本。它将停止服务，将数据文件夹的内容复制到一个新的子目录，然后启动 MySQL 服务。

set mysql_data_dir=C:\ProgramData\MySQL\MySQL Server 8.0\Data
set backup_dir=D:\Temp
set mysql_service_name=MySQL80

NET STOP %mysql_service_name%
set mysql_backup_folder=%backup_dir%\mysql-backup-%DATE:~-4%-%DATE:~4,2%-%DATE:~7,2%-%time::=.% 
mkdir %mysql_backup_folder%
xcopy /e /k /h /i "%mysql_data_dir%" "%mysql_backup_folder%"
NET START %mysql_service_name%
从文件恢复数据库

按照以下步骤恢复数据：

  1. 停止 MySQL 服务
  2. 清空 MySQL 服务器数据目录
  3. 将保存的数据复制到数据目录
  4. 启动服务

使用 mysqlbackup 工具

前一种方法的主要缺点是需要停止 MySQL 服务器。虽然复制本身应该不会花费太多时间，但即使是短暂的服务器停机也可能是无法接受的。
对于 Windows 服务器，Oracle 公司开发了一个 MySQL 企业备份产品，可以创建在线物理备份。
此方案包括一个控制台工具 – mysqlbackup，它可以创建物理备份。使用起来和 mysqldump一样简单。

以下命令在 D:\Temp\EnterpriseBackup 目录中创建一个备份文件。

mysqlbackup --user=root --password --backup-image=backup.mbi --backup-dir=D:\Temp\EnterpriseBackup backup-to-image

–backup-image – 备份文件名

–backup-dir – 创建备份的目录

要从备份中恢复数据，你需要进行准备步骤，即停止 MySQL 服务并清空 MySQL 服务器数据目录。然后，你需要使用相同的工具来恢复数据，但参数不同。

mysqlbackup --datadir=C:\ProgramData\MySQL\MySQL Server 8.0\Data\ --backup-image=backup.mbi --backup-dir=D:\Temp\EnterpriseBackup copy-back-and-apply-log

–datadir – MySQL 服务器数据目录

–backup-image – 备份文件名

–backup-dir – 包含备份文件的目录。

mysqlbackup 工具支持即时压缩，部分备份，增量备份和许多其他功能。详情可点击此处查看。

此方案的主要缺点是需要购买 MySQL 企业版，对于小型和中型企业来说，这可能会过于昂贵。

总结

请记住，保护你的数据不仅仅是创建一个备份文件。你还需要确保备份文件被转移到安全的存储，并定期删除旧的备份，以防止存储空间溢出。
无论你是创建一个使用 mysqldump / mysqlbackup 的自动化 MySQL 服务器备份批处理脚本，还是使用 SqlBak 或者 SQLBackupAndFTP，最重要的是不要忘记，你创建备份的目的是为了以后可以恢复。因此，时不时地测试你的恢复脚本是非常重要的。
希望这些指导能帮助你找到最适合你需求的备份方案。

Leave a Comment