MySQL 5.7增强半同步测试

Wesley13
• 阅读 752

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.Install the plugin of semi-synchronous replication on master.
  2 root@localhost:mysql3306.sock [(none)]02:17:05>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
  3 Query OK, 0 rows affected (0.11 sec)
  4 
  5 root@localhost:mysql3306.sock [(none)]02:29:53>SELECT PLUGIN_NAME, PLUGIN_STATUS
  6     ->        FROM INFORMATION_SCHEMA.PLUGINS
  7     ->        WHERE PLUGIN_NAME LIKE '%semi%';
  8 +----------------------+---------------+
  9 | PLUGIN_NAME          | PLUGIN_STATUS |
 10 +----------------------+---------------+
 11 | rpl_semi_sync_master | ACTIVE        |
 12 +----------------------+---------------+
 13 1 row in set (0.00 sec)
 14 
 15 root@localhost:mysql3306.sock [(none)]02:29:57>show plugins;
 16 +----------------------------+----------+--------------------+--------------------+---------+
 17 | Name                       | Status   | Type               | Library            | License |
 18 +----------------------------+----------+--------------------+--------------------+---------+
 19 | binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 20 | mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
 21 | sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
 22 | PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 23 | MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 24 | MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 25 | InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 26 | INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 27 | INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 28 | INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 29 | INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 30 | INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 31 | INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 32 | INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 33 | INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 34 | INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 35 | INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 36 | INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 37 | INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 38 | INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 39 | INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 40 | INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 41 | INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 42 | INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 43 | INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 44 | INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 45 | INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 46 | INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 47 | INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 48 | INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 49 | INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 50 | INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 51 | INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 52 | INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 53 | INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 54 | INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 55 | INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
 56 | CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 57 | MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 58 | ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 59 | partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 60 | BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
 61 | FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
 62 | ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     |
 63 | rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |  -- Here you'll see plugin "rpl_semi_sync_master" has been installed
 64 +----------------------------+----------+--------------------+--------------------+---------+
 65 45 rows in set (0.00 sec)
 66 
 67 root@localhost:mysql3306.sock [(none)]02:39:08>
 68 
 69 2.Install the plugin of semi-synchronous replication on slave 1.
 70 root@localhost:mysql.sock [(none)]02:17:39>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
 71 Query OK, 0 rows affected (0.06 sec)
 72 
 73 root@localhost:mysql.sock [(none)]02:30:32>SELECT PLUGIN_NAME, PLUGIN_STATUS
 74     ->        FROM INFORMATION_SCHEMA.PLUGINS
 75     ->        WHERE PLUGIN_NAME LIKE '%semi%';
 76 +---------------------+---------------+
 77 | PLUGIN_NAME         | PLUGIN_STATUS |
 78 +---------------------+---------------+
 79 | rpl_semi_sync_slave | ACTIVE        |
 80 +---------------------+---------------+
 81 1 row in set (0.00 sec)
 82 
 83 root@localhost:mysql.sock [(none)]02:31:05>
 84     
 85 3.Install the plugin of semi-synchronous replication on slave 2.
 86 root@localhost:mysql3306.sock [(none)]02:31:59>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
 87 Query OK, 0 rows affected (0.09 sec)
 88 
 89 root@localhost:mysql3306.sock [(none)]02:32:09>SELECT PLUGIN_NAME, PLUGIN_STATUS
 90     ->        FROM INFORMATION_SCHEMA.PLUGINS
 91     ->        WHERE PLUGIN_NAME LIKE '%semi%';
 92 +---------------------+---------------+
 93 | PLUGIN_NAME         | PLUGIN_STATUS |
 94 +---------------------+---------------+
 95 | rpl_semi_sync_slave | ACTIVE        |
 96 +---------------------+---------------+
 97 1 row in set (0.00 sec)
 98 
 99 root@localhost:mysql3306.sock [(none)]02:32:17>
