mysql show slave_mysql ，show slave status详解

• Slave_IO_State

A copyof the State field of the SHOW PROCESSLIST output for the slave I/O thread. This tells you whatthe threadis doing: trying to connect to the master, waiting for events from the master, reconnecting tothe master,and so on. For a listing of possible states, see Section 8.14.6, “Replication Slave I/O ThreadStates”.

• Master_Host

The master host that the slaveis connected to.

• Master_User

Theuser name of the account used to connect tothe master.

• Master_Port

The port usedto connect tothe master.

• Connect_Retry

Thenumber of seconds between connect retries (default 60). This can be set withthe CHANGE MASTERTOstatement.

• Master_Log_File

The nameof the master binary log file from which the I/O thread iscurrently reading.

• Read_Master_Log_Pos

The positionin the current master binary log file up to which the I/O thread has read.

• Relay_Log_File

The nameof the relay log file from which the SQL thread is currently reading andexecuting.

• Relay_Log_Pos

The positionin the current relay log file up to which the SQL thread has read andexecuted.

• Relay_Master_Log_File

The nameof the master binary log file containing the most recent event executed bythe SQL thread.

• Slave_IO_Running

Whether the I/O thread is started and has connected successfully to the master. Internally, the state ofthis threadis represented by one of the following three values:

• MYSQL_SLAVE_NOT_RUN. The slave I/O thread is not running. Forthis state,

Slave_IO_RunningisNo.

• MYSQL_SLAVE_RUN_NOT_CONNECT. The slave I/O thread is running, but is not connected toareplication master. For this state, Slave_IO_Running isConnecting.

• MYSQL_SLAVE_RUN_CONNECT. The slave I/O thread is running, and is connected toareplication master. For this state, Slave_IO_Running isYes.

The valueof the Slave_running system status variable corresponds withthis value.

• Slave_SQL_Running

Whether the SQL threadisstarted.

• Replicate_Do_DB, Replicate_Ignore_DB

The listsof databases that were specified with the --replicate-do-db and --replicate-ignoredb options, if any.

• Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table,

Replicate_Wild_Ignore_Table

The listsof tables that were specified with the --replicate-do-table, --replicate-ignoretable, --replicate-wild-do-table, and --replicate-wild-ignore-table options, if any.

• Last_Errno, Last_Error

These columns are aliasesfor Last_SQL_Errno andLast_SQL_Error.

Issuing RESET MASTERor RESET SLAVE resets the values shown inthese columns.

NoteWhen the slave SQL thread receives an error, it reports the error first, thenstops the SQL thread. This means that thereis a small window oftime during which

SHOW SLAVE STATUS shows a nonzero valueforLast_SQL_Errno even though Slave_SQL_Running still displays Yes.

• Skip_Counter

Thecurrent value of the sql_slave_skip_counter system variable. See Section 13.4.2.4, “SETGLOBAL sql_slave_skip_counter Syntax”.

• Exec_Master_Log_Pos

The positionin the current master binary log file to which the SQL thread has read andexecuted,

marking the startof the next transaction or event to be processed. You can use this value withthe CHANGE MASTERTO statement's MASTER_LOG_POS option when starting a new slave from an existing slave, so that the new slave reads from this point. The coordinates given by

(Relay_Master_Log_File, Exec_Master_Log_Pos) in the master's binary log correspond tothe coordinates givenby (Relay_Log_File, Relay_Log_Pos) in the relay log.When using a multithreaded slave (by setting slave_parallel_workers toa nonzero value), the valuein this column actually represents a “low-water” mark, before which no uncommittedtransactions

remain. Because thecurrent implementation allows execution of transactions on different databases ina differentorder on the slave than on the master, this is not necessarily the position ofthe most recently

executedtransaction.

• Relay_Log_Space

The total combined sizeof all existing relay logfiles.

• Until_Condition, Until_Log_File, Until_Log_Pos

Thevalues specified in the UNTIL clause ofthe START SLAVE statement. Until_Condition has thesevalues:

• Noneifno UNTIL clause was specified

• Masterif the slave is reading until a given position in the master's binary log

• Relay if the slave is reading until a given position in its relay log

• SQL_BEFORE_GTIDS if the slave SQL thread is processing transactions until it has reached the first

