When performing physical backup on system that are heavily used, it can happen that the backup speed cannot keep up with the redo log generation. This can happen when the backup storage is slower than the redo log storage media and this can lead in inconsistency in the generated backup.

MySQL Enterprise Backup (aka MEB) and probably others, benefit from the possibility to sequentially write redo log records to an archive file in addition to the redo log files.

This feature was introduced in MySQL 8.0.17.

How to enable it ?

To enable this feature, two settings are necessary:

• set globally a directory where those archiving logs can be stored
• start the archiving process in a session by calling the dedicated function

The global variable is  innodb_redo_log_archive_dirs.

This variable musts contain labelled directories where the archiving redo logs can be stored. The format is a semi-colon separated string like this:

innodb_redo_log_archive_dirs='label1:/backups1;label2:/backups2'

The system user running mysqld must have access to those directories and should not be accessible to all users.

The redo log archiving is started using the function innodb_redo_log_archive_start() and stopped using innodb_redo_log_archive_stop(). Only users with the INNODB_REDO_LOG_ARCHIVE privilege can call those functions.

It’s important to notice that the MySQL session that activates redo log archiving must remain open for the duration of the archiving. You must deactivate redo log archiving in the same session. 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.

Let’s see how to enable it:

$ sudo mkdir -p /var/lib/mysql-redo-archive/backup1
$ sudo chown mysql. -R /var/lib/mysql-redo-archive
$ sudo chmod -R 700 /var/lib/mysql-redo-archive/

In fact, it is ready to work but it is not enabled, only when a session, usually the one that initializes the backup, will invoke the innodb_redo_log_archive_start() function it will be enabled:

Is it enabled ?

How can we see that the redo log archiving is active ?

We can check if MySQL is using a redo log archive file using the following query:

select * from performance_schema.file_instances
   where event_name like '%::redo_log_archive_file'\G

If there is an entry, this means that the redo log archive process is enabled or has been enabled and stopped successfully using the dedicated function:

So this is not enough to be sure that the redo log archiving is active. But we have the possibility to also check if the thread is active using this query:

select thread_id, name, type from threads 
   where name like '%redo_log_archive%';

If a row is returned, it means that the redo log archiving is enabled and active:

Error Messages

Here are some common error messages related to Redo Log Archiving:

ERROR: 3850 (HY000): Redo log archiving failed: Session terminated with active redo log archiving - stopped redo log archiving and deleted the file. This error happens when you try to stop the redo log archiving from another session and the session that started it was terminated.

ERROR: 3851 (HY000): Redo log archiving has not been started by this session. This is when the session that started the process is still open and you try to stop the redo log archiving from another session.

ERROR: 3848 (HY000): Redo log archiving has been started on '/var/lib/mysql-redo-archive/backup2/archive.17f6a975-e2b4-11ec-b714-c8cb9e32df8e.000001.log' - Call innodb_redo_log_archive_stop() first:  this happens when you try to start the archiving process and there is already one active.

ERROR: 3842 (HY000): Label 'backup2' not found in server variable 'innodb_redo_log_archive_dirs': this is when you try to start the redo log archiving and you are using a label which is not defined in innodb_redo_log_archive_dirs.

ERROR: 3846 (HY000): Redo log archive directory '/var/lib/mysql-redo-archive/backup2' is accessible to all OS users: this is when the directory is accessible by others users too. Only the user running mysqld should have access to it.

ERROR: 3844 (HY000): Redo log archive directory '/var/lib/mysql-redo-archive/backup3' does not exist or is not a directory: this is a very common error, it happens when the subdir is not existing in the directory defined by the corresponding label in innodb_redo_log_archive_dirs. In this example, backup3 is not created in /var/lib/mysql-redo-archive.

ERROR: 3847 (HY000): Cannot create redo log archive file '/var/lib/mysql-redo-archive/backup3/archive.17f6a975-e2b4-11ec-b714-c8cb9e32df8e.000001.log' (OS errno: 13 - Permission denied): this is simple to understand, the directory and sub-directory exist but doesn’t belong to the user running mysqld (usually mysql).

Callable Functions

There are several functions that are related to Redo Log Archiving, we already used 2 of them to start and stop the process. Here is the list as MySQL 8.0.31:

innodb_redo_log_archive_flush is used to flush the redo log archive queue.

innodb_redo_log_sharp_checkpoint makes a checkpoint calling log_make_lastest_checkpoint(*log_sys)

The consumer functions are also used by MEB to allow incremental backups using only the redo log since the new dynamic redo log implementation in 8.0.30 (page 57 of this manual).

Conclusion

Although not yet popular, this feature is mandatory for heavy workloads when backup storage does not have the same capacity as production storage and is not able to keep up with the speed of writes.

When enabled by the DBA, MySQL Enterprise Backup will use it automatically. To know if a the redo log archiving process was started and is still active, the DBA can check the performance_schema.threads table.