innodb_redo_log_capacity 系统变量控制重做日志文件占用的磁盘空间量。您可以在启动时在选项文件中设置此变量，或在运行时使用 SET GLOBAL 语句设置；例如，以下语句将重做日志容量设置为 8GB：The innodb_redo_log_capacity system variable controls the amount of disk space occupied by redo log files. You can set this variable in an option file at startup or at runtime using a SET GLOBAL statement; for example, the following statement sets the redo log capacity to 8GB:

SET GLOBAL innodb_redo_log_capacity = 8589934592;

在运行时设置时，配置更改会立即生效，但新限制完全实施可能需要一些时间。如果重做日志文件占用的空间小于指定值，脏页从缓冲池刷写到表空间数据文件的速度将减慢，最终会增加重做日志文件占用的磁盘空间。如果重做日志文件占用的空间大于指定值，脏页刷写会更积极，最终会减少重做日志文件占用的磁盘空间。When set at runtime, the configuration change occurs immediately but it may take some time for the new limit to be fully implemented. If the redo log files occupy less space than the specified value, dirty pages are flushed from the buffer pool to tablespace data files less aggressively, eventually increasing the disk space occupied by the redo log files. If the redo log files occupy more space than the specified value, dirty pages are flushed more aggressively, eventually decreasing the disk space occupied by redo log files.

如果没有定义innodb_redo_log_capacity，并且innodb_log_file_size或innodb_log_files_in_group也未定义，则使用默认的innodb_redo_log_capacity值。If innodb_redo_log_capacity is not defined, and if neither innodb_log_file_size or innodb_log_files_in_group are defined, then the default innodb_redo_log_capacity value is used.

如果未定义 innodb_redo_log_capacity，并且定义了 innodb_log_file_size 和/或 innodb_log_files_in_group，则 InnoDB 重做日志容量将计算为 (innodb_log_files_in_group * innodb_log_file_size)。此计算不会修改未使用的 innodb_redo_log_capacity 设置的值。If innodb_redo_log_capacity is not defined, and if innodb_log_file_size and/or innodb_log_files_in_group is defined, then the InnoDB redo log capacity is calculated as (innodb_log_files_in_group * innodb_log_file_size). This calculation does not modify the unused innodb_redo_log_capacity setting's value.

Innodb_redo_log_capacity_resized 服务器状态变量表示所有重做日志文件的总重做日志容量。The Innodb_redo_log_capacity_resized server status variable indicates the total redo log capacity for all redo log files.

重做日志文件位于数据目录中的 #innodb_redo 目录，除非通过 innodb_log_group_home_dir 变量指定了不同的目录。如果定义了 innodb_log_group_home_dir，重做日志文件将位于该目录下的 #innodb_redo 目录中。重做日志文件有两种类型：普通和备用。普通重做日志文件是正在使用的文件，备用重做日志文件是等待使用的文件。InnoDB 尽量保持总共 32 个重做日志文件，每个文件的大小等于 1/32 * innodb_redo_log_capacity；然而，在修改 innodb_redo_log_capacity 设置后，文件大小在一段时间内可能会有所不同。Redo log files reside in the #innodb_redo directory in the data directory unless a different directory was specified by the innodb_log_group_home_dir variable. If innodb_log_group_home_dir was defined, the redo log files reside in the #innodb_redo directory in that directory. There are two types of redo log files, ordinary and spare. Ordinary redo log files are those being used. Spare redo log files are those waiting to be used. InnoDB tries to maintain 32 redo log files in total, with each file equal in size to 1/32 * innodb_redo_log_capacity; however, file sizes may differ for a time after modifying the innodb_redo_log_capacity setting.

重做日志文件使用#ib_redoN命名规则，其中N是重做日志文件的编号。备用重做日志文件以_tmp后缀表示。下面的示例显示了#innodb_redo目录中的重做日志文件，其中有 21 个活动重做日志文件和 11 个备用重做日志文件，编号按顺序排列。Redo log files use an #ib_redoN naming convention, where N is the redo log file number. Spare redo log files are denoted by a _tmp suffix. The following example shows the redo log files in an #innodb_redo directory, where there are 21 active redo log files and 11 spare redo log files, numbered sequentially.

