Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.5Mb
PDF (A4) - 35.5Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 201.6Kb
Man Pages (Zip) - 306.9Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

18.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. In MySQL 5.7, 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 18.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 14.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 7.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 Fileslave_master_info Table ColumnSHOW SLAVE STATUS ColumnDescription
1Number_of_lines[None]Number of lines in the file, or columns in the table
2Master_log_nameMaster_Log_FileThe name of the master binary log currently being read from the master
3Master_log_posRead_Master_Log_PosThe current position within the master binary log that have been read from the master
4HostMaster_HostThe host name of the master
5User_nameMaster_UserThe user name used to connect to the master
6User_passwordPassword (not shown by SHOW SLAVE STATUS)The password used to connect to the master
7PortMaster_PortThe network port used to connect to the master
8Connect_retryConnect_RetryThe period (in seconds) that the slave will wait before trying to reconnect to the master
9Enabled_sslMaster_SSL_AllowedIndicates whether the server supports SSL connections
10Ssl_caMaster_SSL_CA_FileThe file used for the Certificate Authority (CA) certificate
11Ssl_capathMaster_SSL_CA_PathThe path to the Certificate Authority (CA) certificates
12Ssl_certMaster_SSL_CertThe name of the SSL certificate file
13Ssl_cipherMaster_SSL_CipherThe list of possible ciphers used in the handshake for the SSL connection
14Ssl_keyMaster_SSL_KeyThe name of the SSL key file
15Ssl_verify_server_certMaster_SSL_Verify_Server_CertWhether to verify the server certificate
16Heartbeat[None]Interval between replication heartbeats, in seconds
17BindMaster_BindWhich of the slave's network interfaces should be used for connecting to the master
18Ignored_server_idsReplicate_Ignore_Server_IdsThe 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.
19UuidMaster_UUIDThe master's unique ID
20Retry_countMaster_Retry_CountMaximum number of reconnection attempts permitted
21Ssl_crl[None]Path to an ssl certificate revocation list file
22Ssl_crl_path[None]Path to a directory containing ssl certificate revocation list files
23Enabled_auto_positionAuto_positionIf autopositioning is in use or not
24Channel_nameChannel_nameThe name of the replication channel

The slave SQL thread updates the relay log info log. In MySQL 5.7, 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.infoslave_relay_log_info Table ColumnSHOW SLAVE STATUS ColumnDescription
1Number_of_lines[None]Number of lines in the file or columns in the table
2Relay_log_nameRelay_Log_FileThe name of the current relay log file
3Relay_log_posRelay_Log_PosThe current position within the relay log file; events up to this position have been executed on the slave database
4Master_log_nameRelay_Master_Log_FileThe name of the master binary log file from which the events in the relay log file were read
5Master_log_posExec_Master_Log_PosThe equivalent position within the master's binary log file of events that have already been executed
6Sql_delaySQL_DelayThe number of seconds that the slave must lag the master
7Number_of_workers[None]The number of slave worker threads for executing replication events (transactions) in parallel
8Id[None]ID used for internal purposes; currently this is always 1
9Channel_nameChannel_nameThe name of the replication channel

In older 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).

LineStatus ColumnDescription
1Relay_Log_FileThe name of the current relay log file
2Relay_Log_PosThe current position within the relay log file; events up to this position have been executed on the slave database
3Relay_Master_Log_FileThe name of the master binary log file from which the events in the relay log file were read
4Exec_Master_Log_PosThe 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.