100 
101 4.Enbale the semi-synchronous replication on master.
102 root@localhost:mysql3306.sock [(none)]02:39:08>SET GLOBAL rpl_semi_sync_master_enabled=1;  -- The parameter can be changed at runtime.
103 Query OK, 0 rows affected (0.00 sec)
104 
105 root@localhost:mysql3306.sock [(none)]02:41:47>show global status like 'rpl%';  -- We can see all the status about semi-synchronous replication here.
106 +--------------------------------------------+-------+
107 | Variable_name                              | Value |
108 +--------------------------------------------+-------+
109 | Rpl_semi_sync_master_clients               | 0     |
110 | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
111 | Rpl_semi_sync_master_net_wait_time         | 0     |
112 | Rpl_semi_sync_master_net_waits             | 0     |
113 | Rpl_semi_sync_master_no_times              | 0     |
114 | Rpl_semi_sync_master_no_tx                 | 0     |
115 | Rpl_semi_sync_master_status                | ON    |
116 | Rpl_semi_sync_master_timefunc_failures     | 0     |
117 | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
118 | Rpl_semi_sync_master_tx_wait_time          | 0     |
119 | Rpl_semi_sync_master_tx_waits              | 0     |
120 | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
121 | Rpl_semi_sync_master_wait_sessions         | 0     |
122 | Rpl_semi_sync_master_yes_tx                | 0     |
123 +--------------------------------------------+-------+
124 14 rows in set (0.00 sec)
125 
126 5.Enbale the semi-synchronous replication on salve 1.
127 root@localhost:mysql.sock [(none)]02:43:29>SET GLOBAL rpl_semi_sync_slave_enabled=1;
128 Query OK, 0 rows affected (0.00 sec)
129 
130 root@localhost:mysql.sock [(none)]02:43:44>show global status like 'rpl%';
131 +----------------------------+-------+
132 | Variable_name              | Value |
133 +----------------------------+-------+
134 | Rpl_semi_sync_slave_status | OFF   |  -- It's still "OFF" now,'cause the sql IO_Thread should be restart.
135 +----------------------------+-------+
136 1 row in set (0.00 sec)
137 
138 root@localhost:mysql.sock [(none)]02:50:45>stop slave io_thread;
139 Query OK, 0 rows affected (0.00 sec)
140 
141 root@localhost:mysql.sock [(none)]02:51:10>start slave io_thread;
142 Query OK, 0 rows affected (0.00 sec)
143 
144 root@localhost:mysql.sock [(none)]02:51:19>show global status like 'rpl%';
145 +----------------------------+-------+
146 | Variable_name              | Value |
147 +----------------------------+-------+
148 | Rpl_semi_sync_slave_status | ON    |  -- Now,the value has become "ON"
149 +----------------------------+-------+
150 1 row in set (0.00 sec)
151 
152 root@localhost:mysql.sock [(none)]02:51:25>
153 
154 6.Enbale the semi-synchronous replication on salve 2.
155 root@localhost:mysql3306.sock [(none)]02:33:13>SET GLOBAL rpl_semi_sync_slave_enabled=1;
156 Query OK, 0 rows affected (0.00 sec)
157 
158 root@localhost:mysql3306.sock [(none)]02:44:54>show global status like 'rpl%';
159 +----------------------------+-------+
160 | Variable_name              | Value |
161 +----------------------------+-------+
162 | Rpl_semi_sync_slave_status | OFF   |
163 +----------------------------+-------+
164 1 row in set (0.01 sec)
165 
166 root@localhost:mysql3306.sock [(none)]02:45:09>stop slave io_thread;
167 Query OK, 0 rows affected (0.00 sec)
168 
169 root@localhost:mysql3306.sock [(none)]02:51:33>start slave io_thread;
170 Query OK, 0 rows affected (0.00 sec)
171 
172 root@localhost:mysql3306.sock [(none)]02:51:40>show global status like 'rpl%';
173 +----------------------------+-------+
174 | Variable_name              | Value |
175 +----------------------------+-------+
176 | Rpl_semi_sync_slave_status | ON    |
177 +----------------------------+-------+
178 1 row in set (0.00 sec)
179 
180 root@localhost:mysql3306.sock [(none)]02:51:49>
181 
182 7.check the status of master again.
183 root@localhost:mysql3306.sock [(none)]02:42:17>show global status like 'rpl%';
184 +--------------------------------------------+-------+
185 | Variable_name                              | Value |
186 +--------------------------------------------+-------+
187 | Rpl_semi_sync_master_clients               | 2     |  -- This value of status will show how many slave in semi_synchronous mode you really have.
188 | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
189 | Rpl_semi_sync_master_net_wait_time         | 0     |
190 | Rpl_semi_sync_master_net_waits             | 0     |
191 | Rpl_semi_sync_master_no_times              | 0     |
192 | Rpl_semi_sync_master_no_tx                 | 0     |
193 | Rpl_semi_sync_master_status                | ON    |
194 | Rpl_semi_sync_master_timefunc_failures     | 0     |
195 | Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
196 | Rpl_semi_sync_master_tx_wait_time          | 0     |
197 | Rpl_semi_sync_master_tx_waits              | 0     |
198 | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
199 | Rpl_semi_sync_master_wait_sessions         | 0     |
200 | Rpl_semi_sync_master_yes_tx                | 0     |
201 +--------------------------------------------+-------+
202 14 rows in set (0.00 sec)
203 
204 root@localhost:mysql3306.sock [(none)]02:55:36>
205 
206 8.Let's do some tests with semi-synchronous replication,create a table and insert several records on master.
207 root@localhost:mysql3306.sock [(none)]02:55:36>use zlm;
208 Reading table information for completion of table and column names
209 You can turn off this feature to get a quicker startup with -A
210 
211 Database changed
212 root@localhost:mysql3306.sock [zlm]02:59:39>create table semi_sync_test(
213     -> id int auto_increment,
214     -> name char(15) not null default '',
215     -> primary key(id)
216     -> ) engine=innodb charset=utf8;
217 Query OK, 0 rows affected (0.04 sec)
218 
219 root@localhost:mysql3306.sock [zlm]03:03:08>insert into semi_sync_test(name) values('aaa');
220 Query OK, 1 row affected (0.00 sec)
221 
222 root@localhost:mysql3306.sock [zlm]03:03:28>show global status like 'rpl%';
223 +--------------------------------------------+-------+
224 | Variable_name                              | Value |
225 +--------------------------------------------+-------+
226 | Rpl_semi_sync_master_clients               | 2     |
227 | Rpl_semi_sync_master_net_avg_wait_time     | 0     |
228 | Rpl_semi_sync_master_net_wait_time         | 0     |
229 | Rpl_semi_sync_master_net_waits             | 4     |  -- The times of master waiting for slave replies(two slaves plus two transcations got 4).
230 | Rpl_semi_sync_master_no_times              | 0     |
231 | Rpl_semi_sync_master_no_tx                 | 0     |
232 | Rpl_semi_sync_master_status                | ON    |
233 | Rpl_semi_sync_master_timefunc_failures     | 0     |
234 | Rpl_semi_sync_master_tx_avg_wait_time      | 3257  |  -- Total avertage time master has waited for transaction.
235 | Rpl_semi_sync_master_tx_wait_time          | 6514  |  -- Total time master has waited for transaction.(two transactions plus 3257 got 6514).
236 | Rpl_semi_sync_master_tx_waits              | 2     |  -- I generated two transactions just now,the figure here is "2"
237 | Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
238 | Rpl_semi_sync_master_wait_sessions         | 0     |
239 | Rpl_semi_sync_master_yes_tx                | 2     |  -- The two transactions were all acknowleged by the two slaves.
240 +--------------------------------------------+-------+
241 14 rows in set (0.00 sec)
242 
243 root@localhost:mysql3306.sock [zlm]03:03:40>
244 
245 here's some messages from error log:
246 
247 2018-06-10T12:41:47.265030Z 5 [Note] Semi-sync replication initialized for transactions.
248 2018-06-10T12:41:47.265060Z 5 [Note] Semi-sync replication enabled on the master.
249 2018-06-10T12:41:47.265349Z 0 [Note] Starting ack receiver thread  -- There's a individual thread called "ack receiver"
250 2018-06-10T12:47:17.754330Z 5 [Note] Aborted connection 5 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)
251 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).
252 2018-06-10T12:51:19.658773Z 6 [Note] Start binlog_dump to master_thread_id(6) slave_server(1013306), pos(, 4)
253 2018-06-10T12:51:19.658797Z 6 [Note] Start semi-sync binlog_dump to slave (server_id: 1013306), pos(, 4)
254 2018-06-10T12:51:19.658989Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 1013306)
255 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).
256 2018-06-10T12:51:40.294611Z 7 [Note] Start binlog_dump to master_thread_id(7) slave_server(1023306), pos(, 4)
257 2018-06-10T12:51:40.294663Z 3 [Note] Stop asynchronous binlog_dump to slave (server_id: 1023306)
258 2018-06-10T12:51:40.694773Z 7 [Note] Start semi-sync binlog_dump to slave (server_id: 1023306), pos(, 4)
259 2018-06-10T13:08:41.071298Z 8 [Note] Aborted connection 8 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
260 
261 my parameter about semi-synchronous replication listed below:
262 
263 root@localhost:mysql3306.sock [zlm]03:22:51>show variables like 'rpl%';
264 +-------------------------------------------+------------+
265 | Variable_name                             | Value      |
266 +-------------------------------------------+------------+
267 | rpl_semi_sync_master_enabled              | ON         |
268 | rpl_semi_sync_master_timeout              | 10000      |  -- 10s is the default value,it's the threshold when the master will turn into async mode.
269 | rpl_semi_sync_master_trace_level          | 32         |  -- There're 4 different value of it such as 1,16,32(default),64 of debug level.
270 | rpl_semi_sync_master_wait_for_slave_count | 1          |
271 | 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".  
272 | rpl_semi_sync_master_wait_point           | AFTER_SYNC |  -- This values is only permitted in 5.7 or above which can guarantee consistency in replication.
273 | rpl_stop_slave_timeout                    | 31536000   |  -- This is the maxium and default value which means 1 year(it seems hardly to timeout^^).
274 +-------------------------------------------+------------+
275 7 rows in set (0.00 sec)
276 
277 root@localhost:mysql3306.sock [zlm]03:22:58>
278 
279 Now i'm gonna to test when the semi-sync mode will turned into async mode:
280 
281 1.set the parameter "rpl_semi_sync_master_wait_for_slave_count"=2 and "rpl_semi_sync_master_wait_no_slave"=OFF
282 root@localhost:mysql3306.sock [zlm]03:44:01>set global rpl_semi_sync_master_wait_for_slave_count=2;
283 Query OK, 0 rows affected (0.00 sec)
284 
285 root@localhost:mysql3306.sock [zlm]03:44:40>set global rpl_semi_sync_master_wait_no_slave=OFF;
286 Query OK, 0 rows affected (0.00 sec)
287 
288 root@localhost:mysql3306.sock [zlm]03:44:58>show variables like 'rpl%';
289 +-------------------------------------------+------------+
290 | Variable_name                             | Value      |
291 +-------------------------------------------+------------+
292 | rpl_semi_sync_master_enabled              | ON         |
293 | rpl_semi_sync_master_timeout              | 10000      |
294 | rpl_semi_sync_master_trace_level          | 32         |
295 | rpl_semi_sync_master_wait_for_slave_count | 2          |
296 | rpl_semi_sync_master_wait_no_slave        | OFF        |
297 | rpl_semi_sync_master_wait_point           | AFTER_SYNC |
298 | rpl_stop_slave_timeout                    | 31536000   |
299 +-------------------------------------------+------------+
300 7 rows in set (0.00 sec)
301 
302 root@localhost:mysql3306.sock [zlm]03:45:06>
303 
304 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
305 slave 2:
306 root@localhost:mysql3306.sock [(none)]03:46:49>stop slave io_thread;
307 Query OK, 0 rows affected (0.00 sec)
308 
309 root@localhost:mysql3306.sock [(none)]03:47:15>show status like 'rpl%';
310 +----------------------------+-------+
311 | Variable_name              | Value |
312 +----------------------------+-------+
313 | Rpl_semi_sync_slave_status | OFF   |  -- As soo as the IO_Thread had been stopped,the semi-sync mode immediately turned into async mode.
314 +----------------------------+-------+
315 1 row in set (0.00 sec)
316 
317 root@localhost:mysql3306.sock [(none)]03:47:37>
318 
319 master:
320 root@localhost:mysql3306.sock [zlm]03:47:50>show status like 'rpl_semi_sync_master_status';
321 +-----------------------------+-------+
322 | Variable_name               | Value |
323 +-----------------------------+-------+
324 | Rpl_semi_sync_master_status | OFF   |  -- The value of status changed follow the value on slave.
325 +-----------------------------+-------+
326 1 row in set (0.00 sec)
327 
328 root@localhost:mysql3306.sock [zlm]03:48:49>
329 
330 whicn also can be seen in the error log:
331 2018-06-10T13:47:15.870173Z 0 [ERROR] mysqld: Got an error reading communication packets  -- Here occured an error.
332 2018-06-10T13:47:58.552523Z 7 [Note] Stop semi-sync binlog_dump to slave (server_id: 1023306)
333 2018-06-10T13:47:58.942023Z 7 [Note] Semi-sync replication switched OFF.  -- The semi-sync mode was OFF.
334 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.
335 2018-06-10T13:53:49.983185Z 10 [Note] Aborted connection 10 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
336 
337 3.set the parameter "rpl_semi_sync_master_wait_no_slave"=ON on master again.
338 root@localhost:mysql3306.sock [zlm]03:48:49>set global rpl_semi_sync_master_wait_no_slave=ON;
339 Query OK, 0 rows affected (0.00 sec)
340 
341 root@localhost:mysql3306.sock [zlm]03:58:35>show status like 'rpl_semi_sync_master_status';
342 +-----------------------------+-------+
343 | Variable_name               | Value |
344 +-----------------------------+-------+
345 | Rpl_semi_sync_master_status | ON    |  -- After set "rpl_semi_sync_master_wait_no_slave"=ON,it turned back again with semi-sync mode.
346 +-----------------------------+-------+
347 1 row in set (0.00 sec)
348 
349 root@localhost:mysql3306.sock [zlm]03:58:54>
350 
351 slave 1:
352 root@localhost:mysql.sock [(none)]03:59:58>show status like 'rpl_semi_sync_slave_status';
353 +----------------------------+-------+
354 | Variable_name              | Value |
355 +----------------------------+-------+
356 | Rpl_semi_sync_slave_status | ON    |  -- slave 1 still can be replicated from master using semi-sync mode.
357 +----------------------------+-------+
358 1 row in set (0.00 sec)
359 
360 root@localhost:mysql.sock [(none)]04:00:11>
361 
362 slave 2:
363 root@localhost:mysql3306.sock [(none)]03:47:37>show status like 'rpl%';
364 +----------------------------+-------+
365 | Variable_name              | Value |
366 +----------------------------+-------+
367 | Rpl_semi_sync_slave_status | OFF   |  -- only if IO_Thread is stated,the value cannnot be changed.
368 +----------------------------+-------+
369 1 row in set (0.00 sec)
370 
371 root@localhost:mysql3306.sock [(none)]04:00:53>
372 
373 4.continue to insert into test table "semi_sync_test" wait until up to timeout happen.
374 master:
375 root@localhost:mysql3306.sock [zlm]03:58:35>show status like 'rpl_semi_sync_master_status';
376 +-----------------------------+-------+
377 | Variable_name               | Value |
378 +-----------------------------+-------+
379 | Rpl_semi_sync_master_status | ON    |
380 +-----------------------------+-------+
381 1 row in set (0.00 sec)
382 
383 root@localhost:mysql3306.sock [zlm]03:58:54>insert into semi_sync_test(name) values('bbb');
384 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.
385 
386 root@localhost:mysql3306.sock [zlm]04:07:00>show status like 'rpl_semi_sync_master_status';
387 +-----------------------------+-------+
388 | Variable_name               | Value |
389 +-----------------------------+-------+
390 | Rpl_semi_sync_master_status | OFF   |
391 +-----------------------------+-------+
392 1 row in set (0.00 sec)
393 
394 root@localhost:mysql3306.sock [zlm]04:07:12>
395 
396 The master become using async mode again on account of the limit time of parameter "rpl_semi_sync_master_timeout".
397 
398 error log of master:
399 2018-06-10T13:53:49.983185Z 10 [Note] Aborted connection 10 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
400 2018-06-10T14:03:54.321177Z 11 [Note] Aborted connection 11 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
401 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.
402 2018-06-10T14:07:00.368027Z 12 [Note] Semi-sync replication switched OFF.  -- the error log also has told you about the change.
403 
404 slave 1:
405 root@localhost:mysql.sock [(none)]04:00:11>show status like 'rpl_semi_sync_slave_status';
406 +----------------------------+-------+
407 | Variable_name              | Value |
408 +----------------------------+-------+
409 | 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.
410 +----------------------------+-------+
411 1 row in set (0.00 sec)
412 
413 root@localhost:mysql.sock [(none)]04:08:08>
414 
415 slave 2:
416 root@localhost:mysql3306.sock [(none)]04:08:38>show status like 'rpl%';
417 +----------------------------+-------+
418 | Variable_name              | Value |
419 +----------------------------+-------+
420 | Rpl_semi_sync_slave_status | OFF   |  -- Slave won't have any change about the value.
421 +----------------------------+-------+
422 1 row in set (0.00 sec)
423 
424 root@localhost:mysql3306.sock [(none)]04:08:46>
425 
426 5.In the end,let's start the IO_Thread on slave 2 and see what will happen.
427 slave1:
428 root@localhost:mysql3306.sock [(none)]04:08:46>start slave;
429 Query OK, 0 rows affected (0.00 sec)
430 
431 root@localhost:mysql3306.sock [(none)]04:17:12>show status like 'rpl%';
432 +----------------------------+-------+
433 | Variable_name              | Value |
434 +----------------------------+-------+
435 | Rpl_semi_sync_slave_status | ON    |
436 +----------------------------+-------+
437 1 row in set (0.00 sec)
438 
439 root@localhost:mysql3306.sock [(none)]04:17:16>
440 
441 slave2:
442 +----------------------------+-------+
443 | Variable_name              | Value |
444 +----------------------------+-------+
445 | Rpl_semi_sync_slave_status | ON    |
446 +----------------------------+-------+
447 1 row in set (0.00 sec)
448 
449 root@localhost:mysql.sock [(none)]04:17:27>
450 
451 master:
452 root@localhost:mysql3306.sock [zlm]04:07:12>show status like 'rpl_semi_sync_master_status';
453 +-----------------------------+-------+
454 | Variable_name               | Value |
455 +-----------------------------+-------+
456 | Rpl_semi_sync_master_status | ON    |
457 +-----------------------------+-------+
458 1 row in set (0.00 sec)
459 
460 root@localhost:mysql3306.sock [zlm]04:17:34>
461 
462 master error log:
463 2018-06-10T14:12:12.905142Z 12 [Note] Aborted connection 12 to db: 'zlm' user: 'root' host: 'localhost' (Got timeout reading communication packets)
464 2018-06-10T14:17:12.140168Z 13 [Note] Start binlog_dump to master_thread_id(13) slave_server(1023306), pos(, 4)
465 2018-06-10T14:17:12.438034Z 13 [Note] Start semi-sync binlog_dump to slave (server_id: 1023306), pos(, 4)
466 2018-06-10T14:17:12.447797Z 0 [Note] Semi-sync replication switched ON at (mysql-bin.000041, 1002)  -- became semi-sync mode eventually.

