MySQL master_info_repository and relay_log_info_repository

This article explains the purpose of the master_info_repository and relay_log_info_repository system variables in MySQL replication. It covers what these settings do, the differences between their storage options, and why configuring them correctly is critical for maintaining database crash safety.

In MySQL replication, a replica needs to keep track of its progress to know where it stands relative to the source database. The master_info_repository and relay_log_info_repository settings determine where the replication metadata and status details are stored on the replica.

What is master_info_repository?

The master_info_repository setting determines where the replica stores information about its connection to the source (master) server. This metadata includes: * Hostname and port of the source. * Replication login credentials. * The current binary log file and position that the replica’s I/O thread has read from the source.

Historically, this information was stored in a file named master.info in the data directory.

What is relay_log_info_repository?

The relay_log_info_repository setting determines where the replica stores execution progress status. This metadata includes: * The relay log files and positions that have been executed by the replica’s SQL thread. * The corresponding binary log file and position on the source server.

Historically, this information was stored in a file named relay-log.info in the data directory.

Storage Options: FILE vs. TABLE

Both parameters can be configured to use one of two storage types:

1. FILE: Metadata is written to traditional text files (master.info and relay-log.info).
2. TABLE: Metadata is written to transactional InnoDB tables in the system database: mysql.slave_master_info and mysql.slave_relay_log_info.

The Importance of Table-Based Repositories for Crash Safety

The primary reason to configure these repositories is to ensure crash safety.

When repositories are set to FILE, the replication state updates are not synchronized atomically with the data changes written to the database. If the replica server crashes unexpectedly, the files on disk may not match the actual state of the databases. This can result in replication lag, duplicate key errors, or missing data upon recovery because the replica may attempt to re-read or re-apply transactions it has already processed.

When repositories are set to TABLE (and the tables use the InnoDB engine), the updates to the replication state tables occur within the same transaction as the data updates. If the server crashes, the replication progress is rolled back or committed atomically along with the data. This guarantees that when the replica restarts, it resumes replication from the exact transaction where it left off, preventing data inconsistency.

Modern MySQL Recommendations

In MySQL 5.7, the default value for these settings was FILE, making it a best practice to manually change them to TABLE.

In MySQL 8.0, the default was changed to TABLE. Furthermore, in MySQL 8.0.23 and later, these settings have been deprecated, and the file-based repositories are no longer supported in newer releases, making transactional, table-based replication metadata storage the default standard.