mysql 半同步测试_MySQL 5.7增强半同步测试

最新推荐文章于 2021-04-08 20:15:16 发布

weixin_39824801

最新推荐文章于 2021-04-08 20:15:16 发布

阅读量141

点赞数

文章标签： mysql 半同步测试

本文链接：https://blog.csdn.net/weixin_39824801/article/details/113721774

版权

本文详细介绍了在MySQL 5.7中安装和启用半同步复制插件的过程，包括在主库和从库上安装插件，并通过设置参数启用半同步模式。在测试中，当从库IO线程停止时，主库会自动切换到异步模式，当IO线程重新启动后，系统恢复半同步模式。通过调整超时参数，观察插入操作在达到超时时主库如何切换回异步模式。

摘要由CSDN通过智能技术生成

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.11sec)4

5 root@localhost:mysql3306.sock [(none)]02:29:53>SELECT PLUGIN_NAME, PLUGIN_STATUS6 ->FROM INFORMATION_SCHEMA.PLUGINS7 -> 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.00sec)14

15 root@localhost:mysql3306.sock [(none)]02:29:57>show plugins;16 +----------------------------+----------+--------------------+--------------------+---------+

18 +----------------------------+----------+--------------------+--------------------+---------+

56 | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |

64 +----------------------------+----------+--------------------+--------------------+---------+

65 45 rows in set (0.00sec)66

67 root@localhost:mysql3306.sock [(none)]02:39:08>

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.06sec)72

73 root@localhost:mysql.sock [(none)]02:30:32>SELECT PLUGIN_NAME, PLUGIN_STATUS74 ->FROM INFORMATION_SCHEMA.PLUGINS75 -> 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.00sec)82

83 root@localhost:mysql.sock [(none)]02:31:05>

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.09sec)88

89 root@localhost:mysql3306.sock [(none)]02:32:09>SELECT PLUGIN_NAME, PLUGIN_STATUS90 ->FROM INFORMATION_SCHEMA.PLUGINS91 -> 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.00sec)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.00sec)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.00sec)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.00sec)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.00sec)137

138 root@localhost:mysql.sock [(none)]02:50:45>stop slave io_thread;139 Query OK, 0 rows affected (0.00sec)140

141 root@localhost:mysql.sock [(none)]02:51:10>start slave io_thread;142 Query OK, 0 rows affected (0.00sec)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.00sec)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.00sec)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.01sec)165

166 root@localhost:mysql3306.sock [(none)]02:45:09>stop slave io_thread;167 Query OK, 0 rows affected (0.00sec)168

169 root@localhost:mysql3306.sock [(none)]02:51:33>start slave io_thread;170 Query OK, 0 rows affected (0.00sec)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.00sec)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 insemi_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.00sec)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 forcompletion of table and column names209 You can turn off this feature to get a quicker startup with -A210

211 Database changed212 root@localhost:mysql3306.sock [zlm]02:59:39>create table semi_sync_test(213 -> id intauto_increment,214 -> name char(15) not null default '',215 -> primary key(id)216 -> ) engine=innodb charset=utf8;217 Query OK, 0 rows affected (0.04sec)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.00sec)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 |

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.00sec)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 fortransactions.248 2018-06-10T12:41:47.265060Z 5 [Note] Semi-syncreplication 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 , 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 inreplication.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.00sec)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"=OFF282 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.00sec)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.00sec)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.00sec)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-syncmode both slave and master305 slave 2:306 root@localhost:mysql3306.sock [(none)]03:46:49>stop slave io_thread;307 Query OK, 0 rows affected (0.00sec)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-syncmode immediately turned into async mode.314 +----------------------------+-------+

315 1 row in set (0.00sec)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.00sec)327

328 root@localhost:mysql3306.sock [zlm]03:48:49>

329

330 whicn also can be seen inthe 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-syncmode 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.00sec)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-syncmode.346 +-----------------------------+-------+

347 1 row in set (0.00sec)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-syncmode.357 +----------------------------+-------+

358 1 row in set (0.00sec)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 ifIO_Thread is stated,the value cannnot be changed.368 +----------------------------+-------+

369 1 row in set (0.00sec)370

371 root@localhost:mysql3306.sock [(none)]04:00:53>

372

373 4.continue to insert into test table "semi_sync_test" wait untilup 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.00sec)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.00sec)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-syncmode duo to master has changed.410 +----------------------------+-------+

411 1 row in set (0.00sec)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.00sec)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.00sec)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.00sec)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.00sec)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.00sec)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.

weixin_39824801

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