show master/slave status求根溯源
show master/slave status分别是查看主数据库以及副数据库的状态，是一种能查看主从复制运行情况的方式。
这里仅仅讨论linux下的nysql5.7.13版本的执行情况
一、show master status
开始与show global status类似，都是分配一个线程去处理该连接的命令（图1）
图1 show master status命令处理流程
1.在sql_yacc.cc:yyparse中
、
（1）初始化内存
（2）初始化解析后命令选项 SQLCOM_SHOW_MASTER_STAT
（3）初始化为单查询（此处无用）
2.show master status命令处理流程
图2. show_master_status的处理流程
（1）在mysql_execute_command（图2#3）中
进入该选项分支
1 case SQLCOM_SHOW_MASTER_STAT: 2 { 3 /* Accept one of two privileges */ 4 if (check_global_access(thd, SUPER_ACL | REPL_CLIENT_ACL)) 5 goto error; 6 res = show_master_status(thd); 7 break; 8 }
（2）在show_master_status（图2#2）中
1）初始化发送格式格式
1 field_list.push_back(new Item_empty_string("File", FN_REFLEN)); 2 field_list.push_back(new Item_return_int("Position",20, 3 MYSQL_TYPE_LONGLONG)); 4 field_list.push_back(new Item_empty_string("Binlog_Do_DB",255)); 5 field_list.push_back(new Item_empty_string("Binlog_Ignore_DB",255)); 6 field_list.push_back(new Item_empty_string("Executed_Gtid_Set", 7 gtid_set_size)); 8 9 if (thd->send_result_metadata(&field_list, 10 Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
2）储存内容并发送
1 protocol->start_row(); 2 3 if (mysql_bin_log.is_open()) 4 { 5 LOG_INFO li; 6 mysql_bin_log.get_current_log(&li); 7 size_t dir_len = dirname_length(li.log_file_name); 8 protocol->store(li.log_file_name + dir_len, &my_charset_bin); 9 protocol->store((ulonglong) li.pos); 10 store(protocol, binlog_filter->get_do_db()); 11 store(protocol, binlog_filter->get_ignore_db()); 12 protocol->store(gtid_set_buffer, &my_charset_bin); 13 if (protocol->end_row()) 14 { 15 my_free(gtid_set_buffer); 16 DBUG_RETURN(true); 17 } 18 }
（3）在raw_get_current_log（图2#1）中
1 int MYSQL_BIN_LOG::get_current_log(LOG_INFO* linfo, bool need_lock_log/*true*/) 2 { 3 if (need_lock_log) 4 mysql_mutex_lock(&LOCK_log); 5 int ret = raw_get_current_log(linfo); 6 if (need_lock_log) 7 mysql_mutex_unlock(&LOCK_log); 8 return ret; 9 }
主要的处理函数就是raw_get_current_log
（4）在raw_get_current_log（图2#0）中
在binlog的那点事中的写位置定位有详细解释，这里不再赘述。
二、show slave status
图3 show slave status命令处理流程
1.在sql_yacc.cc:yyparse中
1）初始化信息
2）初始化信道
3）初始化执行命令类型
4）使用单查询（此处无用）
2.命令执行流程
图4 show slave status命令处理
（1）同样地在mysql_execute_command（图4#3）中
1 case SQLCOM_SHOW_MASTER_STAT: 2 { 3 /* Accept one of two privileges */ 4 if (check_global_access(thd, SUPER_ACL | REPL_CLIENT_ACL)) 5 goto error; 6 res = show_master_status(thd); 7 break; 8 }
（2）在show_master_status（图4#2）中
由于在解析中看到lex->mi.for_channel=false,为此进入此分支
1 if (!lex->mi.for_channel) 2 res= show_slave_status(thd);
（3）在show_master_status（图4#3）中
1）清空每一信道的内存
1 for (mi_map::iterator it= channel_map.begin(); it!=channel_map.end(); it++) 2 { 3 mi= it->second; 4 /* 5 The following statement is needed because, when mi->host[0]=0 6 we don't alloc memory for retried_gtid_set. However, we try 7 to free it at the end, causing a crash. To be on safeside, 8 we initialize it to NULL, so that my_free() takes care of it. 9 */ 10 io_gtid_set_buffer_array[idx]= NULL; 11 12 if (mi != NULL && mi->host[0]) 13 { 14 const Gtid_set* io_gtid_set= mi->rli->get_gtid_set(); 15 16 /* 17 @todo: a single memory allocation improves speed, 18 instead of doing it for each loop 19 */ 20 21 if ((io_gtid_set_size= 22 io_gtid_set->to_string(&io_gtid_set_buffer_array[idx])) < 0) 23 { 24 my_eof(thd); 25 my_free(sql_gtid_set_buffer); 26 27 for (uint i= 0; i < idx -1; i++) 28 { 29 my_free(io_gtid_set_buffer_array[i]); 30 } 31 my_free(io_gtid_set_buffer_array); 32 33 global_sid_lock->unlock(); 34 DBUG_RETURN(true); 35 } 36 else 37 max_io_gtid_set_size= max_io_gtid_set_size > io_gtid_set_size ? 38 max_io_gtid_set_size : io_gtid_set_size; 39 } 40 idx++; 41 }
2）在show_slave_status_metadata，申请内存
1 void show_slave_status_metadata(List<Item> &field_list, 2 int io_gtid_set_size, int sql_gtid_set_size) 3 { 4 5 field_list.push_back(new Item_empty_string("Slave_IO_State", 14)); 6 field_list.push_back(new Item_empty_string("Master_Host", 7 HOSTNAME_LENGTH+1)); 8 field_list.push_back(new Item_empty_string("Master_User", 9 USERNAME_LENGTH+1)); 10 field_list.push_back(new Item_return_int("Master_Port", 7,MYSQL_TYPE_LONG)); 11 field_list.push_back(new Item_return_int("Connect_Retry", 10, 12 MYSQL_TYPE_LONG)); 13 field_list.push_back(new Item_empty_string("Master_Log_File", FN_REFLEN)); 14 field_list.push_back(new Item_return_int("Read_Master_Log_Pos", 10, 15 MYSQL_TYPE_LONGLONG)); 16 field_list.push_back(new Item_empty_string("Relay_Log_File", FN_REFLEN)); 17 field_list.push_back(new Item_return_int("Relay_Log_Pos", 10, 18 MYSQL_TYPE_LONGLONG)); 19 field_list.push_back(new Item_empty_string("Relay_Master_Log_File", 20 FN_REFLEN)); 21 field_list.push_back(new Item_empty_string("Slave_IO_Running", 3)); 22 field_list.push_back(new Item_empty_string("Slave_SQL_Running", 3)); 23 field_list.push_back(new Item_empty_string("Replicate_Do_DB", 20)); 24 field_list.push_back(new Item_empty_string("Replicate_Ignore_DB", 20)); 25 field_list.push_back(new Item_empty_string("Replicate_Do_Table", 20)); 26 field_list.push_back(new Item_empty_string("Replicate_Ignore_Table", 23)); 27 field_list.push_back(new Item_empty_string("Replicate_Wild_Do_Table", 24)); 28 field_list.push_back(new Item_empty_string("Replicate_Wild_Ignore_Table", 29 28)); 30 field_list.push_back(new Item_return_int("Last_Errno", 4, MYSQL_TYPE_LONG)); 31 field_list.push_back(new Item_empty_string("Last_Error", 20)); 32 field_list.push_back(new Item_return_int("Skip_Counter", 10, 33 MYSQL_TYPE_LONG)); 34 field_list.push_back(new Item_return_int("Exec_Master_Log_Pos", 10, 35 MYSQL_TYPE_LONGLONG)); 36 field_list.push_back(new Item_return_int("Relay_Log_Space", 10, 37 MYSQL_TYPE_LONGLONG)); 38 field_list.push_back(new Item_empty_string("Until_Condition", 6)); 39 field_list.push_back(new Item_empty_string("Until_Log_File", FN_REFLEN)); 40 field_list.push_back(new Item_return_int("Until_Log_Pos", 10, 41 MYSQL_TYPE_LONGLONG)); 42 field_list.push_back(new Item_empty_string("Master_SSL_Allowed", 7)); 43 field_list.push_back(new Item_empty_string("Master_SSL_CA_File", FN_REFLEN)); 44 field_list.push_back(new Item_empty_string("Master_SSL_CA_Path", FN_REFLEN)); 45 field_list.push_back(new Item_empty_string("Master_SSL_Cert", FN_REFLEN)); 46 field_list.push_back(new Item_empty_string("Master_SSL_Cipher", FN_REFLEN)); 47 field_list.push_back(new Item_empty_string("Master_SSL_Key", FN_REFLEN)); 48 field_list.push_back(new Item_return_int("Seconds_Behind_Master", 10, 49 MYSQL_TYPE_LONGLONG)); 50 field_list.push_back(new Item_empty_string("Master_SSL_Verify_Server_Cert", 51 3)); 52 field_list.push_back(new Item_return_int("Last_IO_Errno", 4, MYSQL_TYPE_LONG)); 53 field_list.push_back(new Item_empty_string("Last_IO_Error", 20)); 54 field_list.push_back(new Item_return_int("Last_SQL_Errno", 4, MYSQL_TYPE_LONG)); 55 field_list.push_back(new Item_empty_string("Last_SQL_Error", 20)); 56 field_list.push_back(new Item_empty_string("Replicate_Ignore_Server_Ids", 57 FN_REFLEN)); 58 field_list.push_back(new Item_return_int("Master_Server_Id", sizeof(ulong), 59 MYSQL_TYPE_LONG)); 60 field_list.push_back(new Item_empty_string("Master_UUID", UUID_LENGTH)); 61 field_list.push_back(new Item_empty_string("Master_Info_File", 62 2 * FN_REFLEN)); 63 field_list.push_back(new Item_return_int("SQL_Delay", 10, MYSQL_TYPE_LONG)); 64 field_list.push_back(new Item_return_int("SQL_Remaining_Delay", 8, MYSQL_TYPE_LONG)); 65 field_list.push_back(new Item_empty_string("Slave_SQL_Running_State", 20)); 66 field_list.push_back(new Item_return_int("Master_Retry_Count", 10, 67 MYSQL_TYPE_LONGLONG)); 68 field_list.push_back(new Item_empty_string("Master_Bind", HOSTNAME_LENGTH+1)); 69 field_list.push_back(new Item_empty_string("Last_IO_Error_Timestamp", 20)); 70 field_list.push_back(new Item_empty_string("Last_SQL_Error_Timestamp", 20)); 71 field_list.push_back(new Item_empty_string("Master_SSL_Crl", FN_REFLEN)); 72 field_list.push_back(new Item_empty_string("Master_SSL_Crlpath", FN_REFLEN)); 73 field_list.push_back(new Item_empty_string("Retrieved_Gtid_Set", 74 io_gtid_set_size)); 75 field_list.push_back(new Item_empty_string("Executed_Gtid_Set", 76 sql_gtid_set_size)); 77 field_list.push_back(new Item_return_int("Auto_Position", sizeof(ulong), 78 MYSQL_TYPE_LONG)); 79 field_list.push_back(new Item_empty_string("Replicate_Rewrite_DB", 24)); 80 field_list.push_back(new Item_empty_string("Channel_Name", CHANNEL_NAME_LENGTH)); 81 field_list.push_back(new Item_empty_string("Master_TLS_Version", FN_REFLEN)); 82 83 }
3）初始化发送协议
1 if (thd->send_result_metadata(&field_list, 2 Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) 3 { 4 goto err; 5 }
4）向各个信道发送要查询的状态
1 idx=0; 2 for (mi_map::iterator it= channel_map.begin(); it!=channel_map.end(); it++) 3 { 4 mi= it->second; 5 6 if (mi != NULL && mi->host[0]) 7 { 8 if (show_slave_status_send_data(thd, mi, io_gtid_set_buffer_array[idx], 9 sql_gtid_set_buffer)) 10 goto err; 11 12 if (protocol->end_row()) 13 goto err; 14 } 15 idx++; 16 }
对于show_slave_status_send_data
1 bool show_slave_status_send_data(THD *thd, Master_info *mi, 2 char* io_gtid_set_buffer, 3 char* sql_gtid_set_buffer) 4 { 5 DBUG_ENTER("show_slave_status_send_data"); 6 7 Protocol *protocol = thd->get_protocol(); 8 char* slave_sql_running_state= NULL; 9 10 DBUG_PRINT("info",("host is set: '%s'", mi->host)); 11 12 protocol->start_row(); 13 14 /* 15 slave_running can be accessed without run_lock but not other 16 non-volatile members like mi->info_thd or rli->info_thd, for 17 them either info_thd_lock or run_lock hold is required. 18 */ 19 mysql_mutex_lock(&mi->info_thd_lock); 20 protocol->store(mi->info_thd ? mi->info_thd->get_proc_info() : "", 21 &my_charset_bin); 22 mysql_mutex_unlock(&mi->info_thd_lock); 23 24 mysql_mutex_lock(&mi->rli->info_thd_lock); 25 slave_sql_running_state= const_cast<char *>(mi->rli->info_thd ? mi->rli->info_thd->get_proc_info() : ""); 26 mysql_mutex_unlock(&mi->rli->info_thd_lock); 27 28 mysql_mutex_lock(&mi->data_lock); 29 mysql_mutex_lock(&mi->rli->data_lock); 30 mysql_mutex_lock(&mi->err_lock); 31 mysql_mutex_lock(&mi->rli->err_lock); 32 33 DEBUG_SYNC(thd, "wait_after_lock_active_mi_and_rli_data_lock_is_acquired"); 34 protocol->store(mi->host, &my_charset_bin); 35 protocol->store(mi->get_user(), &my_charset_bin); 36 protocol->store((uint32) mi->port); 37 protocol->store((uint32) mi->connect_retry); 38 protocol->store(mi->get_master_log_name(), &my_charset_bin); 39 protocol->store((ulonglong) mi->get_master_log_pos()); 40 protocol->store(mi->rli->get_group_relay_log_name() + 41 dirname_length(mi->rli->get_group_relay_log_name()), 42 &my_charset_bin); 43 protocol->store((ulonglong) mi->rli->get_group_relay_log_pos()); 44 protocol->store(mi->rli->get_group_master_log_name(), &my_charset_bin); 45 protocol->store(mi->slave_running == MYSQL_SLAVE_RUN_CONNECT ? 46 "Yes" : (mi->slave_running == MYSQL_SLAVE_RUN_NOT_CONNECT ? 47 "Connecting" : "No"), &my_charset_bin); 48 protocol->store(mi->rli->slave_running ? "Yes":"No", &my_charset_bin); 49 store(protocol, rpl_filter->get_do_db()); 50 store(protocol, rpl_filter->get_ignore_db()); 51 52 char buf[256]; 53 String tmp(buf, sizeof(buf), &my_charset_bin); 54 rpl_filter->get_do_table(&tmp); 55 protocol->store(&tmp); 56 rpl_filter->get_ignore_table(&tmp); 57 protocol->store(&tmp); 58 rpl_filter->get_wild_do_table(&tmp); 59 protocol->store(&tmp); 60 rpl_filter->get_wild_ignore_table(&tmp); 61 protocol->store(&tmp); 62 63 protocol->store(mi->rli->last_error().number); 64 protocol->store(mi->rli->last_error().message, &my_charset_bin); 65 protocol->store((uint32) mi->rli->slave_skip_counter); 66 protocol->store((ulonglong) mi->rli->get_group_master_log_pos()); 67 protocol->store((ulonglong) mi->rli->log_space_total); 68 69 70 const char *until_type= ""; 71 72 switch (mi->rli->until_condition) 73 { 74 case Relay_log_info::UNTIL_NONE: 75 until_type= "None"; 76 break; 77 case Relay_log_info::UNTIL_MASTER_POS: 78 until_type= "Master"; 79 break; 80 case Relay_log_info::UNTIL_RELAY_POS: 81 until_type= "Relay"; 82 break; 83 case Relay_log_info::UNTIL_SQL_BEFORE_GTIDS: 84 until_type= "SQL_BEFORE_GTIDS"; 85 break; 86 case Relay_log_info::UNTIL_SQL_AFTER_GTIDS: 87 until_type= "SQL_AFTER_GTIDS"; 88 break; 89 case Relay_log_info::UNTIL_SQL_VIEW_ID: 90 until_type= "SQL_VIEW_ID"; 91 break; 92 case Relay_log_info::UNTIL_SQL_AFTER_MTS_GAPS: 93 until_type= "SQL_AFTER_MTS_GAPS"; 94 case Relay_log_info::UNTIL_DONE: 95 until_type= "DONE"; 96 break; 97 default: 98 DBUG_ASSERT(0); 99 } 100 protocol->store(until_type, &my_charset_bin); 101 protocol->store(mi->rli->until_log_name, &my_charset_bin); 102 protocol->store((ulonglong) mi->rli->until_log_pos); 103 104 #ifdef HAVE_OPENSSL 105 protocol->store(mi->ssl? "Yes":"No", &my_charset_bin); 106 #else 107 protocol->store(mi->ssl? "Ignored":"No", &my_charset_bin); 108 #endif 109 protocol->store(mi->ssl_ca, &my_charset_bin); 110 protocol->store(mi->ssl_capath, &my_charset_bin); 111 protocol->store(mi->ssl_cert, &my_charset_bin); 112 protocol->store(mi->ssl_cipher, &my_charset_bin); 113 protocol->store(mi->ssl_key, &my_charset_bin); 114 115 /* 116 The pseudo code to compute Seconds_Behind_Master: 117 if (SQL thread is running) 118 { 119 if (SQL thread processed all the available relay log) 120 { 121 if (IO thread is running) 122 print 0; 123 else 124 print NULL; 125 } 126 else 127 compute Seconds_Behind_Master; 128 } 129 else 130 print NULL; 131 */ 132 133 if (mi->rli->slave_running) 134 { 135 /* 136 Check if SQL thread is at the end of relay log 137 Checking should be done using two conditions 138 condition1: compare the log positions and 139 condition2: compare the file names (to handle rotation case) 140 */ 141 if ((mi->get_master_log_pos() == mi->rli->get_group_master_log_pos()) && 142 (!strcmp(mi->get_master_log_name(), mi->rli->get_group_master_log_name()))) 143 { 144 if (mi->slave_running == MYSQL_SLAVE_RUN_CONNECT) 145 protocol->store(0LL); 146 else 147 protocol->store_null(); 148 } 149 else 150 { 151 long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp) 152 - mi->clock_diff_with_master); 153 /* 154 Apparently on some systems time_diff can be <0. Here are possible 155 reasons related to MySQL: 156 - the master is itself a slave of another master whose time is ahead. 157 - somebody used an explicit SET TIMESTAMP on the master. 158 Possible reason related to granularity-to-second of time functions 159 (nothing to do with MySQL), which can explain a value of -1: 160 assume the master's and slave's time are perfectly synchronized, and 161 that at slave's connection time, when the master's timestamp is read, 162 it is at the very end of second 1, and (a very short time later) when 163 the slave's timestamp is read it is at the very beginning of second 164 2. Then the recorded value for master is 1 and the recorded value for 165 slave is 2. At SHOW SLAVE STATUS time, assume that the difference 166 between timestamp of slave and rli->last_master_timestamp is 0 167 (i.e. they are in the same second), then we get 0-(2-1)=-1 as a result. 168 This confuses users, so we don't go below 0: hence the max(). 169 170 last_master_timestamp == 0 (an "impossible" timestamp 1970) is a 171 special marker to say "consider we have caught up". 172 */ 173 protocol->store((longlong)(mi->rli->last_master_timestamp ? 174 max(0L, time_diff) : 0)); 175 } 176 } 177 else 178 { 179 protocol->store_null(); 180 } 181 protocol->store(mi->ssl_verify_server_cert? "Yes":"No", &my_charset_bin); 182 183 // Last_IO_Errno 184 protocol->store(mi->last_error().number); 185 // Last_IO_Error 186 protocol->store(mi->last_error().message, &my_charset_bin); 187 // Last_SQL_Errno 188 protocol->store(mi->rli->last_error().number); 189 // Last_SQL_Error 190 protocol->store(mi->rli->last_error().message, &my_charset_bin); 191 // Replicate_Ignore_Server_Ids 192 { 193 char buff[FN_REFLEN]; 194 ulong i, cur_len; 195 for (i= 0, buff[0]= 0, cur_len= 0; 196 i < mi->ignore_server_ids->dynamic_ids.size(); i++) 197 { 198 ulong s_id, slen; 199 char sbuff[FN_REFLEN]; 200 s_id= mi->ignore_server_ids->dynamic_ids[i]; 201 slen= sprintf(sbuff, (i == 0 ? "%lu" : ", %lu"), s_id); 202 if (cur_len + slen + 4 > FN_REFLEN) 203 { 204 /* 205 break the loop whenever remained space could not fit 206 ellipses on the next cycle 207 */ 208 sprintf(buff + cur_len, "..."); 209 break; 210 } 211 cur_len += sprintf(buff + cur_len, "%s", sbuff); 212 } 213 protocol->store(buff, &my_charset_bin); 214 } 215 // Master_Server_id 216 protocol->store((uint32) mi->master_id); 217 protocol->store(mi->master_uuid, &my_charset_bin); 218 // Master_Info_File 219 protocol->store(mi->get_description_info(), &my_charset_bin); 220 // SQL_Delay 221 protocol->store((uint32) mi->rli->get_sql_delay()); 222 // SQL_Remaining_Delay 223 if (slave_sql_running_state == stage_sql_thd_waiting_until_delay.m_name) 224 { 225 time_t t= my_time(0), sql_delay_end= mi->rli->get_sql_delay_end(); 226 protocol->store((uint32)(t < sql_delay_end ? sql_delay_end - t : 0)); 227 } 228 else 229 protocol->store_null(); 230 // Slave_SQL_Running_State 231 protocol->store(slave_sql_running_state, &my_charset_bin); 232 // Master_Retry_Count 233 protocol->store((ulonglong) mi->retry_count); 234 // Master_Bind 235 protocol->store(mi->bind_addr, &my_charset_bin); 236 // Last_IO_Error_Timestamp 237 protocol->store(mi->last_error().timestamp, &my_charset_bin); 238 // Last_SQL_Error_Timestamp 239 protocol->store(mi->rli->last_error().timestamp, &my_charset_bin); 240 // Master_Ssl_Crl 241 protocol->store(mi->ssl_crl, &my_charset_bin); 242 // Master_Ssl_Crlpath 243 protocol->store(mi->ssl_crlpath, &my_charset_bin); 244 // Retrieved_Gtid_Set 245 protocol->store(io_gtid_set_buffer, &my_charset_bin); 246 // Executed_Gtid_Set 247 protocol->store(sql_gtid_set_buffer, &my_charset_bin); 248 // Auto_Position 249 protocol->store(mi->is_auto_position() ? 1 : 0); 250 // Replicate_Rewrite_DB 251 rpl_filter->get_rewrite_db(&tmp); 252 protocol->store(&tmp); 253 // channel_name 254 protocol->store(mi->get_channel(), &my_charset_bin); 255 // Master_TLS_Version 256 protocol->store(mi->tls_version, &my_charset_bin); 257 258 mysql_mutex_unlock(&mi->rli->err_lock); 259 mysql_mutex_unlock(&mi->err_lock); 260 mysql_mutex_unlock(&mi->rli->data_lock); 261 mysql_mutex_unlock(&mi->data_lock); 262 263 DBUG_RETURN(false); 264 }
在这个函数和show_slave_status_metadata中，我们可以看到每个状态取自那个函数，这样就可以弄明白每个状态的实时更新性
在这里我们关注的是Master_Log_File和 Read_Master_Log_Pos的状态。这是主从同步情况最主要的状态，但是这涉及到slave IO的运行情况，我们下次在slave IO说明
