MySQL 5.7 Release Notes
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 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”.
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, 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 |
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.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 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).
| 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.