'#ib_redo582'  '#ib_redo590'  '#ib_redo598'      '#ib_redo606_tmp'
'#ib_redo583'  '#ib_redo591'  '#ib_redo599'      '#ib_redo607_tmp'
'#ib_redo584'  '#ib_redo592'  '#ib_redo600'      '#ib_redo608_tmp'
'#ib_redo585'  '#ib_redo593'  '#ib_redo601'      '#ib_redo609_tmp'
'#ib_redo586'  '#ib_redo594'  '#ib_redo602'      '#ib_redo610_tmp'
'#ib_redo587'  '#ib_redo595'  '#ib_redo603_tmp'  '#ib_redo611_tmp'
'#ib_redo588'  '#ib_redo596'  '#ib_redo604_tmp'  '#ib_redo612_tmp'
'#ib_redo589'  '#ib_redo597'  '#ib_redo605_tmp'  '#ib_redo613_tmp'

每个普通重做日志文件都与特定范围的 LSN 值相关；例如，以下查询显示了前面示例中列出的活动重做日志文件的START_LSN和END_LSN值：Each ordinary redo log file is associated with a particular range of LSN values; for example, the following query shows the START_LSN and END_LSN values for the active redo log files listed in the previous example:

mysql> SELECT FILE_NAME, START_LSN, END_LSN FROM performance_schema.innodb_redo_log_files;
+----------------------------+--------------+--------------+
| FILE_NAME                  | START_LSN    | END_LSN      |
+----------------------------+--------------+--------------+
| ./#innodb_redo/#ib_redo582 | 117654982144 | 117658256896 |
| ./#innodb_redo/#ib_redo583 | 117658256896 | 117661531648 |
| ./#innodb_redo/#ib_redo584 | 117661531648 | 117664806400 |
| ./#innodb_redo/#ib_redo585 | 117664806400 | 117668081152 |
| ./#innodb_redo/#ib_redo586 | 117668081152 | 117671355904 |
| ./#innodb_redo/#ib_redo587 | 117671355904 | 117674630656 |
| ./#innodb_redo/#ib_redo588 | 117674630656 | 117677905408 |
| ./#innodb_redo/#ib_redo589 | 117677905408 | 117681180160 |
| ./#innodb_redo/#ib_redo590 | 117681180160 | 117684454912 |
| ./#innodb_redo/#ib_redo591 | 117684454912 | 117687729664 |
| ./#innodb_redo/#ib_redo592 | 117687729664 | 117691004416 |
| ./#innodb_redo/#ib_redo593 | 117691004416 | 117694279168 |
| ./#innodb_redo/#ib_redo594 | 117694279168 | 117697553920 |
| ./#innodb_redo/#ib_redo595 | 117697553920 | 117700828672 |
| ./#innodb_redo/#ib_redo596 | 117700828672 | 117704103424 |
| ./#innodb_redo/#ib_redo597 | 117704103424 | 117707378176 |
| ./#innodb_redo/#ib_redo598 | 117707378176 | 117710652928 |
| ./#innodb_redo/#ib_redo599 | 117710652928 | 117713927680 |
| ./#innodb_redo/#ib_redo600 | 117713927680 | 117717202432 |
| ./#innodb_redo/#ib_redo601 | 117717202432 | 117720477184 |
| ./#innodb_redo/#ib_redo602 | 117720477184 | 117723751936 |
+----------------------------+--------------+--------------+

在进行检查点时，InnoDB 会将检查点 LSN 存储在包含该 LSN 的文件的头部。在恢复期间，会检查所有重做日志文件，并从最新的检查点 LSN 开始恢复。When doing a checkpoint, InnoDB stores the checkpoint LSN in the header of the file which contains this LSN. During recovery, all redo log files are checked and recovery starts at the latest checkpoint LSN.

提供了多个状态变量用于监控重做日志和重做日志容量调整操作；例如，您可以查询 Innodb_redo_log_resize_status 来查看调整操作的状态：Several status variables are provided for monitoring the redo log and redo log capacity resize operations; for example, you can query Innodb_redo_log_resize_status to view the status of a resize operation:

mysql> SHOW STATUS LIKE 'Innodb_redo_log_resize_status';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_redo_log_resize_status | OK    |
+-------------------------------+-------+

