本节总结了一些制作备份的常用方法。This section summarizes some general methods for making backups.

使用 MySQL 企业备份进行热备份Making a Hot Backup with MySQL Enterprise Backup

MySQL 企业版的客户可以使用 MySQL 企业备份 产品对整个实例或选定的数据库、表或两者进行 物理 备份。该产品包括 增量 和 压缩 备份功能。备份物理数据库文件比使用 mysqldump 命令等逻辑技术恢复更快。InnoDB 表是使用 热备份 机制复制的。（理想情况下，InnoDB 表应占据数据的大多数。）其他存储引擎的表是使用 温备份 机制复制的。有关 MySQL 企业备份产品的概述，请参见 第 32.1 节，“MySQL 企业备份概述”。Customers of MySQL Enterprise Edition can use the MySQL Enterprise Backup product to do physical backups of entire instances or selected databases, tables, or both. This product includes features for incremental and compressed backups. Backing up the physical database files makes restore much faster than logical techniques such as the mysqldump command. InnoDB tables are copied using a hot backup mechanism. (Ideally, the InnoDB tables should represent a substantial majority of the data.) Tables from other storage engines are copied using a warm backup mechanism. For an overview of the MySQL Enterprise Backup product, see Section 32.1, “MySQL Enterprise Backup Overview”.

使用 mysqldump 进行备份Making Backups with mysqldump

mysqldump 程序可以进行备份。它可以备份各种类型的表。（参见 第 9.4 节，“使用 mysqldump 进行备份”。）The mysqldump program can make backups. It can back up all kinds of tables. (See Section 9.4, “Using mysqldump for Backups”.)

对于 InnoDB 表，可以使用 --single-transaction 选项对 mysqldump 进行在线备份，而不对表加锁。参见 第 9.3.1 节，“建立备份策略”。For InnoDB tables, it is possible to perform an online backup that takes no locks on tables using the --single-transaction option to mysqldump. See Section 9.3.1, “Establishing a Backup Policy”.

通过复制表文件进行备份Making Backups by Copying Table Files

MyISAM 表可以通过复制表文件（*.MYD、*.MYI 文件，以及相关的 *.sdi 文件）来进行备份。为了获得一致的备份，需要停止服务器或锁定并刷新相关表：MyISAM tables can be backed up by copying table files (*.MYD, *.MYI files, and associated *.sdi files). To get a consistent backup, stop the server or lock and flush the relevant tables:

FLUSH TABLES tbl_list WITH READ LOCK;

你只需要一个读取锁；这可以让其他客户端在你复制数据库目录中文件的同时继续查询表。需要执行刷新操作以确保在开始备份之前，所有活动的索引页都已写入磁盘。请参见第 15.3.6 节，“LOCK TABLES 和 UNLOCK TABLES 语句”，以及第 15.7.8.3 节，“FLUSH 语句”。You need only a read lock; this enables other clients to continue to query the tables while you are making a copy of the files in the database directory. The flush is needed to ensure that the all active index pages are written to disk before you start the backup. See Section 15.3.6, “LOCK TABLES and UNLOCK TABLES Statements”, and Section 15.7.8.3, “FLUSH Statement”.

你也可以通过简单地复制表文件来创建二进制备份，只要服务器没有进行任何更新。（但请注意，如果你的数据库包含InnoDB表，表文件复制方法将不起作用。另外，即使服务器没有积极更新数据，InnoDB仍可能有修改的数据缓存于内存中而未刷新到磁盘。）You can also create a binary backup simply by copying the table files, as long as the server is not updating anything. (But note that table file copying methods do not work if your database contains InnoDB tables. Also, even if the server is not actively updating data, InnoDB may still have modified data cached in memory and not flushed to disk.)

有关此备份方法的示例，请参阅第 15.2.6 节，“IMPORT TABLE 语句”中的导出和导入示例。For an example of this backup method, refer to the export and import example in Section 15.2.6, “IMPORT TABLE Statement”.

制作分隔文本文件备份Making Delimited-Text File Backups

要创建包含表数据的文本文件，可以使用 SELECT * INTO OUTFILE 'file_name' FROM tbl_name。该文件是在 MySQL 服务器主机上创建的，而不是客户端主机。对于此语句，输出文件不能已经存在，因为允许覆盖文件会构成安全风险。参见 第 15.2.13 节，“SELECT 语句”。此方法适用于任何类型的数据文件，但仅保存表数据，而不保存表结构。To create a text file containing a table's data, you can use SELECT * INTO OUTFILE 'file_name' FROM tbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because permitting files to be overwritten constitutes a security risk. See Section 15.2.13, “SELECT Statement”. This method works for any kind of data file, but saves only table data, not the table structure.

