Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.8Mb
PDF (A4) - 37.8Mb
PDF (RPM) - 36.4Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.6Mb
Man Pages (TGZ) - 209.5Kb
Man Pages (Zip) - 318.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

16.2.4.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. 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.6, “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”.

Setting relay_log_info_repository and master_info_repository to TABLE can improve resilience to unexpected halts. The master info log stores information required for the recovery of the slave's I/O thread, and the relay log info log stores information required for the recovery of the SQL thread. The updates to the tables are committed together with the transaction, meaning that the information in them is always consistent with what has been applied to the database, even in the event of a server halt. The --relay-log-recovery option must be enabled on the slave to guarantee resilience. For more details, see Section 16.3.2, “Handling an Unexpected Halt of a Replication Slave”.

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.

master.info File Line slave_master_info Table Column SHOW SLAVE STATUS Column Description
1 Number_of_lines [None] Number of lines in the file, or columns in the table
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_name 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 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.
19 Uuid Master_UUID The master's unique ID
20 Retry_count Master_Retry_Count Maximum number of reconnection attempts permitted
21 Ssl_crl [None] Path to an ssl certificate revocation list file
22 Ssl_crl_path [None] Path to a directory containing ssl certificate revocation list files
23 Enabled_auto_position Auto_position If autopositioning is in use or not
24 Channel_name Channel_name The name of the replication channel
25 Tls_Version Master_TLS_Version TLS version on master

The slave SQL thread updates the relay log info log. 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 columns 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 events that have already been executed
6 Sql_delay SQL_Delay The number of seconds that the slave must lag the master
7 Number_of_workers [None] The number of slave worker threads for executing replication events (transactions) in parallel
8 Id [None] ID used for internal purposes; currently this is always 1
9 Channel_name Channel_name The name of the replication channel

In versions of MySQL 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 events that 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.


User Comments
Sign Up Login You must be logged in to post a comment.