The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. During normal operations, the redo log encodes requests to change table data that result from SQL statements or low-level API calls. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically during initialization, and before connections are accepted. For information about the role of the redo log in crash recovery, see Section 15.18.2, “InnoDB Recovery”.
By default, the redo log is physically represented on disk by two
ib_logfile1. MySQL writes to the redo log
files in a circular fashion. Data in the redo log is encoded in
terms of records affected; this data is collectively referred to
as redo. The passage of data through the redo log is represented
by an ever-increasing LSN value.
Information and procedures related to redo logs are described under the following topics in the section:
To change the number or the size of redo log files, perform the following steps:
InnoDB detects that the
from the redo log file size, it writes a log checkpoint, closes
and removes the old log files, creates new log files at the
requested size, and opens the new log files.
InnoDB, like any other
ACID-compliant database engine,
flushes the redo log of a
transaction before it is committed.
uses group commit
functionality to group multiple flush requests together to avoid
one flush for each commit. With group commit,
InnoDB issues a single write to the log file
to perform the commit action for multiple user transactions that
commit at about the same time, significantly improving
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, introduced in MySQL 8.0.17, 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.
Enabling redo log archiving on the server requires setting a
value for the
system variable. The value is specified as a semicolon-separated
list of labeled redo log archive directories. The
pair is separated by a colon (
mysql> SET GLOBAL innodb_redo_log_archive_dirs='label1:directory_path1[;label2:directory_path2;…]';
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
variable must be configured before the redo log archiving can be
activated. The default value is
does not permit activating redo log archiving.
The archive directories that you specify must satisfy the following requirements. (The requirements are enforced when redo log archiving is activated.):
Directories must exist. Directories are not created by the redo log archive process. Otherwise, the following error is returned:
ERROR 3844 (HY000): Redo log archive directory '
directory_path1' does not exist or is not a directory
Directories must not be world-accessible. This is to prevent the redo log data from being exposed to unauthorized users on the system. Otherwise, the following error is returned:
ERROR 3846 (HY000): Redo log archive directory '
directory_path1' is accessible to all OS users
Directories cannot be those defined by
secure_file_priv, nor can they be parent directories or subdirectories of those directories. Otherwise, an error similar to the following is returned:
ERROR 3845 (HY000): Redo log archive directory '
directory_path1' is in, under, or over server directory 'datadir' - '
When a backup utility that supports redo log archiving initiates
a backup, the backup utility activates redo log archiving by
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 | +------------------------------------------+
mysql> DO innodb_redo_log_archive_start('label', 'subdir'); Query OK, 0 rows affected (0.09 sec)
The MySQL session that activates redo log archiving (using
remain open for the duration of the archiving. The same
session must deactivate redo log archiving (using
innodb_redo_log_archive_stop()). If the
session is terminated before the redo log archiving is
explicitly deactivated, the server deactivates redo log
archiving implicitly and removes the redo log archive file.
label is a label defined by
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.
Only users with the
can activate redo log archiving by invoking
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
The redo log archive file path is
is the archive directory identified by the
optional argument used for
For example, the full path and name for a redo log archive file appears similar to the following:
After the backup utility finishes copying
InnoDB data files, it deactivates redo log
archiving by calling the
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 | +--------------------------------+
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.
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.
Activating redo log archiving typically has a minor performance cost due to the additional write activity.
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.
As of MySQL 8.0.21, you can disable redo logging using the
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.
This feature is intended only for loading data into a new MySQL instance. Do not disable redo logging on a production system. It is permitted to shutdown and restart the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption.
Attempting to restart the server after an unexpected server stoppage while redo logging is disabled is refused with the following error:
[ERROR] [MY-013578] [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
In this case, initialize a new MySQL instance and start the data loading procedure again.
privilege is required to enable and disable redo logging.
status variable permits monitoring redo logging status.
Cloning operations and redo log archiving are not permitted while redo logging is disabled and vice versa.
INSTANCE [ENABLE|DISABLE] INNODB REDO_LOG operation
requires an exclusive backup metadata lock, which prevents other
ALTER INSTANCE operations from
executing concurrently. Other
INSTANCE operations must wait for the lock to be
released before executing.
The following procedure demonstrates how to disable redo logging when loading data into a new MySQL instance.
On the new MySQL instance, grant the
INNODB_REDO_LOG_ENABLEprivilege to the user account responsible for disabling redo logging.
mysql> GRANT INNODB_REDO_LOG_ENABLE ON *.* to 'data_load_admin';
data_load_adminuser, disable redo logging:
mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
Innodb_redo_log_enabledstatus variable to ensure that redo logging is disabled.
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Innodb_redo_log_enabled | OFF | +-------------------------+-------+
Run the data load operation.
data_load_adminuser, enable redo logging after the data load operation finishes:
mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
Innodb_redo_log_enabledstatus variable to ensure that redo logging is enabled.
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Innodb_redo_log_enabled | ON | +-------------------------+-------+