Innodb_redo_log_capacity_resized 状态变量显示当前重做日志容量限制：The Innodb_redo_log_capacity_resized status variable shows the current redo log capacity limit:

mysql> SHOW STATUS LIKE 'Innodb_redo_log_capacity_resized';
 +----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| Innodb_redo_log_capacity_resized | 104857600 |
+----------------------------------+-----------+

其他适用的状态变量包括：Other applicable status variables include:

有关更多信息，请参考状态变量说明。Refer to the status variable descriptions for more information.

您可以通过查询 innodb_redo_log_files 性能模式表来查看活动重做日志文件的信息。以下查询可检索表中所有列的数据：You can view information about active redo log files by querying the innodb_redo_log_files Performance Schema table. The following query retrieves data from all of the table's columns:

SELECT FILE_ID, START_LSN, END_LSN, SIZE_IN_BYTES, IS_FULL, CONSUMER_LEVEL 
FROM performance_schema.innodb_redo_log_files;

当服务器使用 --innodb-dedicated-server 启动时，InnoDB 会自动计算并设置某些 InnoDB 参数的值，包括重做日志容量。自动配置适用于部署在专用 MySQL 服务器上的 MySQL 实例，在这种情况下 MySQL 服务器可以使用所有可用的系统资源。有关更多信息，请参见 第 17.8.12 节，“为专用 MySQL 服务器启用自动 InnoDB 配置”。When the server is started with --innodb-dedicated-server, InnoDB automatically calculates and sets the values for certain InnoDB parameters, including redo log capacity. Automated configuration is intended for MySQL instances that reside on a server dedicated to MySQL, where the MySQL server can use all available system resources. For more information, see Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”.

复制重做日志记录的备份工具在备份操作进行时有时可能无法跟上重做日志的生成速度，导致这些记录被覆盖而丢失重做日志记录。此问题最常发生在备份操作期间 MySQL 服务器活动显著增加，并且重做日志文件存储介质的速度快于备份存储介质时。重做日志归档功能通过将重做日志记录顺序写入归档文件（除了写入重做日志文件之外）来解决此问题。备份工具可以根据需要从归档文件中复制重做日志记录，从而避免潜在的数据丢失。Backup utilities that copy redo log records may sometimes fail to keep pace with redo log generation while a backup operation is in progress, resulting in lost redo log records due to those records being overwritten. This issue most often occurs when there is significant MySQL server activity during the backup operation, and the redo log file storage media operates at a faster speed than the backup storage media. The redo log archiving feature addresses this issue by sequentially writing redo log records to an archive file in addition to the redo log files. Backup utilities can copy redo log records from the archive file as necessary, thereby avoiding the potential loss of data.

如果在服务器上配置了重做日志归档，MySQL 企业备份（可在 MySQL 企业版 获取）在备份 MySQL 服务器时会使用重做日志归档功能。If redo log archiving is configured on the server, MySQL Enterprise Backup, available with MySQL Enterprise Edition, uses the redo log archiving feature when backing up a MySQL server.

在服务器上启用重做日志归档需要为 innodb_redo_log_archive_dirs 系统变量设置一个值。该值以分号分隔的带标签重做日志归档目录列表形式指定。label:directory 对由冒号 (:) 分隔。例如：Enabling redo log archiving on the server requires setting a value for the innodb_redo_log_archive_dirs system variable. The value is specified as a semicolon-separated list of labeled redo log archive directories. The label:directory pair is separated by a colon (:). For example:

mysql> SET GLOBAL innodb_redo_log_archive_dirs='label1:directory_path1[;label2:directory_path2;…]';

这个 label 是归档目录的任意标识符。它可以是任何字符组成的字符串，但不允许使用冒号 (:)。空标签也是允许的，但在这种情况下仍然需要冒号 (:)。必须指定 directory_path。当启用重做日志归档时，选择用于重做日志归档文件的目录必须存在，否则会返回错误。路径中可以包含冒号 (':')，但不允许使用分号 (;)The label is an arbitrary identifier for the archive directory. It can be any string of characters, with the exception of colons (:), which are not permitted. An empty label is also permitted, but the colon (:) is still required in this case. A directory_path must be specified. The directory selected for the redo log archive file must exist when redo log archiving is activated, or an error is returned. The path can contain colons (':'), but semicolons (;) are not permitted.

