16.2.4.2 Replication Applier Metadata Repositories
A replica server creates two replication applier metadata repositories, the replication connection metadata repository and the replication applier metadata repository. The replication applier metadata repositories survive a replica server's shutdown. If binary log file position based replication is in use, when the replica restarts, it reads the two repositories to determine how far it previously proceeded in reading the binary log from the source and in processing its own relay log. If GTID-based replication is in use, the replica does not use the replication applier metadata repositories for that purpose, but does need them for the other metadata that they contain.
The replication connection metadata repository contains information that the replication I/O thread needs to connect to the replication source server and retrieve transactions from the source's binary log. The metadata in this repository includes the connection configuration, the replication user account details, the SSL settings for the connection, and the file name and position where the replication I/O thread is currently reading from the source's binary log.
The replication applier metadata repository contains information that the replication SQL thread needs to read and apply transactions from the replica's relay log. The metadata in this repository includes the file name and position up to which the replication SQL thread has executed the transactions in the relay log, and the equivalent position in the source's binary log. It also includes metadata for the process of applying transactions, such as the number of worker threads.
By default, the replication applier metadata repositories are 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
option and relay_log_info_file
system variable. To create the replication applier metadata repositories as tables, specify master_info_repository=TABLE
and relay_log_info_repository=TABLE
at server startup. In that case, the replication connection metadata repository is written to the slave_master_info
table in the mysql
system schema, and the replication applier metadata repository is written to the slave_relay_log_info
table in the mysql
system schema. A warning message is issued if mysqld is unable to initialize the tables for the replication applier metadata repositories, but the replica is allowed to continue starting. This situation is most likely to occur when upgrading from a version of MySQL that does not support the use of tables for the repositories to one in which they are supported.
Do not attempt to update or insert rows in the
mysql.slave_master_info
ormysql.slave_relay_log_info
tables manually. Doing so can cause undefined behavior, and is not supported. Execution of any statement requiring a write lock on either or both of theslave_master_info
andslave_relay_log_info
tables is disallowed while replication is ongoing (although statements that perform only reads are permitted at any time).Access to the replication connection metadata repository file or table should be restricted to the database administrator, because it contains the replication user account name and password for connecting to the source. Use a restricted access mode to protect database backups that include this repository.
RESET SLAVE
clears the data in the replication applier metadata repositories, with the exception of the replication connection parameters (depending on the MySQL Server release and repository type). For details, see the description for RESET SLAVE
.
If you set master_info_repository
and relay_log_info_repository
to TABLE
, the mysql.slave_master_info
and mysql.slave_relay_log_info
tables are created using the InnoDB
transactional storage engine. Updates to the replication applier metadata repository table are committed together with the transactions, meaning that the replica's progress information recorded in that repository 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 replica to guarantee resilience. For more details, see Section 16.3.2, “Handling an Unexpected Halt of a Replica”.
When you back up the replica's data or transfer a snapshot of its data to create a new replica, ensure that you include the mysql.slave_master_info
and mysql.slave_relay_log_info
tables containing the replication applier metadata repositories, or the equivalent files (master.info
and relay-log.info
in the data directory, unless you specified alternative names and locations). When binary log file position based replication is in use, the replication applier metadata repositories are needed to resume replication after restarting the restored or copied replica. If you do not have the relay log files, but still have the replication applier metadata repository, you can check it to determine how far the replication SQL thread has executed in the source's binary log. Then you can use a CHANGE MASTER TO
statement with the MASTER_LOG_FILE
and MASTER_LOG_POS
options to tell the replica to re-read the binary logs from the source from that point (provided that the required binary logs still exist on the source).
One additional repository, the replication applier worker metadata repository, is created primarily for internal use, and holds status information about worker threads on a multithreaded replica. The replication applier worker metadata repository includes the names and positions for the relay log file and the source's binary log file for each worker thread. If the replication applier metadata repository is created as a table, which is the default, the replication applier worker metadata repository is written to the mysql.slave_worker_info
table. If the replication applier metadata repository is written to a file, the replication applier worker metadata repository 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 replication applier metadata repositories originally contained 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 Replica Servers”. Further information has since been added to the replication applier metadata repositories which is not displayed by the SHOW SLAVE STATUS
statement.
For the replication connection metadata repository, 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 master.info
file.
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 binary log currently being read from the source |
3 | Master_log_pos |
Read_Master_Log_Pos |
The current position within the binary log that has been read from the source |
4 | Host |
Master_Host |
The host name of the source server |
5 | User_name |
Master_User |
The replication user name used to connect to the source |
6 | User_password |
Password (not shown by SHOW SLAVE STATUS ) |
The password used to connect to the source |
7 | Port |
Master_Port |
The network port used to connect to the source |
8 | Connect_retry |
Connect_Retry |
The period (in seconds) that the replica will wait before trying to reconnect to the source |
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 replica's network interfaces should be used for connecting to the source |
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 source'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_crlpath |
[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 source |
For the replication applier metadata repository, 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 relay-log.info
file.
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 replica database |
4 | Master_log_name |
Relay_Master_Log_File |
The name of the source's 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 source's binary log file of events that have already been executed |
6 | Sql_delay |
SQL_Delay |
The number of seconds that the replica must lag the source |
7 | Number_of_workers |
[None] | The number of worker threads on the replica 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 replica database |
3 | Relay_Master_Log_File |
The name of the source's binary log file from which the events in the relay log file were read |
4 | Exec_Master_Log_Pos |
The equivalent position within the source's binary log file of events that have already been executed |
If you downgrade a replica 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 replica that is offline (that is, mysqld
is not running). For a running system, you can use SHOW SLAVE STATUS
, or query the mysql.slave_master_info
and mysql.slave_relay_log_info
tables if you are writing the replication applier metadata repositories to tables.