transaction whose GTID is listed in the gtid_set.

• SQL_AFTER_GTIDS if the slave threads are processing all transactions until the last transaction in the

gtid_set has been processed by both threads.

• SQL_AFTER_MTS_GAPS if a multithreaded slave's SQL threads are running until no more gaps are

foundin the relay log.

Until_Log_Fileand Until_Log_Pos indicate the log file name andposition that define the

coordinates at which the SQL thread stops executing.For more information on UNTIL clauses, see Section 13.4.2.5, “START SLAVE Syntax”.

• Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert,

Master_SSL_Cipher, Master_SSL_CRL_File, Master_SSL_CRL_Path, Master_SSL_Key,

Master_SSL_Verify_Server_Cert

These fields show the SSL parameters usedby the slave to connect to the master, if any.

Master_SSL_Allowed has thesevalues:

• Yesif an SSL connection to the master ispermitted

• Noif an SSL connection to the master is notpermitted

• Ignoredif an SSL connection is permitted but the slave server does nothave SSL support enabled

Thevalues of the other SSL-related fields correspond to the values ofthe MASTER_SSL_CA,

MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_CIPHER, MASTER_SSL_CRL,

MASTER_SSL_CRLPATH, MASTER_SSL_KEY,and MASTER_SSL_VERIFY_SERVER_CERT options tothe

CHANGE MASTERTO statement. See Section 13.4.2.1, “CHANGE MASTER TOSyntax”.

• Seconds_Behind_Master

This fieldis an indication of how “late” the slave is:

•When the slave is actively processing updates, this field shows the difference between the currenttimestamp on the slave and the original timestamp logged on the master forthe event currently being processedonthe slave.

•When no event is currently being processed on the slave, this value is 0.In essence, this field measures the time difference in seconds between the slave SQL thread andthe slave I/O thread. If the network connection between master and slave is fast, the slave I/O thread isveryclose to the master, so this field is a good approximation of how late the slave SQL thread iscomparedto the master. If the network is slow, this is nota good approximation; the slave SQL thread may quite often be caught upwith the slow-reading slave I/O thread, so Seconds_Behind_Master often shows

a valueof 0, even if the I/O thread is late compared to the master. In other words, this column isusefulonly forfast networks.

This timedifference computation works even if the master and slave do nothave identical clock times, provided that thedifference, computed when the slave I/O thread starts, remains constant from then

on. Any changes—including NTP updates—can lead to clock skews that can make calculation ofSeconds_Behind_Master less reliable.In MySQL 5.6.9 and later, this field is NULL (undefined or unknown) if the slave SQL thread is notrunning,or if the SQL thread has consumed all of the relay log and the slave I/O thread is notrunning.

