17.2.4.2 Slave Status Logs

A replication slave server creates two slave status logs in the form of InnoDB tables in the mysql database: the master info log slave_master_info, and the relay log info log slave_relay_log_info.

The two slave status logs contain information like that shown in the output of the SHOW SLAVE STATUS statement, which is discussed in Section 13.4.2, “SQL Statements for Controlling Slave Servers”. The slave status logs survive a slave server's shutdown. The next time the slave starts up, it reads the two logs to determine how far it has proceeded in reading binary logs from the master and in processing its own relay logs.

The master info log table should be protected because it contains the password for connecting to the master. See Section 6.1.2.3, “Passwords and Logging”.

Before MySQL 8.0, to create the slave status logs as tables, it was necessary to specify the options --master-info-repository=TABLE and --relay-log-info-repository=TABLE at server startup. Otherwise, the logs were created as files in the data directory named master.info and relay-log.info, or with alternative names and locations specified by the --master-info-file and --relay-log-info-file options. From MySQL 8.0, creating the slave status logs as tables is the default, and creating the slave status logs as files is deprecated. For more information, see Section 17.1.6, “Replication and Binary Logging Options and Variables”.

The mysql.slave_master_info and mysql.slave_relay_log_info tables are created using the transactional storage engine InnoDB. Updates to the relay log info log table are committed together with the transactions, meaning that the slave's progress information recorded in that log is always consistent with what has been applied to the database, even in the event of an unexpected server halt. The --relay-log-recovery option must be enabled on the slave to guarantee resilience. For more details, see Section 17.3.2, “Handling an Unexpected Halt of a Replication Slave”.

One additional slave status log is created primarily for internal use, and holds status information about worker threads on a multithreaded replication slave. This slave worker log includes the names and positions for the relay log file and master binary log file for each worker thread. If the relay log info log for the slave is created as a table, which is the default, the slave worker log is written to the mysql.slave_worker_info table. If the relay log info log is written to a file, the slave worker log is written to the worker-relay-log.info file. For external use, status information for worker threads is presented in the Performance Schema table replication_applier_status_by_worker.

The slave I/O thread updates the master info log. The following table shows the correspondence between the columns in the mysql.slave_master_info table, the columns displayed by SHOW SLAVE STATUS, and the lines in the deprecated master.info file.

The slave SQL thread updates the relay log info log. The following table shows the correspondence between the columns in the mysql.slave_relay_log_info table, the columns displayed by SHOW SLAVE STATUS, and the lines in the deprecated relay-log.info file.

When you back up the replication slave's data, ensure that you back up the mysql.slave_master_info and mysql.slave_relay_log_info tables containing the slave status logs, because they are needed to resume replication after you restore the data from the slave. If you lose the relay log files, but still have the relay log info log, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_LOG_FILE and MASTER_LOG_POS options to tell the slave to re-read the binary logs from that point. Of course, this requires that the binary logs still exist on the master.