show master/slave status求根溯源，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 }
View Code
（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))
View Code
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 }
View Code
（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 }
View Code
主要的處理函數就是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 }
View Code
（2）在show_master_status（圖4#2）中
由於在解析中看到lex->mi.for_channel=false,為此進入此分支
![]()
1 if (!lex->mi.for_channel)
2 res= show_slave_status(thd);
View Code
（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 }
View Code
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 }
View Code
3）初始化發送協議
![]()
1 if (thd->send_result_metadata(&field_list,
2 Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
3 {
4 goto err;
5 }
View Code
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 }
View Code
對於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 }
View Code
在這個函數和show_slave_status_metadata中，我們可以看到每個狀態取自那個函數，這樣就可以弄明白每個狀態的實時更新性
在這裡我們關注的是Master_Log_File和 Read_Master_Log_Pos的狀態。這是主從同步情況最主要的狀態，但是這涉及到slave IO的運行情況，我們下次在slave IO說明