Previously, this field wasNULL if the slave SQL thread or the slave I/O thread was not running orwasnot connected to the master. (Bug #12946333) For example, if (prior to MySQL 5.6.9) the slave I/O

thread was running but wasnot connected to the master and was sleeping for the number ofseconds

givenby the CHANGE MASTER TO statement or --master-connect-retry option (default 60) before

reconnecting, the value was NULL. Now in such cases, the connection to the master is nottested;

instead,if the I/O thread is running but the relay log is exhausted, Seconds_Behind_Master is set to

0.

The valueof Seconds_Behind_Master is based on the timestamps stored inevents, which are

preserved throughreplication. This means that if a master M1 is itself a slave of M0, any event from M1's

binary log that originates from M0's binary log has M0's timestamp for that event. This enables MySQL

to replicate TIMESTAMP successfully. However, the problem for Seconds_Behind_Master is that if

M1 also receives direct updates from clients, the Seconds_Behind_Master value randomly fluctuates

because sometimes the last event from M1 originates from M0 and sometimes is the result of a direct

update on M1.

When using a multithreaded slave, you should keep in mind that this value is based on

Exec_Master_Log_Pos, and so may not reflect the position of the most recently committed

transaction.

• Last_IO_Errno, Last_IO_Error

The error number and error message of the most recent error that caused the I/O thread to stop. An

error number of 0 and message of the empty string mean “no error.” If the Last_IO_Error value is not

empty, the error values also appear in the slave's error log.

I/O error information includes a timestamp showing when the most recent I/O thread error occurred. Thistimestamp uses the format YYMMDD HH:MM:SS, and appears inthe Last_IO_Error_Timestampcolumn.

Issuing RESET MASTERor RESET SLAVE resets the values shown inthese columns.

• Last_SQL_Errno, Last_SQL_Error

The errornumber and error message of the most recent error that caused the SQL thread tostop. An

errornumber of 0 and message of the empty string mean “no error.” If the Last_SQL_Error value is

not empty, the error values also appear in the slave s error log.

SQL error information includes a timestamp showing when the most recent SQL thread

error occurred. This timestamp uses the format YYMMDD HH:MM:SS, and appears in the

Last_SQL_Error_Timestamp column.

Issuing RESET MASTER or RESET SLAVE resets the values shown in these columns.

• Replicate_Ignore_Server_Ids

In MySQL 5.6, you set a slave to ignore events from 0 or more masters using the IGNORE_SERVER_IDS

option of the CHANGE MASTER TO statement. By default this is blank, and is usually modified

only when using a circular or other multi-master replication setup. The message shown for

Replicate_Ignore_Server_Ids when not blank consists of a comma-delimited list of one or more

numbers, indicating the server IDs to be ignored. For example:

Replicate_Ignore_Server_Ids: 2, 6, 9

Note

Ignored_server_ids also shows the server IDs to be ignored, but is a

space-delimited list, which is preceded by the total number of server IDs to

be ignored. For example, if a CHANGE MASTER TO statement containing the

IGNORE_SERVER_IDS = (2,6,9) option has been issued to tell a slave to

ignore masters having the server ID 2, 6, or 9, that information appears as:

Ignored_server_ids: 3 2 6 9

where 3 is the total number of server IDs being ignored

Replicate_Ignore_Server_Ids filtering is performed by the I/O thread, rather than by the SQL

thread, which means that events which are filtered out are not written to the relay log. This differs from

the filtering actions taken by server options such --replicate-do-table, which apply to the SQL

thread.

• Master_Server_Id

The server_id value from the master.

• Master_UUID

The server_uuid value from the master.

• Master_Info_File

The location of the master.info file.

• SQL_Delay

The number of seconds that the slave must lag the master.

• SQL_Remaining_Delay

When Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master

executed event, this field contains the number of delay seconds remaining. At other times, this field is

NULL.

• Slave_SQL_Running_State

The state of the SQL thread (analogous to Slave_IO_State). The value is identical to the State value

of the SQL thread as displayed by SHOW PROCESSLIST; Section 8.14.7, “Replication Slave SQL Thread

States”, provides a listing of possible states.

• Master_Retry_Count

The number of times the slave can attempt to reconnect to the master in the event of a lost connection.

This value can be set using the MASTER_RETRY_COUNT option of the CHANGE MASTER TO statement

(preferred) or the older --master-retry-count server option (still supported for backward

compatibility).

• Master_Bind

The network interface that the slave is bound to, if any. This is set using the MASTER_BIND option for the

CHANGE MASTER TO statement.

• Last_IO_Error_Timestamp

A timestamp in YYMMDD HH:MM:SS format that shows when the most recent I/O error took place.

• Last_SQL_Error_Timestamp

A timestamp in YYMMDD HH:MM:SS format that shows when the most recent SQL error occurred.

• Retrieved_Gtid_Set

The set of global transaction IDs corresponding to all transactions received by this slave. Empty if GTIDs

are not in use.

This is the set of all GTIDs that exist or have existed in the relay logs. Each GTID is added as soon as

the Gtid_log_event is received. This can cause partially transmitted transactions to have their GTIDs

included in the set.

When all relay logs are lost due to executing RESET SLAVE or CHANGE MASTER TO, or due to the

effects of the --relay-log-recovery option, the set is cleared. When relay_log_purge = 1, the

newest relay log is always kept, and the set is not cleared.

• Executed_Gtid_Set

The set of global transaction IDs written in the binary log. This is the same as the value for the global

gtid_executed system variable on this server, as well as the value for Executed_Gtid_Set in the

output of SHOW MASTER STATUS on this server. Empty if GTIDs are not in use. See GTID Sets for more

information.

• Auto_Position

1 if autopositioning is in use; otherwise 0.