Summary:

  • there're sevaral restrictions when using semi-synchronous replication:
  • based on MySQL 5.5 or above version.
  • parameter "have_dynamic_loading" must be set as "yes".
  • replication must has been normally worked such as asynchronous mode.
  • merely compatible with default replication channel.
  • take care of the converting condition between semi-sync and async mode due to timeout.
  • in order to keep consistency of replication,increase the value of "rpl_semi_sync_master_timeout" close to an infinite value maybe makeshift.
mysql运维开发运维开发
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
4133
0
0
待兔 待兔
•
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
1073
0
0
Jacquelyn38 Jacquelyn38
•
3年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
2389
0
0
Wesley13 Wesley13
•
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
1319
0
0
Wesley13 Wesley13
•
3年前
00：Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork)，斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟，已经成为Web应用程序的首选开发语言。Java是简单易学，完全面向对象，安全可靠，与平台无关的编程语言。
1258
0
0
Stella981 Stella981
•
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
1060
0
0
Wesley13 Wesley13
•
3年前
thinkphp 基本配置
12returnarray(34//定义数据库连接信息5'DB\_TYPE''mysql',//指定数据库是mysql67'DB\_HOST''localhost',89'DB\_NAME''uchome',//数据库名1011'DB\_USER''root
846
0
0
Wesley13 Wesley13
•
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1524
0
0
京东云开发者 京东云开发者
•
1年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
1041
0
0
Python进阶者 Python进阶者
•
10个月前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
511
0
0
Wesley13
Wesley13
Lv1
最大的安心是自律温柔和爱自己。
文章
1.2w
粉丝
20
获赞
1.7k
热门文章
JavPlayer：AI破坏马赛克，大量马赛克破坏版影片流出
2020年最新可用的磁力搜索引擎&种子搜索网站，持续整理分享
8个在线接收手机短信验证码的免费网络服务整理
IDM 激活破解工具
java.net.ConnectException：Connection timed out：connect 出现连接超时的问题？