MySQL 5.7增强半同步测试

we've know the machenism of semi-synchronous replication in my previous article,let's do some test to learn more details about it.now i have three virtual machines in my test environment,one master and the other two slaves are replicated from master which based on GTID mode.the replication method is asynchronous at the moment.okay here we go.

1  1 1.Install the plugin of semi-synchronous replication on master.
2  2 root@localhost:mysql3306.sock [(none)]02:17:05>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
3  3 Query OK, 0 rows affected (0.11 sec)
4  4 
5  5 root@localhost:mysql3306.sock [(none)]02:29:53>SELECT PLUGIN_NAME, PLUGIN_STATUS
6  6     ->        FROM INFORMATION_SCHEMA.PLUGINS
7  7     ->        WHERE PLUGIN_NAME LIKE '%semi%';
8  8 +----------------------+---------------+
9  9 | PLUGIN_NAME          | PLUGIN_STATUS |
10 10 +----------------------+---------------+
11 11 | rpl_semi_sync_master | ACTIVE        |
12 12 +----------------------+---------------+
13 13 1 row in set (0.00 sec)
14 14 
15 15 root@localhost:mysql3306.sock [(none)]02:29:57>show plugins;
16 16 +----------------------------+----------+--------------------+--------------------+---------+
17 17 | Name                       | Status   | Type               | Library            | License |
18 18 +----------------------------+----------+--------------------+--------------------+---------+
19 19 | binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
20 20 | mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
21 21 | sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
22 22 | PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
23 23 | MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
24 24 | MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
25 25 | InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
26 26 | INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
27 27 | INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
28 28 | INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
29 29 | INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
30 30 | INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
31 31 | INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
32 32 | INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
33 33 | INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
34 34 | INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
35 35 | INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
36 36 | INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
37 37 | INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
38 38 | INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
39 39 | INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
40 40 | INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
41 41 | INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
42 42 | INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
43 43 | INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
44 44 | INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
45 45 | INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
46 46 | INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
47 47 | INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
48 48 | INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
49 49 | INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
50 50 | INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
51 51 | INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
52 52 | INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
53 53 | INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
54 54 | INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
55 55 | INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
56 56 | CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
57 57 | MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
58 58 | ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
59 59 | partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
60 60 | BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
61 61 | FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
62 62 | ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     |
63 63 | rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |  -- Here you'll see plugin "rpl_semi_sync_master" has been installed
64 64 +----------------------------+----------+--------------------+--------------------+---------+
65 65 45 rows in set (0.00 sec)
66 66 
67 67 root@localhost:mysql3306.sock [(none)]02:39:08>
68 68 
69 69 2.Install the plugin of semi-synchronous replication on slave 1.
70 70 root@localhost:mysql.sock [(none)]02:17:39>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
71 71 Query OK, 0 rows affected (0.06 sec)
72 72 
73 73 root@localhost:mysql.sock [(none)]02:30:32>SELECT PLUGIN_NAME, PLUGIN_STATUS
74 74     ->        FROM INFORMATION_SCHEMA.PLUGINS
75 75     ->        WHERE PLUGIN_NAME LIKE '%semi%';
76 76 +---------------------+---------------+
77 77 | PLUGIN_NAME         | PLUGIN_STATUS |
78 78 +---------------------+---------------+
79 79 | rpl_semi_sync_slave | ACTIVE        |
80 80 +---------------------+---------------+
81 81 1 row in set (0.00 sec)
82 82 
83 83 root@localhost:mysql.sock [(none)]02:31:05>
84 84     
85 85 3.Install the plugin of semi-synchronous replication on slave 2.
86 86 root@localhost:mysql3306.sock [(none)]02:31:59>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
87 87 Query OK, 0 rows affected (0.09 sec)
88 88 
89 89 root@localhost:mysql3306.sock [(none)]02:32:09>SELECT PLUGIN_NAME, PLUGIN_STATUS
90 90     ->        FROM INFORMATION_SCHEMA.PLUGINS
91 91     ->        WHERE PLUGIN_NAME LIKE '%semi%';
92 92 +---------------------+---------------+
93 93 | PLUGIN_NAME         | PLUGIN_STATUS |
94 94 +---------------------+---------------+
95 95 | rpl_semi_sync_slave | ACTIVE        |
96 96 +---------------------+---------------+
97 97 1 row in set (0.00 sec)
98 98 
99 99 root@localhost:mysql3306.sock [(none)]02:32:17>
100100 
101101 4.Enbale the semi-synchronous replication on master.
102102 root@localhost:mysql3306.sock [(none)]02:39:08>SET GLOBAL rpl_semi_sync_master_enabled=1;  -- The parameter can be changed at runtime.
103103 Query OK, 0 rows affected (0.00 sec)
104104 
105105 root@localhost:mysql3306.sock [(none)]02:41:47>show global status like 'rpl%';  -- We can see all the status about semi-synchronous replication here.
106106 +--------------------------------------------+-------+
107107 | Variable_name                              | Value |
108108 +--------------------------------------------+-------+
109109 | Rpl_semi_sync_master_clients               | 0     |
110110 | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
111111 | Rpl_semi_sync_master_net_wait_time         | 0     |
112112 | Rpl_semi_sync_master_net_waits             | 0     |
113113 | Rpl_semi_sync_master_no_times              | 0     |
114114 | Rpl_semi_sync_master_no_tx                 | 0     |
115115 | Rpl_semi_sync_master_status                | ON    |
116116 | Rpl_semi_sync_master_timefunc_failures     | 0     |
117117 | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
118118 | Rpl_semi_sync_master_tx_wait_time          | 0     |
119119 | Rpl_semi_sync_master_tx_waits              | 0     |
120120 | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
121121 | Rpl_semi_sync_master_wait_sessions         | 0     |
122122 | Rpl_semi_sync_master_yes_tx                | 0     |
123123 +--------------------------------------------+-------+
124124 14 rows in set (0.00 sec)
125125 
126126 5.Enbale the semi-synchronous replication on salve 1.
127127 root@localhost:mysql.sock [(none)]02:43:29>SET GLOBAL rpl_semi_sync_slave_enabled=1;
128128 Query OK, 0 rows affected (0.00 sec)
129129 
130130 root@localhost:mysql.sock [(none)]02:43:44>show global status like 'rpl%';
131131 +----------------------------+-------+
132132 | Variable_name              | Value |
133133 +----------------------------+-------+
134134 | Rpl_semi_sync_slave_status | OFF   |  -- It's still "OFF" now,'cause the sql IO_Thread should be restart.
135135 +----------------------------+-------+
136136 1 row in set (0.00 sec)
137137 
138138 root@localhost:mysql.sock [(none)]02:50:45>stop slave io_thread;
139139 Query OK, 0 rows affected (0.00 sec)
140140 
141141 root@localhost:mysql.sock [(none)]02:51:10>start slave io_thread;
142142 Query OK, 0 rows affected (0.00 sec)
143143 
144144 root@localhost:mysql.sock [(none)]02:51:19>show global status like 'rpl%';
145145 +----------------------------+-------+
146146 | Variable_name              | Value |
147147 +----------------------------+-------+
148148 | Rpl_semi_sync_slave_status | ON    |  -- Now,the value has become "ON"
149149 +----------------------------+-------+
150150 1 row in set (0.00 sec)
151151 
152152 root@localhost:mysql.sock [(none)]02:51:25>
153153 
154154 6.Enbale the semi-synchronous replication on salve 2.
155155 root@localhost:mysql3306.sock [(none)]02:33:13>SET GLOBAL rpl_semi_sync_slave_enabled=1;
156156 Query OK, 0 rows affected (0.00 sec)
157157 
158158 root@localhost:mysql3306.sock [(none)]02:44:54>show global status like 'rpl%';
159159 +----------------------------+-------+
160160 | Variable_name              | Value |
161161 +----------------------------+-------+
162162 | Rpl_semi_sync_slave_status | OFF   |
163163 +----------------------------+-------+
164164 1 row in set (0.01 sec)
165165 
166166 root@localhost:mysql3306.sock [(none)]02:45:09>stop slave io_thread;
167167 Query OK, 0 rows affected (0.00 sec)
168168 
169169 root@localhost:mysql3306.sock [(none)]02:51:33>start slave io_thread;
170170 Query OK, 0 rows affected (0.00 sec)
171171 
172172 root@localhost:mysql3306.sock [(none)]02:51:40>show global status like 'rpl%';
173173 +----------------------------+-------+
174174 | Variable_name              | Value |
175175 +----------------------------+-------+
176176 | Rpl_semi_sync_slave_status | ON    |
177177 +----------------------------+-------+
178178 1 row in set (0.00 sec)
179179 
180180 root@localhost:mysql3306.sock [(none)]02:51:49>
181181 
182182 7.check the status of master again.
183183 root@localhost:mysql3306.sock [(none)]02:42:17>show global status like 'rpl%';
184184 +--------------------------------------------+-------+
185185 | Variable_name                              | Value |
186186 +--------------------------------------------+-------+
187187 | Rpl_semi_sync_master_clients               | 2     |  -- This value of status will show how many slave in semi_synchronous mode you really have.
188188 | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
189189 | Rpl_semi_sync_master_net_wait_time         | 0     |
190190 | Rpl_semi_sync_master_net_waits             | 0     |
191191 | Rpl_semi_sync_master_no_times              | 0     |
192192 | Rpl_semi_sync_master_no_tx                 | 0     |
193193 | Rpl_semi_sync_master_status                | ON    |
194194 | Rpl_semi_sync_master_timefunc_failures     | 0     |
195195 | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
196196 | Rpl_semi_sync_master_tx_wait_time          | 0     |
197197 | Rpl_semi_sync_master_tx_waits              | 0     |
198198 | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
199199 | Rpl_semi_sync_master_wait_sessions         | 0     |
200200 | Rpl_semi_sync_master_yes_tx                | 0     |
201201 +--------------------------------------------+-------+
202202 14 rows in set (0.00 sec)
203203 
204204 root@localhost:mysql3306.sock [(none)]02:55:36>
205205 
206206 8.Let's do some tests with semi-synchronous replication,create a table and insert several records on master.
207207 root@localhost:mysql3306.sock [(none)]02:55:36>use zlm;
208208 Reading table information for completion of table and column names
209209 You can turn off this feature to get a quicker startup with -A
210210 
211211 Database changed
212212 root@localhost:mysql3306.sock [zlm]02:59:39>create table semi_sync_test(
213213     -> id int auto_increment,
214214     -> name char(15) not null default '',
215215     -> primary key(id)
216216     -> ) engine=innodb charset=utf8;
217217 Query OK, 0 rows affected (0.04 sec)
218218 
219219 root@localhost:mysql3306.sock [zlm]03:03:08>insert into semi_sync_test(name) values('aaa');
220220 Query OK, 1 row affected (0.00 sec)
221221 
222222 root@localhost:mysql3306.sock [zlm]03:03:28>show global status like 'rpl%';
223223 +--------------------------------------------+-------+
224224 | Variable_name                              | Value |
225225 +--------------------------------------------+-------+
226226 | Rpl_semi_sync_master_clients               | 2     |
227227 | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
228228 | Rpl_semi_sync_master_net_wait_time         | 0     |
229229 | Rpl_semi_sync_master_net_waits             | 4     |  -- The times of master waiting for slave replies(two slaves plus two transcations got 4).
230230 | Rpl_semi_sync_master_no_times              | 0     |
231231 | Rpl_semi_sync_master_no_tx                 | 0     |
232232 | Rpl_semi_sync_master_status                | ON    |
233233 | Rpl_semi_sync_master_timefunc_failures     | 0     |
234234 | Rpl_semi_sync_master_tx_avg_wait_time      | 3257  |  -- Total avertage time master has waited for transaction.
235235 | Rpl_semi_sync_master_tx_wait_time          | 6514  |  -- Total time master has waited for transaction.(two transactions plus 3257 got 6514).
236236 | Rpl_semi_sync_master_tx_waits              | 2     |  -- I generated two transactions just now,the figure here is "2"
237237 | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
238238 | Rpl_semi_sync_master_wait_sessions         | 0     |
239239 | Rpl_semi_sync_master_yes_tx                | 2     |  -- The two transactions were all acknowleged by the two slaves.
240240 +--------------------------------------------+-------+
241241 14 rows in set (0.00 sec)
242242 
243243 root@localhost:mysql3306.sock [zlm]03:03:40>
244244 
245245 here's some messages from error log:
246246 
247247 2018-06-10T12:41:47.265030Z 5 [Note] Semi-sync replication initialized for transactions.
248248 2018-06-10T12:41:47.265060Z 5 [Note] Semi-sync replication enabled on the master.
249249 2018-06-10T12:41:47.265349Z 0 [Note] Starting ack receiver thread  -- There's a individual thread called "ack receiver"
250250 2018-06-10T12:47:17.754330Z 5 [Note] Aborted connection 5 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)
251251 2018-06-10T12:51:19.658621Z 6 [Note] While initializing dump thread for slave with UUID <27af30ca-6800-11e8-ad7e-080027de0e0e>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(4).
252252 2018-06-10T12:51:19.658773Z 6 [Note] Start binlog_dump to master_thread_id(6) slave_server(1013306), pos(, 4)
253253 2018-06-10T12:51:19.658797Z 6 [Note] Start semi-sync binlog_dump to slave (server_id: 1013306), pos(, 4)
254254 2018-06-10T12:51:19.658989Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 1013306)
255255 2018-06-10T12:51:40.294491Z 7 [Note] While initializing dump thread for slave with UUID <e00ef9f5-6c4b-11e8-8389-080027de0e0e>, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread(3).
256256 2018-06-10T12:51:40.294611Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(1023306), pos(, 4)
257257 2018-06-10T12:51:40.294663Z 3 [Note] Stop asynchronous binlog_dump to slave (server_id: 1023306)
258258 2018-06-10T12:51:40.694773Z 7 [Note] Start semi-sync binlog_dump to slave (server_id: 1023306), pos(, 4)
259259 2018-06-10T13:08:41.071298Z 8 [Note] Aborted connection 8 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
260260 
261261 my parameter about semi-synchronous replication listed below:
262262 
263263 root@localhost:mysql3306.sock [zlm]03:22:51>show variables like 'rpl%';
264264 +-------------------------------------------+------------+
265265 | Variable_name                             | Value      |
266266 +-------------------------------------------+------------+
267267 | rpl_semi_sync_master_enabled              | ON         |
268268 | rpl_semi_sync_master_timeout              | 10000      |  -- 10s is the default value,it's the threshold when the master will turn into async mode.
269269 | rpl_semi_sync_master_trace_level          | 32         |  -- There're 4 different value of it such as 1,16,32(default),64 of debug level.
270270 | rpl_semi_sync_master_wait_for_slave_count | 1          |
271271 | rpl_semi_sync_master_wait_no_slave        | ON         |  -- "ON" is default values,the semi-sync will continue even if the slave remain is less than the value of                                                        value of "rpl_semi_sync_master_wait_for_slave_count".  
272272 | rpl_semi_sync_master_wait_point           | AFTER_SYNC |  -- This values is only permitted in 5.7 or above which can guarantee consistency in replication.
273273 | rpl_stop_slave_timeout                    | 31536000   |  -- This is the maxium and default value which means 1 year(it seems hardly to timeout^^).
274274 +-------------------------------------------+------------+
275275 7 rows in set (0.00 sec)
276276 
277277 root@localhost:mysql3306.sock [zlm]03:22:58>
278278 
279279 Now i'm gonna to test when the semi-sync mode will turned into async mode:
280280 
281281 1.set the parameter "rpl_semi_sync_master_wait_for_slave_count"=2 and "rpl_semi_sync_master_wait_no_slave"=OFF
282282 root@localhost:mysql3306.sock [zlm]03:44:01>set global rpl_semi_sync_master_wait_for_slave_count=2;
283283 Query OK, 0 rows affected (0.00 sec)
284284 
285285 root@localhost:mysql3306.sock [zlm]03:44:40>set global rpl_semi_sync_master_wait_no_slave=OFF;
286286 Query OK, 0 rows affected (0.00 sec)
287287 
288288 root@localhost:mysql3306.sock [zlm]03:44:58>show variables like 'rpl%';
289289 +-------------------------------------------+------------+
290290 | Variable_name                             | Value      |
291291 +-------------------------------------------+------------+
292292 | rpl_semi_sync_master_enabled              | ON         |
293293 | rpl_semi_sync_master_timeout              | 10000      |
294294 | rpl_semi_sync_master_trace_level          | 32         |
295295 | rpl_semi_sync_master_wait_for_slave_count | 2          |
296296 | rpl_semi_sync_master_wait_no_slave        | OFF        |
297297 | rpl_semi_sync_master_wait_point           | AFTER_SYNC |
298298 | rpl_stop_slave_timeout                    | 31536000   |
299299 +-------------------------------------------+------------+
300300 7 rows in set (0.00 sec)
301301 
302302 root@localhost:mysql3306.sock [zlm]03:45:06>
303303 
304304 2.turn off the io_thread in one of the slave such as slave 2 and check the status of semi-sync mode both slave and master
305305 slave 2:
306306 root@localhost:mysql3306.sock [(none)]03:46:49>stop slave io_thread;
307307 Query OK, 0 rows affected (0.00 sec)
308308 
309309 root@localhost:mysql3306.sock [(none)]03:47:15>show status like 'rpl%';
310310 +----------------------------+-------+
311311 | Variable_name              | Value |
312312 +----------------------------+-------+
313313 | Rpl_semi_sync_slave_status | OFF   |  -- As soo as the IO_Thread had been stopped,the semi-sync mode immediately turned into async mode.
314314 +----------------------------+-------+
315315 1 row in set (0.00 sec)
316316 
317317 root@localhost:mysql3306.sock [(none)]03:47:37>
318318 
319319 master:
320320 root@localhost:mysql3306.sock [zlm]03:47:50>show status like 'rpl_semi_sync_master_status';
321321 +-----------------------------+-------+
322322 | Variable_name               | Value |
323323 +-----------------------------+-------+
324324 | Rpl_semi_sync_master_status | OFF   |  -- The value of status changed follow the value on slave.
325325 +-----------------------------+-------+
326326 1 row in set (0.00 sec)
327327 
328328 root@localhost:mysql3306.sock [zlm]03:48:49>
329329 
330330 whicn also can be seen in the error log:
331331 2018-06-10T13:47:15.870173Z 0 [ERROR] mysqld: Got an error reading communication packets  -- Here occured an error.
332332 2018-06-10T13:47:58.552523Z 7 [Note] Stop semi-sync binlog_dump to slave (server_id: 1023306)
333333 2018-06-10T13:47:58.942023Z 7 [Note] Semi-sync replication switched OFF.  -- The semi-sync mode was OFF.
334334 2018-06-10T13:47:58.942052Z 7 [Note] Aborted connection 7 to db: 'unconnected' user: 'repl' host: 'zlm3' (failed on flush_net())  -- lost connection with slave 2.
335335 2018-06-10T13:53:49.983185Z 10 [Note] Aborted connection 10 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
336336 
337337 3.set the parameter "rpl_semi_sync_master_wait_no_slave"=ON on master again.
338338 root@localhost:mysql3306.sock [zlm]03:48:49>set global rpl_semi_sync_master_wait_no_slave=ON;
339339 Query OK, 0 rows affected (0.00 sec)
340340 
341341 root@localhost:mysql3306.sock [zlm]03:58:35>show status like 'rpl_semi_sync_master_status';
342342 +-----------------------------+-------+
343343 | Variable_name               | Value |
344344 +-----------------------------+-------+
345345 | Rpl_semi_sync_master_status | ON    |  -- After set "rpl_semi_sync_master_wait_no_slave"=ON,it turned back again with semi-sync mode.
346346 +-----------------------------+-------+
347347 1 row in set (0.00 sec)
348348 
349349 root@localhost:mysql3306.sock [zlm]03:58:54>
350350 
351351 slave 1:
352352 root@localhost:mysql.sock [(none)]03:59:58>show status like 'rpl_semi_sync_slave_status';
353353 +----------------------------+-------+
354354 | Variable_name              | Value |
355355 +----------------------------+-------+
356356 | Rpl_semi_sync_slave_status | ON    |  -- slave 1 still can be replicated from master using semi-sync mode.
357357 +----------------------------+-------+
358358 1 row in set (0.00 sec)
359359 
360360 root@localhost:mysql.sock [(none)]04:00:11>
361361 
362362 slave 2:
363363 root@localhost:mysql3306.sock [(none)]03:47:37>show status like 'rpl%';
364364 +----------------------------+-------+
365365 | Variable_name              | Value |
366366 +----------------------------+-------+
367367 | Rpl_semi_sync_slave_status | OFF   |  -- only if IO_Thread is stated,the value cannnot be changed.
368368 +----------------------------+-------+
369369 1 row in set (0.00 sec)
370370 
371371 root@localhost:mysql3306.sock [(none)]04:00:53>
372372 
373373 4.continue to insert into test table "semi_sync_test" wait until up to timeout happen.
374374 master:
375375 root@localhost:mysql3306.sock [zlm]03:58:35>show status like 'rpl_semi_sync_master_status';
376376 +-----------------------------+-------+
377377 | Variable_name               | Value |
378378 +-----------------------------+-------+
379379 | Rpl_semi_sync_master_status | ON    |
380380 +-----------------------------+-------+
381381 1 row in set (0.00 sec)
382382 
383383 root@localhost:mysql3306.sock [zlm]03:58:54>insert into semi_sync_test(name) values('bbb');
384384 Query OK, 1 row affected (10.01 sec)  -- Notice,the time used is "10.01" secsonds which is matched with the parameter "rpl_semi_sync_master_timeout"=10000.
385385 
386386 root@localhost:mysql3306.sock [zlm]04:07:00>show status like 'rpl_semi_sync_master_status';
387387 +-----------------------------+-------+
388388 | Variable_name               | Value |
389389 +-----------------------------+-------+
390390 | Rpl_semi_sync_master_status | OFF   |
391391 +-----------------------------+-------+
392392 1 row in set (0.00 sec)
393393 
394394 root@localhost:mysql3306.sock [zlm]04:07:12>
395395 
396396 The master become using async mode again on account of the limit time of parameter "rpl_semi_sync_master_timeout".
397397 
398398 error log of master:
399399 2018-06-10T13:53:49.983185Z 10 [Note] Aborted connection 10 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
400400 2018-06-10T14:03:54.321177Z 11 [Note] Aborted connection 11 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
401401 2018-06-10T14:07:00.367988Z 12 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000041, pos: 1002), semi-sync up to file mysql-bin.000041, position 732.
402402 2018-06-10T14:07:00.368027Z 12 [Note] Semi-sync replication switched OFF.  -- the error log also has told you about the change.
403403 
404404 slave 1:
405405 root@localhost:mysql.sock [(none)]04:00:11>show status like 'rpl_semi_sync_slave_status';
406406 +----------------------------+-------+
407407 | Variable_name              | Value |
408408 +----------------------------+-------+
409409 | Rpl_semi_sync_slave_status | ON    |  -- Even though the value is "ON" in slave 1,but it still cannot use semi-sync mode duo to master has changed.
410410 +----------------------------+-------+
411411 1 row in set (0.00 sec)
412412 
413413 root@localhost:mysql.sock [(none)]04:08:08>
414414 
415415 slave 2:
416416 root@localhost:mysql3306.sock [(none)]04:08:38>show status like 'rpl%';
417417 +----------------------------+-------+
418418 | Variable_name              | Value |
419419 +----------------------------+-------+
420420 | Rpl_semi_sync_slave_status | OFF   |  -- Slave won't have any change about the value.
421421 +----------------------------+-------+
422422 1 row in set (0.00 sec)
423423 
424424 root@localhost:mysql3306.sock [(none)]04:08:46>
425425 
426426 5.In the end,let's start the IO_Thread on slave 2 and see what will happen.
427427 slave1:
428428 root@localhost:mysql3306.sock [(none)]04:08:46>start slave;
429429 Query OK, 0 rows affected (0.00 sec)
430430 
431431 root@localhost:mysql3306.sock [(none)]04:17:12>show status like 'rpl%';
432432 +----------------------------+-------+
433433 | Variable_name              | Value |
434434 +----------------------------+-------+
435435 | Rpl_semi_sync_slave_status | ON    |
436436 +----------------------------+-------+
437437 1 row in set (0.00 sec)
438438 
439439 root@localhost:mysql3306.sock [(none)]04:17:16>
440440 
441441 slave2:
442442 +----------------------------+-------+
443443 | Variable_name              | Value |
444444 +----------------------------+-------+
445445 | Rpl_semi_sync_slave_status | ON    |
446446 +----------------------------+-------+
447447 1 row in set (0.00 sec)
448448 
449449 root@localhost:mysql.sock [(none)]04:17:27>
450450 
451451 master:
452452 root@localhost:mysql3306.sock [zlm]04:07:12>show status like 'rpl_semi_sync_master_status';
453453 +-----------------------------+-------+
454454 | Variable_name               | Value |
455455 +-----------------------------+-------+
456456 | Rpl_semi_sync_master_status | ON    |
457457 +-----------------------------+-------+
458458 1 row in set (0.00 sec)
459459 
460460 root@localhost:mysql3306.sock [zlm]04:17:34>
461461 
462462 master error log:
463463 2018-06-10T14:12:12.905142Z 12 [Note] Aborted connection 12 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
464464 2018-06-10T14:17:12.140168Z 13 [Note] Start binlog_dump to master_thread_id(13) slave_server(1023306), pos(, 4)
465465 2018-06-10T14:17:12.438034Z 13 [Note] Start semi-sync binlog_dump to slave (server_id: 1023306), pos(, 4)
466466 2018-06-10T14:17:12.447797Z 0 [Note] Semi-sync replication switched ON at (mysql-bin.000041, 1002)  -- became semi-sync mode eventually.