===想确认sql_thread线程是否应用完了io_thread接收到的了relay log，看 Master_Log_File=Relay_Master_Log_File ， Read_Master_Log_Pos=Exec_Master_Log_Pos
1Master_Log_File: mysql-bin.000004 #当前的slave已经读取了master的binlog文件--slave I/O thread 2Read_Master_Log_Pos: 46187589 #当前的slave读取的master binlog---mysql_binlog.000004 位置是46187589--slave I/O thread 3Relay_Log_File: relaylog.000008 #当前relay文件 4Relay_Log_Pos: 46187752 #当前relay文件位置的点relaylog.000008，位置是46187752 5Relay_Master_Log_File: mysql-bin.000004 #当前slave的sql线程应用到master的binlog的mysql-bin.000004，位置为下一行的 46187589--slave SQL thread 6Slave_IO_Running: Yes 7Slave_SQL_Running: Yes 8Skip_Counter: 0 9Exec_Master_Log_Pos: 46187589 #当前sql_thread线程执行到binlog的位置点，---slave SQL thread 10Relay_Log_Space: 46187918 #The total combined size of all existing relay log files. 所有存在的relay log文件的总大小 11Until_Condition: None
mysql 5.6 官方文档
(Master_Log_file, Read_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave I/O thread has read events from that log.
(Relay_Master_Log_File, Exec_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log.
(Relay_Log_File, Relay_Log_Pos): Coordinates in the slave relay log indicating how far the slave SQL thread has executed the relay log. These correspond to the preceding coordinates, but are
expressed in slave relay log coordinates rather than master binary log coordinates.
1SHOW SLAVE STATUS Syntax 2This statement provides status information on essential parameters of the slave threads. It requires either 3the SUPER or REPLICATION CLIENT privilege. 4If you issue this statement using the mysql client, you can use a \G statement terminator rather than a 5semicolon to obtain a more readable vertical layout: 6mysql> SHOW SLAVE STATUS\G 7*************************** 1. row *************************** 8Slave_IO_State: Waiting for master to send event 9Master_Host: localhost 10Master_User: root 11Master_Port: 13000 12Connect_Retry: 60 13Master_Log_File: master-bin.000002 14Read_Master_Log_Pos: 1307 15Relay_Log_File: slave-relay-bin.000003 16Relay_Log_Pos: 1508 17Relay_Master_Log_File: master-bin.000002 18Slave_IO_Running: Yes 19Slave_SQL_Running: Yes 20Replicate_Do_DB: 21Replicate_Ignore_DB: 22Replicate_Do_Table: 23Replicate_Ignore_Table: 24Replicate_Wild_Do_Table: 25Replicate_Wild_Ignore_Table: 26Last_Errno: 0 27Last_Error: 28Skip_Counter: 0 29Exec_Master_Log_Pos: 1307 30Relay_Log_Space: 1858 31Until_Condition: None 32Until_Log_File: 33Until_Log_Pos: 0 34Master_SSL_Allowed: No 35Master_SSL_CA_File: 36Master_SSL_CA_Path: 37Master_SSL_Cert: 38Master_SSL_Cipher: 39Master_SSL_Key: 40Seconds_Behind_Master: 0 41Master_SSL_Verify_Server_Cert: No 42Last_IO_Errno: 0 43Last_IO_Error: 44Last_SQL_Errno: 0 45Last_SQL_Error: 46Replicate_Ignore_Server_Ids: 47Master_Server_Id: 1 48Master_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562 49Master_Info_File: /var/mysqld.2/data/master.info 50SQL_Delay: 0 51SQL_Remaining_Delay: NULL 52Slave_SQL_Running_State: Reading event from the relay log 53Master_Retry_Count: 10 54Master_Bind: 55Last_IO_Error_Timestamp: 56Last_SQL_Error_Timestamp: 57Master_SSL_Crl: 58Master_SSL_Crlpath: 59Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5 60Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5 61Auto_Position: 1 62 63• Slave_IO_State 64A copy of the State field of the SHOW PROCESSLIST output for the slave I/O thread. This tells you whatthe thread is doing: trying to connect to the master, waiting for events from the master, reconnecting to 65the master, and so on. For a listing of possible states, see Section 8.14.6, “Replication Slave I/O ThreadStates”. 66• Master_Host 67The master host that the slave is connected to. 68• Master_User 69The user name of the account used to connect to the master. 70• Master_Port 71The port used to connect to the master. 72• Connect_Retry 73The number of seconds between connect retries (default 60). This can be set with the CHANGE MASTER 74TO statement. 75• Master_Log_File 76The name of the master binary log file from which the I/O thread is currently reading. 77• Read_Master_Log_Pos 78The position in the current master binary log file up to which the I/O thread has read. 79• Relay_Log_File 80The name of the relay log file from which the SQL thread is currently reading and executing. 81• Relay_Log_Pos 82The position in the current relay log file up to which the SQL thread has read and executed. 83• Relay_Master_Log_File 84The name of the master binary log file containing the most recent event executed by the SQL thread. 85• Slave_IO_Running 86Whether the I/O thread is started and has connected successfully to the master. Internally, the state of 87this thread is represented by one of the following three values: 88• MYSQL_SLAVE_NOT_RUN. The slave I/O thread is not running. For this state, 89Slave_IO_Running is No. 90• MYSQL_SLAVE_RUN_NOT_CONNECT. The slave I/O thread is running, but is not connected to a 91replication master. For this state, Slave_IO_Running is Connecting. 92• MYSQL_SLAVE_RUN_CONNECT. The slave I/O thread is running, and is connected to a 93replication master. For this state, Slave_IO_Running is Yes. 94The value of the Slave_running system status variable corresponds with this value. 95• Slave_SQL_Running 96Whether the SQL thread is started. 97• Replicate_Do_DB, Replicate_Ignore_DB 98The lists of databases that were specified with the --replicate-do-db and --replicate-ignoredb options, if any. 99• Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, 100Replicate_Wild_Ignore_Table 101The lists of tables that were specified with the --replicate-do-table, --replicate-ignoretable, --replicate-wild-do-table, and --replicate-wild-ignore-table options, if any. 102• Last_Errno, Last_Error 103These columns are aliases for Last_SQL_Errno and Last_SQL_Error. 104Issuing RESET MASTER or RESET SLAVE resets the values shown in these columns. 105Note 106When the slave SQL thread receives an error, it reports the error first, then stops the SQL thread. This means that there is a small window of time during which 107SHOW SLAVE STATUS shows a nonzero value for Last_SQL_Errno even though Slave_SQL_Running still displays Yes. 108• Skip_Counter 109The current value of the sql_slave_skip_counter system variable. See Section 13.4.2.4, “SET 110GLOBAL sql_slave_skip_counter Syntax”. 111• Exec_Master_Log_Pos 112The position in the current master binary log file to which the SQL thread has read and executed, 113marking the start of the next transaction or event to be processed. You can use this value with 114the CHANGE MASTER TO 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 115(Relay_Master_Log_File, Exec_Master_Log_Pos) in the master's binary log correspond to the coordinates given by (Relay_Log_File, Relay_Log_Pos) in the relay log. 116When using a multithreaded slave (by setting slave_parallel_workers to a nonzero value), the value in this column actually represents a “low-water” mark, before which no uncommitted transactions 117remain. Because the current implementation allows execution of transactions on different databases in a different order on the slave than on the master, this is not necessarily the position of the most recently 118executed transaction. 119• Relay_Log_Space 120The total combined size of all existing relay log files. 121• Until_Condition, Until_Log_File, Until_Log_Pos 122The values specified in the UNTIL clause of the START SLAVE statement. Until_Condition has these values: 123• None if no UNTIL clause was specified 124• Master if the slave is reading until a given position in the master's binary log 125• Relay if the slave is reading until a given position in its relay log 126• SQL_BEFORE_GTIDS if the slave SQL thread is processing transactions until it has reached the first 127transaction whose GTID is listed in the gtid_set. 128• SQL_AFTER_GTIDS if the slave threads are processing all transactions until the last transaction in the 129gtid_set has been processed by both threads. 130• SQL_AFTER_MTS_GAPS if a multithreaded slave's SQL threads are running until no more gaps are 131found in the relay log. 132Until_Log_File and Until_Log_Pos indicate the log file name and position that define the 133coordinates at which the SQL thread stops executing. 134For more information on UNTIL clauses, see Section 13.4.2.5, “START SLAVE Syntax”. 135• Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert, 136Master_SSL_Cipher, Master_SSL_CRL_File, Master_SSL_CRL_Path, Master_SSL_Key, 137Master_SSL_Verify_Server_Cert 138These fields show the SSL parameters used by the slave to connect to the master, if any. 139Master_SSL_Allowed has these values: 140• Yes if an SSL connection to the master is permitted 141• No if an SSL connection to the master is not permitted 142• Ignored if an SSL connection is permitted but the slave server does not have SSL support enabled 143The values of the other SSL-related fields correspond to the values of the MASTER_SSL_CA, 144MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_CIPHER, MASTER_SSL_CRL, 145MASTER_SSL_CRLPATH, MASTER_SSL_KEY, and MASTER_SSL_VERIFY_SERVER_CERT options to the 146CHANGE MASTER TO statement. See Section 13.4.2.1, “CHANGE MASTER TO Syntax”. 147• Seconds_Behind_Master 148This field is an indication of how “late” the slave is: 149• When the slave is actively processing updates, this field shows the difference between the current timestamp on the slave and the original timestamp logged on the master for the event currently being processed on the slave. 150• 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 and the slave I/O thread. If the network connection between master and slave is fast, the slave I/O thread is very 151close to the master, so this field is a good approximation of how late the slave SQL thread is compared 152to the master. If the network is slow, this is not a good approximation; the slave SQL thread may quite often be caught up with the slow-reading slave I/O thread, so Seconds_Behind_Master often shows 153a value of 0, even if the I/O thread is late compared to the master. In other words, this column is useful only for fast networks. 154This time difference computation works even if the master and slave do not have identical clock times, provided that the difference, computed when the slave I/O thread starts, remains constant from then 155on. Any changes—including NTP updates—can lead to clock skews that can make calculation of Seconds_Behind_Master less reliable. 156In MySQL 5.6.9 and later, this field is NULL (undefined or unknown) if the slave SQL thread is not 157running, or if the SQL thread has consumed all of the relay log and the slave I/O thread is not running. 158Previously, this field was NULL if the slave SQL thread or the slave I/O thread was not running or was 159not connected to the master. (Bug #12946333) For example, if (prior to MySQL 5.6.9) the slave I/O 160thread was running but was not connected to the master and was sleeping for the number of seconds 161given by the CHANGE MASTER TO statement or --master-connect-retry option (default 60) before 162reconnecting, the value was NULL. Now in such cases, the connection to the master is not tested; 163instead, if the I/O thread is running but the relay log is exhausted, Seconds_Behind_Master is set to 1640. 165The value of Seconds_Behind_Master is based on the timestamps stored in events, which are 166preserved through replication. This means that if a master M1 is itself a slave of M0, any event from M1's 167binary log that originates from M0's binary log has M0's timestamp for that event. This enables MySQL 168to replicate TIMESTAMP successfully. However, the problem for Seconds_Behind_Master is that if 169M1 also receives direct updates from clients, the Seconds_Behind_Master value randomly fluctuates 170because sometimes the last event from M1 originates from M0 and sometimes is the result of a direct 171update on M1. 172When using a multithreaded slave, you should keep in mind that this value is based on 173Exec_Master_Log_Pos, and so may not reflect the position of the most recently committed 174transaction. 175• Last_IO_Errno, Last_IO_Error 176The error number and error message of the most recent error that caused the I/O thread to stop. An 177error number of 0 and message of the empty string mean “no error.” If the Last_IO_Error value is not 178empty, the error values also appear in the slave's error log. 179I/O error information includes a timestamp showing when the most recent I/O thread error occurred. This 180timestamp uses the format YYMMDD HH:MM:SS, and appears in the Last_IO_Error_Timestamp 181column. 182Issuing RESET MASTER or RESET SLAVE resets the values shown in these columns. 183• Last_SQL_Errno, Last_SQL_Error 184The error number and error message of the most recent error that caused the SQL thread to stop. An 185error number of 0 and message of the empty string mean “no error.” If the Last_SQL_Error value is 186not empty, the error values also appear in the slave s error log. 187SQL error information includes a timestamp showing when the most recent SQL thread 188error occurred. This timestamp uses the format YYMMDD HH:MM:SS, and appears in the 189Last_SQL_Error_Timestamp column. 190Issuing RESET MASTER or RESET SLAVE resets the values shown in these columns. 191• Replicate_Ignore_Server_Ids 192In MySQL 5.6, you set a slave to ignore events from 0 or more masters using the IGNORE_SERVER_IDS 193option of the CHANGE MASTER TO statement. By default this is blank, and is usually modified 194only when using a circular or other multi-master replication setup. The message shown for 195Replicate_Ignore_Server_Ids when not blank consists of a comma-delimited list of one or more 196numbers, indicating the server IDs to be ignored. For example: 197Replicate_Ignore_Server_Ids: 2, 6, 9 198Note 199Ignored_server_ids also shows the server IDs to be ignored, but is a 200space-delimited list, which is preceded by the total number of server IDs to 201be ignored. For example, if a CHANGE MASTER TO statement containing the 202IGNORE_SERVER_IDS = (2,6,9) option has been issued to tell a slave to 203ignore masters having the server ID 2, 6, or 9, that information appears as: 204Ignored_server_ids: 3 2 6 9 205where 3 is the total number of server IDs being ignored 206Replicate_Ignore_Server_Ids filtering is performed by the I/O thread, rather than by the SQL 207thread, which means that events which are filtered out are not written to the relay log. This differs from 208the filtering actions taken by server options such --replicate-do-table, which apply to the SQL 209thread. 210• Master_Server_Id 211The server_id value from the master. 212• Master_UUID 213The server_uuid value from the master. 214• Master_Info_File 215The location of the master.info file. 216• SQL_Delay 217The number of seconds that the slave must lag the master. 218• SQL_Remaining_Delay 219When Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master 220executed event, this field contains the number of delay seconds remaining. At other times, this field is 221NULL. 222• Slave_SQL_Running_State 223The state of the SQL thread (analogous to Slave_IO_State). The value is identical to the State value 224of the SQL thread as displayed by SHOW PROCESSLIST; Section 8.14.7, “Replication Slave SQL Thread 225States”, provides a listing of possible states. 226• Master_Retry_Count 227The number of times the slave can attempt to reconnect to the master in the event of a lost connection. 228This value can be set using the MASTER_RETRY_COUNT option of the CHANGE MASTER TO statement 229(preferred) or the older --master-retry-count server option (still supported for backward 230compatibility). 231• Master_Bind 232The network interface that the slave is bound to, if any. This is set using the MASTER_BIND option for the 233CHANGE MASTER TO statement. 234• Last_IO_Error_Timestamp 235A timestamp in YYMMDD HH:MM:SS format that shows when the most recent I/O error took place. 236• Last_SQL_Error_Timestamp 237A timestamp in YYMMDD HH:MM:SS format that shows when the most recent SQL error occurred. 238• Retrieved_Gtid_Set 239The set of global transaction IDs corresponding to all transactions received by this slave. Empty if GTIDs 240are not in use. 241This is the set of all GTIDs that exist or have existed in the relay logs. Each GTID is added as soon as 242the Gtid_log_event is received. This can cause partially transmitted transactions to have their GTIDs 243included in the set. 244When all relay logs are lost due to executing RESET SLAVE or CHANGE MASTER TO, or due to the 245effects of the --relay-log-recovery option, the set is cleared. When relay_log_purge = 1, the 246newest relay log is always kept, and the set is not cleared. 247• Executed_Gtid_Set 248The set of global transaction IDs written in the binary log. This is the same as the value for the global 249gtid_executed system variable on this server, as well as the value for Executed_Gtid_Set in the 250output of SHOW MASTER STATUS on this server. Empty if GTIDs are not in use. See GTID Sets for more 251information. 252• Auto_Position 2531 if autopositioning is in use; otherwise 0.