在启用重做日志归档之前，必须配置 innodb_redo_log_archive_dirs 变量。默认值是 NULL，不允许启用重做日志归档。The innodb_redo_log_archive_dirs variable must be configured before redo log archiving can be activated. The default value is NULL, which does not permit activating redo log archiving.

当支持重做日志归档的备份工具启动备份时，备份工具会通过调用 innodb_redo_log_archive_start() 函数来激活重做日志归档。When a backup utility that supports redo log archiving initiates a backup, the backup utility activates redo log archiving by invoking the innodb_redo_log_archive_start() function.

如果您未使用支持重做日志归档的备份工具，也可以按如下方式手动激活重做日志归档：If you are not using a backup utility that supports redo log archiving, redo log archiving can also be activated manually, as shown:

mysql> SELECT innodb_redo_log_archive_start('label', 'subdir');
+------------------------------------------+
| innodb_redo_log_archive_start('label') |
+------------------------------------------+
| 0                                        |
+------------------------------------------+

或者：Or:

mysql> DO innodb_redo_log_archive_start('label', 'subdir');
Query OK, 0 rows affected (0.09 sec)

其中 label 是由 innodb_redo_log_archive_dirs 定义的标签；subdir 是一个可选参数，用于指定由 label 标识的目录下用来保存归档文件的子目录；它必须是一个简单的目录名称（不允许使用斜杠(/)、反斜杠(\)或冒号(:)）。subdir 可以为空、为 null，或者可以省略。where label is a label defined by innodb_redo_log_archive_dirs; subdir is an optional argument for specifying a subdirectory of the directory identified by label for saving the archive file; it must be a simple directory name (no slash (/), backslash (\), or colon (:) is permitted). subdir can be empty, null, or it can be left out.

只有具有 INNODB_REDO_LOG_ARCHIVE 权限的用户才能通过调用 innodb_redo_log_archive_start() 来激活重做日志归档，或使用 innodb_redo_log_archive_stop() 来停用它。运行备份工具的 MySQL 用户或手动激活和停用重做日志归档的 MySQL 用户必须具有此权限。Only users with the INNODB_REDO_LOG_ARCHIVE privilege can activate redo log archiving by invoking innodb_redo_log_archive_start(), or deactivate it using innodb_redo_log_archive_stop(). The MySQL user running the backup utility or the MySQL user activating and deactivating redo log archiving manually must have this privilege.

重做日志归档文件路径为 directory_identified_by_label/[subdir/]archive.serverUUID.000001.log，其中 directory_identified_by_label 是由 label 参数为 innodb_redo_log_archive_start() 指定的归档目录。subdir 是 innodb_redo_log_archive_start() 的可选参数。The redo log archive file path is directory_identified_by_label/[subdir/]archive.serverUUID.000001.log, where directory_identified_by_label is the archive directory identified by the label argument for innodb_redo_log_archive_start(). subdir is the optional argument used for innodb_redo_log_archive_start().

例如，重做日志归档文件的完整路径和名称如下所示：For example, the full path and name for a redo log archive file appears similar to the following:

/directory_path/subdirectory/archive.e71a47dc-61f8-11e9-a3cb-080027154b4d.000001.log

在备份工具完成复制InnoDB数据文件后，它会通过调用innodb_redo_log_archive_stop()函数来停用重做日志归档。After the backup utility finishes copying InnoDB data files, it deactivates redo log archiving by calling the innodb_redo_log_archive_stop() function.

如果您没有使用支持重做日志归档的备份工具，也可以手动停用重做日志归档，如下所示：If you are not using a backup utility that supports redo log archiving, redo log archiving can also be deactivated manually, as shown:

mysql> SELECT innodb_redo_log_archive_stop();
+--------------------------------+
| innodb_redo_log_archive_stop() |
+--------------------------------+
| 0                              |
+--------------------------------+

或者：Or:

mysql> DO innodb_redo_log_archive_stop();
Query OK, 0 rows affected (0.01 sec)

在停止函数成功完成后，备份工具会从归档文件中查找相关的重做日志数据部分，并将其复制到备份中。After the stop function completes successfully, the backup utility looks for the relevant section of redo log data from the archive file and copies it into the backup.

