MySQL 8.0 参考手册  / 第 7 章备份与恢复  /  7.1 备份和恢复类型

7.1 备份和恢复类型

本节介绍不同类型备份的特征。

物理（原始）与逻辑备份

物理备份包括存储数据库内容的目录和文件的原始副本。这种备份适用于发生问题时需要快速恢复的大型、重要的数据库。

逻辑备份保存表示为逻辑数据库结构（CREATE DATABASE、 CREATE TABLE语句）和内容（INSERT语句或分隔文本文件）的信息。这种类型的备份适用于您可以编辑数据值或表结构，或在不同机器架构上重新创建数据的少量数据。

物理备份方法具有以下特点：

  • 备份包括数据库目录和文件的精确副本。通常，这是全部或部分 MySQL 数据目录的副本。

  • 物理备份方法比逻辑备份方法更快，因为它们只涉及文件复制而不进行转换。

  • 输出比逻辑备份更紧凑。

  • 由于备份速度和紧凑性对于繁忙的重要数据库很重要，因此 MySQL Enterprise Backup 产品执行物理备份。有关 MySQL Enterprise Backup 产品的概述，请参阅 第 28.2 节，“MySQL Enterprise Backup 概述”。

  • 备份和恢复粒度范围从整个数据目录级别到单个文件级别。这可能会或可能不会提供表级粒度，具体取决于存储引擎。例如， InnoDB每个表可以在一个单独的文件中，或者与其他 InnoDB表共享文件存储；每个 MyISAM表唯一对应一组文件。

  • 除了数据库之外，备份还可以包括任何相关文件，例如日志或配置文件。

  • 表中的数据MEMORY很难以这种方式备份，因为它们的内容没有存储在磁盘上。（MySQL Enterprise Backup 产品具有一项功能，您可以MEMORY在备份期间从表中检索数据。）

  • 备份只能移植到具有相同或相似硬件特征的其他机器。

  • 可以在 MySQL 服务器未运行时执行备份。如果服务器正在运行，则需要执行适当的锁定，以便服务器在备份期间不会更改数据库内容。MySQL Enterprise Backup 会自动为需要它的表进行这种锁定。

  • 物理备份工具包括 MySQL Enterprise Backup for or any other tables的mysqlbackupInnoDB ，或者表的文件系统级命令（如cp、 scp、tar、 rsync）MyISAM 。

  • 对于恢复：

    • MySQL Enterprise Backup 还原InnoDB 它备份的其他表。

    • ndb_restore恢复 NDB表。

    • 可以使用文件系统命令将在文件系统级别复制的文件复制回其原始位置。

逻辑备份方法具有以下特点：

  • 备份是通过查询MySQL服务器获取数据库结构和内容信息来完成的。

  • 备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出是在客户端写的，服务器端也必须将它发送给备份程序。

  • 输出大于物理备份，尤其是在以文本格式保存时。

  • 备份和还原粒度在服务器级别（所有数据库）、数据库级别（特定数据库中的所有表）或表级别可用。无论存储引擎如何，都是如此。

  • 备份不包括日志或配置文件，或不属于数据库的其他与数据库相关的文件。

  • 以逻辑格式存储的备份与机器无关且具有高度可移植性。

  • 逻辑备份是在 MySQL 服务器运行的情况下执行的。服务器未脱机。

  • 逻辑备份工具包括mysqldump 程序和SELECT ... INTO OUTFILE语句。这些适用于任何存储引擎，甚至MEMORY.

  • 要恢复逻辑备份，可以使用mysql客户端处理 SQL 格式的转储文件。要加载分隔文本文件，请使用LOAD DATA语句或mysqlimport 客户端。

Online Versus Offline Backups

Online backups take place while the MySQL server is running so that the database information can be obtained from the server. Offline backups take place while the server is stopped. This distinction can also be described as “hot” versus “cold” backups; a “warm” backup is one where the server remains running but locked against modifying data while you access database files externally.

Online backup methods have these characteristics:

  • The backup is less intrusive to other clients, which can connect to the MySQL server during the backup and may be able to access data depending on what operations they need to perform.

  • Care must be taken to impose appropriate locking so that data modifications do not take place that would compromise backup integrity. The MySQL Enterprise Backup product does such locking automatically.

Offline backup methods have these characteristics:

  • Clients can be affected adversely because the server is unavailable during backup. For that reason, such backups are often taken from a replica server that can be taken offline without harming availability.

  • The backup procedure is simpler because there is no possibility of interference from client activity.

A similar distinction between online and offline applies for recovery operations, and similar characteristics apply. However, it is more likely for clients to be affected for online recovery than for online backup because recovery requires stronger locking. During backup, clients might be able to read data while it is being backed up. Recovery modifies data and does not just read it, so clients must be prevented from accessing data while it is being restored.

Local Versus Remote Backups

A local backup is performed on the same host where the MySQL server runs, whereas a remote backup is done from a different host. For some types of backups, the backup can be initiated from a remote host even if the output is written locally on the server. host.

  • mysqldump can connect to local or remote servers. For SQL output (CREATE and INSERT statements), local or remote dumps can be done and generate output on the client. For delimited-text output (with the --tab option), data files are created on the server host.

  • SELECT ... INTO OUTFILE can be initiated from a local or remote client host, but the output file is created on the server host.

  • Physical backup methods typically are initiated locally on the MySQL server host so that the server can be taken offline, although the destination for copied files might be remote.

Snapshot Backups

Some file system implementations enable “snapshots” to be taken. These provide logical copies of the file system at a given point in time, without requiring a physical copy of the entire file system. (For example, the implementation may use copy-on-write techniques so that only parts of the file system modified after the snapshot time need be copied.) MySQL itself does not provide the capability for taking file system snapshots. It is available through third-party solutions such as Veritas, LVM, or ZFS.

Full Versus Incremental Backups

A full backup includes all data managed by a MySQL server at a given point in time. An incremental backup consists of the changes made to the data during a given time span (from one point in time to another). MySQL has different ways to perform full backups, such as those described earlier in this section. Incremental backups are made possible by enabling the server's binary log, which the server uses to record data changes.

Full Versus Point-in-Time (Incremental) Recovery

A full recovery restores all data from a full backup. This restores the server instance to the state that it had when the backup was made. If that state is not sufficiently current, a full recovery can be followed by recovery of incremental backups made since the full backup, to bring the server to a more up-to-date state.

Incremental recovery is recovery of changes made during a given time span. This is also called point-in-time recovery because it makes a server's state current up to a given time. Point-in-time recovery is based on the binary log and typically follows a full recovery from the backup files that restores the server to its state when the backup was made. Then the data changes written in the binary log files are applied as incremental recovery to redo data modifications and bring the server up to the desired point in time.

Table Maintenance

Data integrity can be compromised if tables become corrupt. For InnoDB tables, this is not a typical issue. For programs to check MyISAM tables and repair them if problems are found, see Section 7.6, “MyISAM Table Maintenance and Crash Recovery”.

Backup Scheduling, Compression, and Encryption

备份计划对于自动化备份过程很有价值。备份输出的压缩减少了空间需求，输出的加密提供了更好的安全性，以防止未经授权访问备份数据。MySQL 本身不提供这些功能。MySQL Enterprise Backup 产品可以压缩InnoDB备份，并且可以使用文件系统实用程序实现备份输出的压缩或加密。其他第三方解决方案可能可用。