Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.2Mb
PDF (A4) - 38.3Mb
PDF (RPM) - 33.1Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 133.2Kb
Man Pages (Zip) - 189.3Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

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.

slave_master_info Table Column SHOW SLAVE STATUS Column master.info File Line Description
Number_of_lines [None] 1 Number of columns in the table (or lines in the file)
Master_log_name Master_Log_File 2 The name of the master binary log currently being read from the master
Master_log_pos Read_Master_Log_Pos 3 The current position within the master binary log that has been read from the master
Host Master_Host 4 The host name of the master
User_name Master_User 5 The user name used to connect to the master
User_password Password (not shown by SHOW SLAVE STATUS) 6 The password used to connect to the master
Port Master_Port 7 The network port used to connect to the master
Connect_retry Connect_Retry 8 The period (in seconds) that the slave will wait before trying to reconnect to the master
Enabled_ssl Master_SSL_Allowed 9 Indicates whether the server supports SSL connections
Ssl_ca Master_SSL_CA_File 10 The file used for the Certificate Authority (CA) certificate
Ssl_capath Master_SSL_CA_Path 11 The path to the Certificate Authority (CA) certificates
Ssl_cert Master_SSL_Cert 12 The name of the SSL certificate file
Ssl_cipher Master_SSL_Cipher 13 The list of possible ciphers used in the handshake for the SSL connection
Ssl_key Master_SSL_Key 14 The name of the SSL key file
Ssl_verify_server_cert Master_SSL_Verify_Server_Cert 15 Whether to verify the server certificate
Heartbeat [None] 16 Interval between replication heartbeats, in seconds
Bind Master_Bind 17 Which of the slave's network interfaces should be used for connecting to the master
Ignored_server_ids Replicate_Ignore_Server_Ids 18 The list of server IDs to be ignored. Note that for Ignored_server_ids the list of server IDs is preceded by the total number of server IDs to ignore.
Uuid Master_UUID 19 The master's unique ID
Retry_count Master_Retry_Count 20 Maximum number of reconnection attempts permitted
Ssl_crl [None] 21 Path to an ssl certificate revocation list file
Ssl_crl_path [None] 22 Path to a directory containing ssl certificate revocation list files
Enabled_auto_position Auto_position 23 If autopositioning is in use or not
Channel_name Channel_name 24 The name of the replication channel
Tls_Version Master_TLS_Version 25 TLS version on master
Master_public_key_path Master_public_key_path 26 Name of RSA public key file
Get_master_public_key Get_master_public_key 27 Whether to request RSA public key from master

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.

slave_relay_log_info Table Column SHOW SLAVE STATUS Column Line in relay-log.info File Description
Number_of_lines [None] 1 Number of columns in the table or lines in the file
Relay_log_name Relay_Log_File 2 The name of the current relay log file
Relay_log_pos Relay_Log_Pos 3 The current position within the relay log file; events up to this position have been executed on the slave database
Master_log_name Relay_Master_Log_File 4 The name of the master binary log file from which the events in the relay log file were read
Master_log_pos Exec_Master_Log_Pos 5 The equivalent position within the master's binary log file of events that have already been executed
Sql_delay SQL_Delay 6 The number of seconds that the slave must lag the master
Number_of_workers [None] 7 The number of slave applier threads for executing replication events (transactions) in parallel
Id [None] 8 ID used for internal purposes; currently this is always 1
Channel_name Channel_name 9 The name of the replication channel

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.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.