Rechercher dans le manuel MySQL
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 system schema: the master info log
slave_master_info, and the relay log info log
slave_relay_log_info.
The two slave status logs contain information similar to 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, it reads the two logs to determine how far it previously
proceeded in reading binary logs from the master and in
processing its own relay logs.
Access privileges for the master info log table should be restricted 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
--master-info-repository=TABLE
and
--relay-log-info-repository=TABLE
options 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 InnoDB transactional
storage engine. 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
replication_applier_status_by_worker
table.
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 |
Master_compression_algorithm |
[None] | 28 | Permitted compression algorithms |
Master_zstd_compression_level |
[None] | 29 | zstd compression level |
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.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-slave-logs-status.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.