在备份工具完成复制重做日志数据且不再需要重做日志归档文件后，它会删除该归档文件。After the backup utility finishes copying the redo log data and no longer needs the redo log archive file, it deletes the archive file.

在正常情况下，删除归档文件是备份工具的责任。然而，如果重做日志归档操作在调用innodb_redo_log_archive_stop()之前意外中止，MySQL 服务器会删除该文件。Removal of the archive file is the responsibility of the backup utility in normal situations. However, if the redo log archiving operation quits unexpectedly before innodb_redo_log_archive_stop() is called, the MySQL server removes the file.

性能考虑Performance Considerations

启用重做日志归档通常会由于额外的写入活动而带来轻微的性能开销。Activating redo log archiving typically has a minor performance cost due to the additional write activity.

在 Unix 和类 Unix 操作系统上，性能影响通常较小，前提是更新率不是持续很高。在 Windows 上，假设相同条件下，性能影响通常会略高一些。On Unix and Unix-like operating systems, the performance impact is typically minor, assuming there is not a sustained high rate of updates. On Windows, the performance impact is typically a bit higher, assuming the same.

如果更新率持续很高，并且重做日志归档文件与重做日志文件位于相同的存储介质上，由于写入活动的叠加，性能影响可能会更显著。If there is a sustained high rate of updates and the redo log archive file is on the same storage media as the redo log files, the performance impact may be more significant due to compounded write activity.

如果更新率持续很高，并且重做日志归档文件位于比重做日志文件更慢的存储介质上，性能影响将是任意的。If there is a sustained high rate of updates and the redo log archive file is on slower storage media than the redo log files, performance is impacted arbitrarily.

写入重做日志归档文件不会阻碍正常的事务日志记录，除非重做日志归档文件的存储介质的运行速度远低于重做日志文件的存储介质，并且有大量已持久化的重做日志块等待写入重做日志归档文件。在这种情况下，事务日志记录速率会降低到较慢存储介质可管理的水平，即重做日志归档文件所在的存储介质。Writing to the redo log archive file does not impede normal transactional logging except in the case that the redo log archive file storage media operates at a much slower rate than the redo log file storage media, and there is a large backlog of persisted redo log blocks waiting to be written to the redo log archive file. In this case, the transactional logging rate is reduced to a level that can be managed by the slower storage media where the redo log archive file resides.

您可以使用 ALTER INSTANCE DISABLE INNODB REDO_LOG 语句来禁用重做日志。此功能旨在向新的 MySQL 实例加载数据。通过避免重做日志写入和双写缓冲，禁用重做日志可以加快数据加载速度。You can disable redo logging using the ALTER INSTANCE DISABLE INNODB REDO_LOG statement. This functionality is intended for loading data into a new MySQL instance. Disabling redo logging speeds up data loading by avoiding redo log writes and doublewrite buffering.

[ERROR] [MY-013598] [InnoDB] Server was killed when Innodb Redo 
logging was disabled. Data files could be corrupt. You can try 
to restart the database with innodb_force_recovery=6

启用和禁用重做日志需要 INNODB_REDO_LOG_ENABLE 权限。The INNODB_REDO_LOG_ENABLE privilege is required to enable and disable redo logging.

Innodb_redo_log_enabled 状态变量可用于监控重做日志状态。The Innodb_redo_log_enabled status variable permits monitoring redo logging status.

当重做日志被禁用时，不允许执行克隆操作和重做日志归档操作，反之亦然。Cloning operations and redo log archiving are not permitted while redo logging is disabled and vice versa.

ALTER INSTANCE [ENABLE|DISABLE] INNODB REDO_LOG 操作需要独占备份元数据锁，这会阻止其他 ALTER INSTANCE 操作同时执行。其他 ALTER INSTANCE 操作必须等待锁释放后才能执行。An ALTER INSTANCE [ENABLE|DISABLE] INNODB REDO_LOG operation requires an exclusive backup metadata lock, which prevents other ALTER INSTANCE operations from executing concurrently. Other ALTER INSTANCE operations must wait for the lock to be released before executing.

下面的步骤示范了在向新的 MySQL 实例加载数据时如何禁用重做日志。The following procedure demonstrates how to disable redo logging when loading data into a new MySQL instance.