创建文本数据文件的另一种方法（以及包含备份表的 CREATE TABLE 语句的文件）是使用 mysqldump 并带上 --tab 选项。请参阅 第 9.4.3 节，“使用 mysqldump 以分隔文本格式导出数据”。Another way to create text data files (along with files containing CREATE TABLE statements for the backed up tables) is to use mysqldump with the --tab option. See Section 9.4.3, “Dumping Data in Delimited-Text Format with mysqldump”.

要重新加载定界文本数据文件，请使用 LOAD DATA 或 mysqlimport。To reload a delimited-text data file, use LOAD DATA or mysqlimport.

通过启用二进制日志进行增量备份Making Incremental Backups by Enabling the Binary Log

MySQL 支持使用二进制日志进行增量备份。二进制日志文件为您提供了在执行备份之后对数据库所做更改的相关信息。因此，为了允许服务器恢复到某一时间点，必须在服务器上启用二进制日志记录，这是 MySQL 8.4 的默认设置；参见 第 7.4.4 节，“二进制日志”。MySQL supports incremental backups using the binary log. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. Therefore, to allow a server to be restored to a point-in-time, binary logging must be enabled on it, which is the default setting for MySQL 8.4 ; see Section 7.4.4, “The Binary Log”.

在你想要进行增量备份（包含自上次完整备份或增量备份以来发生的所有更改）的时候，你应该使用 FLUSH LOGS 来轮换二进制日志。完成此操作后，你需要将从上一次完整备份或增量备份时刻到倒数第二个二进制日志的所有二进制日志复制到备份位置。这些二进制日志就是增量备份；在恢复时，你按照 第 9.5 节，“时间点（增量）恢复” 中所述的方法应用它们。下次进行完整备份时，你也应该使用 FLUSH LOGS 或 mysqldump --flush-logs 来轮换二进制日志。参见 第 6.5.4 节，“mysqldump — 数据库备份程序”。At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 9.5, “Point-in-Time (Incremental) Recovery”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS or mysqldump --flush-logs. See Section 6.5.4, “mysqldump — A Database Backup Program”.

使用副本进行备份Making Backups Using Replicas

如果在进行备份时服务器出现性能问题，一种可行的策略是设置复制，并在副本上执行备份，而不是在源服务器上备份。请参见 第 19.4.1 节，“使用复制进行备份”。If you have performance problems with a server while making backups, one strategy that can help is to set up replication and perform backups on the replica rather than on the source. See Section 19.4.1, “Using Replication for Backups”.

如果您正在备份一个副本，无论选择哪种备份方法，在备份副本的数据库时都应备份其连接元数据存储库和应用器元数据存储库（参见 第 19.2.4 节，“中继日志和复制元数据存储库”）。在恢复副本的数据后，这些信息始终是恢复复制所必需的。如果您的副本正在复制 LOAD DATA 语句，还应备份副本用于此目的的目录中存在的任何 SQL_LOAD-* 文件。副本需要这些文件以恢复任何被中断的 LOAD DATA 操作的复制。该目录的位置是系统变量 replica_load_tmpdir 的值。如果服务器未使用该变量启动，则目录位置为系统变量 tmpdir 的值。If you are backing up a replica, you should back up its connection metadata repository and applier metadata repository (see Section 19.2.4, “Relay Log and Replication Metadata Repositories”) when you back up the replica's databases, regardless of the backup method you choose. This information is always needed to resume replication after you restore the replica's data. If your replica is replicating LOAD DATA statements, you should also back up any SQL_LOAD-* files that exist in the directory that the replica uses for this purpose. The replica needs these files to resume replication of any interrupted LOAD DATA operations. The location of this directory is the value of the system variable replica_load_tmpdir. If the server was not started with that variable set, the directory location is the value of the tmpdir system variable.

恢复损坏的表Recovering Corrupt Tables

如果必须恢复已损坏的MyISAM表，请尝试先使用REPAIR TABLE或myisamchk -r来恢复。这在 99.9% 的情况下都能奏效。如果myisamchk失败，请参阅第 9.6 节，“MyISAM 表维护和崩溃恢复”。If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, see Section 9.6, “MyISAM Table Maintenance and Crash Recovery”.

使用文件系统快照进行备份Making Backups Using a File System Snapshot

如果您正在使用 Veritas 文件系统，您可以这样进行备份：If you are using a Veritas file system, you can make a backup like this:

其他文件系统中可能也有类似的快照功能，例如 LVM 或 ZFS。Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.