16.2.2.2. Slave Status Logs

A replication slave server creates two logs. By default, these logs are files named master.info and relay-log.info and created in the data directory. The names and locations of these files can be changed by using the --master-info-file and --relay-log-info-file options, respectively. In MySQL 5.6 and later, either or both of these logs can also be written to tables in the mysql database by starting the server with the appropriate option: use --master-info-repository to have the master info log written to the mysql.slave_master_info table, and use --relay-log-info-repository to have the relay log info log written to the mysql.slave_relay_log_info table. See Section 16.1.4, "Replication and Binary Logging Options and Variables".

The two 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". Because the status logs are stored on disk, they 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 file or table should be protected because it contains the password for connecting to the master. See Section 6.1.2.3, "Passwords and Logging".

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

Line in `master.info` File	`slave_master_info` Table Column	`SHOW SLAVE STATUS` Column	Description
1	`Number_of_lines`	[None]	Number of lines in the file
2	`Master_log_name`	`Master_Log_File`	The name of the master binary log currently being read from the master
3	`Master_log_pos`	`Read_Master_Log_Pos`	The current position within the master binary log that have been read from the master
4	`Host`	`Master_Host`	The host name of the master
5	`User`	`Master_User`	The user name used to connect to the master
6	`User_password`	Password (not shown by `SHOW SLAVE STATUS`)	The password used to connect to the master
7	`Port`	`Master_Port`	The network port used to connect to the master
8	`Connect_retry`	`Connect_Retry`	The period (in seconds) that the slave will wait before trying to reconnect to the master
9	`Enabled_ssl`	`Master_SSL_Allowed`	Indicates whether the server supports SSL connections
10	`Ssl_ca`	`Master_SSL_CA_File`	The file used for the Certificate Authority (CA) certificate
11	`Ssl_capath`	`Master_SSL_CA_Path`	The path to the Certificate Authority (CA) certificates
12	`Ssl_cert`	`Master_SSL_Cert`	The name of the SSL certificate file
13	`Ssl_cipher`	`Master_SSL_Cipher`	The list of possible ciphers used in the handshake for the SSL connection
14	`Ssl_key`	`Master_SSL_Key`	The name of the SSL key file
15	`Ssl_verify_server_cert`	`Master_SSL_Verify_Server_Cert`	Whether to verify the server certificate
16	`Heartbeat`	[None]	Interval between replication heartbeats, in seconds
17	`Bind`	`Master_Bind`	Which of the slave's network interfaces should be used for connecting to the master
18	`Ignored_server_ids`	`Replicate_Ignore_Server_Ids`	The number of server IDs to be ignored, followed by the actual serverIDs
19	`Uuid`	`Master_UUID`	The master's unique ID
20	`Retry_count`	`Master_Retry_Count`	Maximum number of reconnection attempts permitted (Added in MySQL 5.6.1)

Note

Prior to MySQL 5.6.3, the name of the Ssl_verify_server_cert column was Ssl_verify_servert_cert. (Bug #12407446, Bug #60988)

The slave SQL thread updates the relay log info log. In MySQL 5.6, the relay-log.info file includes a line count and a replication delay value. The following table shows the correspondence between the lines in the relay-log.info file, the columns in the mysql.slave_relay_log_info table, and the columns displayed by SHOW SLAVE STATUS.

Line in `relay-log.info`	`slave_relay_log_info` Table Column	`SHOW SLAVE STATUS` Column	Description
1	`Number_of_lines`	[None]	Number of lines in the file or rows in the table
2	`Relay_log_name`	`Relay_Log_File`	The name of the current relay log file
3	`Relay_log_pos`	`Relay_Log_Pos`	The current position within the relay log file; events up to this position have been executed on the slave database
4	`Master_log_name`	`Relay_Master_Log_File`	The name of the master binary log file from which the events in the relay log file were read
5	`Master_log_pos`	`Exec_Master_Log_Pos`	The equivalent position within the master's binary log file of eventsthat have already been executed
5	`Sql_delay`	`SQL_Delay`	The number of seconds that the slave must lag the master

Prior to MySQL 5.6, the relay-log.info file does not include a line count or a delay value (and the slave_relay_log_info table is not available).

Line	Status Column	Description
1	`Relay_Log_File`	The name of the current relay log file
2	`Relay_Log_Pos`	The current position within the relay log file; events up to this position have been executed on the slave database
3	`Relay_Master_Log_File`	The name of the master binary log file from which the events in the relay log file were read
4	`Exec_Master_Log_Pos`	The equivalent position within the master's binary log file of eventsthat have already been executed

Note

If you downgrade a slave server to a version older than MySQL 5.6, the older server does not read the relay-log.info file correctly. To address this, modify the file in a text editor by deleting the initial line containing the number of lines.

The contents of the relay-log.info file and the states shown by the SHOW SLAVE STATUS statement might not match if the relay-log.info file has not been flushed to disk. Ideally, you should only view relay-log.info on a slave that is offline (that is, mysqld is not running). For a running system, you can use SHOW SLAVE STATUS, or query the slave_master_info and slave_relay_log_info tables if you are writing the status logs to tables.

When you back up the slave's data, you should back up these two status logs, along with the relay log files. The status logs are needed to resume replication after you restore the data from the slave. If you lose the relay logs 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.