SqlBak Blog

如何在Windows上备份MySQL数据库

因为有几种方法可以在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