MySQL 5.6.13基于MySQL-Proxy的讀寫分離

一、MySQL-Proxy簡介

MySQL-Proxy是處在MySQL資料庫客戶和服務端之間的一個中間件，支援嵌入性腳本語言lua，可以用來分析，監控和變換通信資料，支援使用的場景包括：負載均衡和故障轉移處理，查詢分析日志，查詢重寫等。

MySQL Proxy更強大的一項功能是實作“讀寫分離（Read/Write Splitting）”。基本的原理是讓主資料庫處理事務性查詢，而從資料庫處理SELECT查詢。資料庫複制被用來把事務性查詢導緻的變更同步到叢集中的從資料庫。

二、實驗環境

192.168.30.115 OS：CentOS 6.4 x86_64  mysql-proxy.luojianlong.com

192.168.30.116 OS：CentOS 6.4 x86_64  master.luojianlong.com

192.168.30.117 OS：CentOS 6.4 x86_64  slave.luojianlong.com

MySQL version：mysql-5.6.13-linux-glibc2.5-x86_64

MySQL-Proxy version：mysql-proxy-0.8.2-1.el6.x86_64

拓撲圖：

<a href="http://s3.51cto.com/wyfs02/M02/23/DB/wKiom1NFGcqiEMilAACz0U92j20079.jpg" target="_blank"></a>

由于，讀寫分離的是基于主從複制的，上一篇部落格已經介紹了mysql 5.6的主從複制，所有這裡就不介紹了，直接使用做好的主從架構。

首先，在mysql-proxy.luojianlong.com上面安裝mysql-proxy，使用epel的yum源來安裝

1

2

3

4

5

6

7

<code>[root@mysql-proxy ~]</code><code># cat /etc/yum.repos.d/epel.repo</code>

<code>[epel]</code>

<code>name=Extra Packages </code><code>for</code> <code>Enterprise Linux 6 - $basearch</code>

<code>baseurl=http:</code><code>//mirrors</code><code>.sohu.com</code><code>/fedora-epel/6/x86_64/</code>

<code>enabled=1</code>

<code>gpgcheck=0</code>

<code>[root@mysql-proxy ~]</code><code># yum -y install mysql-proxy</code>

修改服務腳本配置檔案/etc/sysconfig/mysql-proxy

<code># 修改為</code>

<code># Options for mysql-proxy</code>

<code>ADMIN_USER=</code><code>"admin"</code>

<code>ADMIN_PASSWORD=</code><code>"admin"</code>

<code>ADMIN_LUA_SCRIPT=</code><code>"/usr/lib64/mysql-proxy/lua/admin.lua"</code>

<code>PROXY_USER=</code><code>"mysql-proxy"</code>

<code>PROXY_OPTIONS=</code><code>"--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.30.116:3306 --proxy-read-only-backend-addresses=192.168.30.117:3306 --proxy-lua-script=/usr/lib64/mysql-proxy/lua/rw-splitting.lua"</code>

其中的proxy-backend-addresses選項和proxy-read-only-backend-addresses選項均可重複使用多次，以實作指定多個讀寫伺服器或隻讀伺服器。

mysql-proxy的配置選項：

--proxy-address=host:port：代理服務監聽的位址和端口；

--admin-address=host:port：管理子產品監聽的位址和端口；

--proxy-backend-addresses=host:port：後端mysql伺服器的位址和端口；

--proxy-read-only-backend-addresses=host:port：後端隻讀mysql伺服器的位址和端口；

--proxy-lua-script=file_name：完成mysql代理功能的Lua腳本；

--daemon：以守護程序模式啟動mysql-proxy；

--keepalive：在mysql-proxy崩潰時嘗試重新開機之；

--log-file=/path/to/log_file_name：日志檔案名稱；

--log-level=level：日志級别；

--log-use-syslog：基于syslog記錄日志；

--plugins=plugin：在mysql-proxy啟動時加載的插件；

--user=user_name：運作mysql-proxy程序的使用者；

--defaults-file=/path/to/conf_file_name：預設使用的配置檔案路徑；其配置段使用[mysql-proxy]辨別；

--proxy-skip-profiling：禁用profile；

--pid-file=/path/to/pid_file_name：程序檔案名；

這裡使用mysql-proxy-0.8.3提供的讀寫分離的lua腳本，将其複制儲存為/usr/lib64/mysql-proxy/lua/proxy/rw-splitting.lua，就可以啟動服務了

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

<code>[root@mysql-proxy ~]</code><code># vi /usr/lib64/mysql-proxy/lua/rw-splitting.lua</code>

<code>--[[ $%BEGINLICENSE%$</code>

<code> </code><code>Copyright (c) 2007, 2012, Oracle and</code><code>/or</code> <code>its affiliates. All rights reserved.</code>

<code> </code><code>This program is </code><code>free</code> <code>software; you can redistribute it and</code><code>/or</code>

<code> </code><code>modify it under the terms of the GNU General Public License as</code>

<code> </code><code>published by the Free Software Foundation; version 2 of the</code>

<code> </code><code>License.</code>

<code> </code><code>This program is distributed </code><code>in</code> <code>the hope that it will be useful,</code>

<code> </code><code>but WITHOUT ANY WARRANTY; without even the implied warranty of</code>

<code> </code><code>MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the</code>

<code> </code><code>GNU General Public License </code><code>for</code> <code>more</code> <code>details.</code>

<code> </code><code>You should have received a copy of the GNU General Public License</code>

<code> </code><code>along with this program; </code><code>if</code> <code>not, write to the Free Software</code>

<code> </code><code>Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA</code>

<code> </code><code>02110-1301  USA</code>

<code> </code><code>$%ENDLICENSE%$ --]]</code>

<code>---</code>

<code>-- a flexible statement based load balancer with connection pooling</code>

<code>--</code>

<code>-- * build a connection pool of min_idle_connections </code><code>for</code> <code>each backend and maintain</code>

<code>--   its size</code>

<code>-- *</code>

<code>local</code> <code>commands    = require(</code><code>"proxy.commands"</code><code>)</code>

<code>local</code> <code>tokenizer   = require(</code><code>"proxy.tokenizer"</code><code>)</code>

<code>local</code> <code>lb          = require(</code><code>"proxy.balance"</code><code>)</code>

<code>local</code> <code>auto_config = require(</code><code>"proxy.auto-config"</code><code>)</code>

<code>--- config</code>

<code>-- connection pool</code>

<code>if</code> <code>not proxy.global.config.rwsplit </code><code>then</code>

<code>    </code><code>proxy.global.config.rwsplit = {</code>

<code>        </code><code>min_idle_connections = 4,</code>

<code>        </code><code>max_idle_connections = 8,</code>

<code>        </code><code>is_debug = </code><code>false</code>

<code>    </code><code>}</code>

<code>end</code>

<code>-- </code><code>read</code><code>/write</code> <code>splitting sends all non-transactional SELECTs to the slaves</code>

<code>-- is_in_transaction tracks the state of the transactions</code>

<code>local</code> <code>is_in_transaction       = </code><code>false</code>

<code>-- </code><code>if</code> <code>this was a SELECT SQL_CALC_FOUND_ROWS ... stay on the same connections</code>

<code>local</code> <code>is_in_select_calc_found_rows = </code><code>false</code>

<code>-- get a connection to a backend</code>

<code>-- as long as we don't have enough connections </code><code>in</code> <code>the pool, create new connections</code>

<code>function</code> <code>connect_server()</code>

<code>    </code><code>local</code> <code>is_debug = proxy.global.config.rwsplit.is_debug</code>

<code>    </code><code>-- </code><code>make</code> <code>sure that we connect to each backend at least ones to</code>

<code>    </code><code>-- keep the connections to the servers alive</code>

<code>    </code><code>--</code>

<code>    </code><code>-- on read_query we can switch the backends again to another backend</code>

<code>    </code><code>if</code> <code>is_debug </code><code>then</code>

<code>        </code><code>print()</code>

<code>        </code><code>print(</code><code>"[connect_server] "</code> <code>.. proxy.connection.client.src.name)</code>

<code>    </code><code>end</code>

<code>    </code><code>local</code> <code>rw_ndx = 0</code>

<code>    </code><code>-- init all backends</code>

<code>    </code><code>for</code> <code>i = 1, </code><code>#proxy.global.backends do</code>

<code>        </code><code>local</code> <code>s        = proxy.global.backends[i]</code>

<code>        </code><code>local</code> <code>pool     = s.pool -- we don't have a username yet, try to </code><code>find</code> <code>a connections </code><code>which</code> <code>is idling</code>

<code>        </code><code>local</code> <code>cur_idle = pool.</code><code>users</code><code>[</code><code>""</code><code>].cur_idle_connections</code>

<code>        </code><code>pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections</code>

<code>        </code><code>pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections</code>

<code>                                                                                                                                </code> 

<code>        </code><code>if</code> <code>is_debug </code><code>then</code>

<code>            </code><code>print(</code><code>"  ["</code><code>.. i ..</code><code>"].connected_clients = "</code> <code>.. s.connected_clients)</code>

<code>            </code><code>print(</code><code>"  ["</code><code>.. i ..</code><code>"].pool.cur_idle     = "</code> <code>.. cur_idle)</code>

<code>            </code><code>print(</code><code>"  ["</code><code>.. i ..</code><code>"].pool.max_idle     = "</code> <code>.. pool.max_idle_connections)</code>

<code>            </code><code>print(</code><code>"  ["</code><code>.. i ..</code><code>"].pool.min_idle     = "</code> <code>.. pool.min_idle_connections)</code>

<code>            </code><code>print(</code><code>"  ["</code><code>.. i ..</code><code>"].type = "</code> <code>.. s.</code><code>type</code><code>)</code>

<code>            </code><code>print(</code><code>"  ["</code><code>.. i ..</code><code>"].state = "</code> <code>.. s.state)</code>

<code>        </code><code>end</code>

<code>        </code><code>-- prefer connections to the master</code>

<code>        </code><code>if</code> <code>s.</code><code>type</code> <code>== proxy.BACKEND_TYPE_RW and</code>

<code>           </code><code>s.state ~= proxy.BACKEND_STATE_DOWN and</code>

<code>           </code><code>cur_idle &lt; pool.min_idle_connections </code><code>then</code>

<code>            </code><code>proxy.connection.backend_ndx = i</code>

<code>            </code><code>break</code>

<code>        </code><code>elseif s.</code><code>type</code> <code>== proxy.BACKEND_TYPE_RO and</code>

<code>               </code><code>s.state ~= proxy.BACKEND_STATE_DOWN and</code>

<code>               </code><code>cur_idle &lt; pool.min_idle_connections </code><code>then</code>

<code>        </code><code>elseif s.</code><code>type</code> <code>== proxy.BACKEND_TYPE_RW and</code>

<code>               </code><code>rw_ndx == 0 </code><code>then</code>

<code>            </code><code>rw_ndx = i</code>

<code>    </code><code>if</code> <code>proxy.connection.backend_ndx == 0 </code><code>then</code>

<code>            </code><code>print(</code><code>"  ["</code> <code>.. rw_ndx .. </code><code>"] taking master as default"</code><code>)</code>

<code>        </code><code>proxy.connection.backend_ndx = rw_ndx</code>

<code>    </code><code>-- pick a random backend</code>

<code>    </code><code>-- we someone have to skip DOWN backends</code>

<code>    </code><code>-- ok, did we got a backend ?</code>

<code>    </code><code>if</code> <code>proxy.connection.server </code><code>then</code>

<code>            </code><code>print(</code><code>"  using pooled connection from: "</code> <code>.. proxy.connection.backend_ndx)</code>

<code>        </code><code>-- stay with it</code>

<code>        </code><code>return</code> <code>proxy.PROXY_IGNORE_RESULT</code>

<code>        </code><code>print(</code><code>"  ["</code> <code>.. proxy.connection.backend_ndx .. </code><code>"] idle-conns below min-idle"</code><code>)</code>

<code>    </code><code>-- </code><code>open</code> <code>a new connection</code>

<code>-- put the successfully authed connection into the connection pool</code>

<code>-- @param auth the context information </code><code>for</code> <code>the auth</code>

<code>-- auth.packet is the packet</code>

<code>function</code> <code>read_auth_result( auth )</code>

<code>        </code><code>print(</code><code>"[read_auth_result] "</code> <code>.. proxy.connection.client.src.name)</code>

<code>    </code><code>if</code> <code>auth.packet:byte() == proxy.MYSQLD_PACKET_OK </code><code>then</code>

<code>        </code><code>-- auth was fine, disconnect from the server</code>

<code>        </code><code>proxy.connection.backend_ndx = 0</code>

<code>    </code><code>elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF </code><code>then</code>

<code>        </code><code>-- we received either a</code>

<code>        </code><code>--</code>

<code>        </code><code>-- * MYSQLD_PACKET_ERR and the auth failed or</code>

<code>        </code><code>-- * MYSQLD_PACKET_EOF </code><code>which</code> <code>means a OLD PASSWORD (4.0) was sent</code>

<code>        </code><code>print(</code><code>"(read_auth_result) ... not ok yet"</code><code>);</code>

<code>    </code><code>elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR </code><code>then</code>

<code>        </code><code>-- auth failed</code>

<code>-- </code><code>read</code><code>/write</code> <code>splitting</code>

<code>function</code> <code>read_query( packet )</code>

<code>    </code><code>local</code> <code>cmd      = commands.parse(packet)</code>

<code>    </code><code>local</code> <code>c        = proxy.connection.client</code>

<code>    </code><code>local</code> <code>r = auto_config.handle(cmd)</code>

<code>    </code><code>if</code> <code>r </code><code>then</code> <code>return</code> <code>r end</code>

<code>    </code><code>local</code> <code>tokens</code>

<code>    </code><code>local</code> <code>norm_query</code>

<code>    </code><code>-- looks like we have to forward this statement to a backend</code>

<code>        </code><code>print(</code><code>"[read_query] "</code> <code>.. proxy.connection.client.src.name)</code>

<code>        </code><code>print(</code><code>"  current backend   = "</code> <code>.. proxy.connection.backend_ndx)</code>

<code>        </code><code>print(</code><code>"  client default db = "</code> <code>.. c.default_db)</code>

<code>        </code><code>print(</code><code>"  client username   = "</code> <code>.. c.username)</code>

<code>        </code><code>if</code> <code>cmd.</code><code>type</code> <code>== proxy.COM_QUERY </code><code>then</code>

<code>            </code><code>print(</code><code>"  query             = "</code>        <code>.. cmd.query)</code>

<code>    </code><code>if</code> <code>cmd.</code><code>type</code> <code>== proxy.COM_QUIT </code><code>then</code>

<code>        </code><code>-- don't send COM_QUIT to the backend. We manage the connection</code>

<code>        </code><code>-- </code><code>in</code> <code>all aspects.</code>

<code>        </code><code>proxy.response = {</code>

<code>            </code><code>type</code> <code>= proxy.MYSQLD_PACKET_OK,</code>

<code>        </code><code>}</code>

<code>                                                                                                                            </code> 

<code>            </code><code>print(</code><code>"  (QUIT) current backend   = "</code> <code>.. proxy.connection.backend_ndx)</code>

<code>        </code><code>return</code> <code>proxy.PROXY_SEND_RESULT</code>

<code>    </code><code>-- COM_BINLOG_DUMP packet can't be balanced</code>

<code>    </code><code>-- so we must send it always to the master</code>

<code>    </code><code>if</code> <code>cmd.</code><code>type</code> <code>== proxy.COM_BINLOG_DUMP </code><code>then</code>

<code>        </code><code>-- </code><code>if</code> <code>we don</code><code>'t have a backend selected, let'</code><code>s pick the master</code>

<code>        </code><code>if</code> <code>proxy.connection.backend_ndx == 0 </code><code>then</code>

<code>            </code><code>proxy.connection.backend_ndx = lb.idle_failsafe_rw()</code>

<code>        </code><code>return</code>

<code>    </code><code>proxy.queries:append(1, packet, { resultset_is_needed = </code><code>true</code> <code>})</code>

<code>    </code><code>-- </code><code>read</code><code>/write</code> <code>splitting</code>

<code>    </code><code>-- send all non-transactional SELECTs to a slave</code>

<code>    </code><code>if</code> <code>not is_in_transaction and</code>

<code>       </code><code>cmd.</code><code>type</code> <code>== proxy.COM_QUERY </code><code>then</code>

<code>        </code><code>tokens     = tokens or assert(tokenizer.tokenize(cmd.query))</code>

<code>        </code><code>local</code> <code>stmt = tokenizer.first_stmt_token(tokens)</code>

<code>        </code><code>if</code> <code>stmt.token_name == </code><code>"TK_SQL_SELECT"</code> <code>then</code>

<code>            </code><code>is_in_select_calc_found_rows = </code><code>false</code>

<code>            </code><code>local</code> <code>is_insert_id = </code><code>false</code>

<code>            </code><code>for</code> <code>i = 1, </code><code>#tokens do</code>

<code>                </code><code>local</code> <code>token = tokens[i]</code>

<code>                </code><code>-- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed</code>

<code>                </code><code>-- on the same connection</code>

<code>                </code><code>-- print(</code><code>"token: "</code> <code>.. token.token_name)</code>

<code>                </code><code>-- print(</code><code>"  val: "</code> <code>.. token.text)</code>

<code>                                                                                                                                        </code> 

<code>                </code><code>if</code> <code>not is_in_select_calc_found_rows and token.token_name == </code><code>"TK_SQL_SQL_CALC_FOUND_ROWS"</code> <code>then</code>

<code>                    </code><code>is_in_select_calc_found_rows = </code><code>true</code>

<code>                </code><code>elseif not is_insert_id and token.token_name == </code><code>"TK_LITERAL"</code> <code>then</code>

<code>                    </code><code>local</code> <code>utext = token.text:upper()</code>

<code>                    </code><code>if</code> <code>utext == </code><code>"LAST_INSERT_ID"</code> <code>or</code>

<code>                       </code><code>utext == </code><code>"@@INSERT_ID"</code> <code>then</code>

<code>                        </code><code>is_insert_id = </code><code>true</code>

<code>                    </code><code>end</code>

<code>                </code><code>end</code>

<code>                </code><code>-- we found the two special token, we can't </code><code>find</code> <code>more</code>

<code>                </code><code>if</code> <code>is_insert_id and is_in_select_calc_found_rows </code><code>then</code>

<code>                    </code><code>break</code>

<code>            </code><code>end</code>

<code>            </code><code>-- </code><code>if</code> <code>we ask </code><code>for</code> <code>the last-insert-</code><code>id</code> <code>we have to ask it on the original</code>

<code>            </code><code>-- connection</code>

<code>            </code><code>if</code> <code>not is_insert_id </code><code>then</code>

<code>                </code><code>local</code> <code>backend_ndx = lb.idle_ro()</code>

<code>                </code><code>if</code> <code>backend_ndx &gt; 0 </code><code>then</code>

<code>                    </code><code>proxy.connection.backend_ndx = backend_ndx</code>

<code>            </code><code>else</code>

<code>                </code><code>print(</code><code>"   found a SELECT LAST_INSERT_ID(), staying on the same backend"</code><code>)</code>

<code>    </code><code>-- no backend selected yet, pick a master</code>

<code>        </code><code>-- we don't have a backend right now</code>

<code>        </code><code>-- </code><code>let</code><code>'s pick a master as a good default</code>

<code>        </code><code>proxy.connection.backend_ndx = lb.idle_failsafe_rw()</code>

<code>    </code><code>-- by now we should have a backend</code>

<code>    </code><code>-- </code><code>in</code> <code>case</code> <code>the master is down, we have to close the client connections</code>

<code>    </code><code>-- otherwise we can go on</code>

<code>        </code><code>return</code> <code>proxy.PROXY_SEND_QUERY</code>

<code>    </code><code>local</code> <code>s = proxy.connection.server</code>

<code>    </code><code>-- </code><code>if</code> <code>client and server db don't match, adjust the server-side</code>

<code>    </code><code>-- skip it </code><code>if</code> <code>we send a INIT_DB anyway</code>

<code>    </code><code>if</code> <code>cmd.</code><code>type</code> <code>~= proxy.COM_INIT_DB and</code>

<code>       </code><code>c.default_db and c.default_db ~= s.default_db </code><code>then</code>

<code>        </code><code>print(</code><code>"    server default db: "</code> <code>.. s.default_db)</code>

<code>        </code><code>print(</code><code>"    client default db: "</code> <code>.. c.default_db)</code>

<code>        </code><code>print(</code><code>"    syncronizing"</code><code>)</code>

<code>        </code><code>proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = </code><code>true</code> <code>})</code>

<code>    </code><code>-- send to master</code>

<code>        </code><code>if</code> <code>proxy.connection.backend_ndx &gt; 0 </code><code>then</code>

<code>            </code><code>local</code> <code>b = proxy.global.backends[proxy.connection.backend_ndx]</code>

<code>            </code><code>print(</code><code>"  sending to backend : "</code> <code>.. b.dst.name);</code>

<code>            </code><code>print(</code><code>"    is_slave         : "</code> <code>.. tostring(b.</code><code>type</code> <code>== proxy.BACKEND_TYPE_RO));</code>

<code>            </code><code>print(</code><code>"    server default db: "</code> <code>.. s.default_db)</code>

<code>            </code><code>print(</code><code>"    server username  : "</code> <code>.. s.username)</code>

<code>        </code><code>print(</code><code>"    in_trans        : "</code> <code>.. tostring(is_in_transaction))</code>

<code>        </code><code>print(</code><code>"    in_calc_found   : "</code> <code>.. tostring(is_in_select_calc_found_rows))</code>

<code>        </code><code>print(</code><code>"    COM_QUERY       : "</code> <code>.. tostring(cmd.</code><code>type</code> <code>== proxy.COM_QUERY))</code>

<code>    </code><code>return</code> <code>proxy.PROXY_SEND_QUERY</code>

<code>-- as long as we are </code><code>in</code> <code>a transaction keep the connection</code>

<code>-- otherwise release it so another client can use it</code>

<code>function</code> <code>read_query_result( inj )</code>

<code>    </code><code>local</code> <code>res      = assert(inj.resultset)</code>

<code>    </code><code>local</code> <code>flags    = res.flags</code>

<code>    </code><code>if</code> <code>inj.</code><code>id</code> <code>~= 1 </code><code>then</code>

<code>        </code><code>-- ignore the result of the USE &lt;default_db&gt;</code>

<code>        </code><code>-- the DB might not exist on the backend, what </code><code>do</code> <code>do</code> <code>?</code>

<code>        </code><code>if</code> <code>inj.</code><code>id</code> <code>== 2 </code><code>then</code>

<code>            </code><code>-- the injected INIT_DB failed as the slave doesn't have this DB</code>

<code>            </code><code>-- or doesn't have permissions to </code><code>read</code> <code>from it</code>

<code>            </code><code>if</code> <code>res.query_status == proxy.MYSQLD_PACKET_ERR </code><code>then</code>

<code>                </code><code>proxy.queries:reset()</code>

<code>                </code><code>proxy.response = {</code>

<code>                    </code><code>type</code> <code>= proxy.MYSQLD_PACKET_ERR,</code>

<code>                    </code><code>errmsg = </code><code>"can't change DB "</code><code>.. proxy.connection.client.default_db ..</code>

<code>                        </code><code>" to on slave "</code> <code>.. proxy.global.backends[proxy.connection.backend_ndx].dst.name</code>

<code>                </code><code>}</code>

<code>                </code><code>return</code> <code>proxy.PROXY_SEND_RESULT</code>

<code>    </code><code>is_in_transaction = flags.in_trans</code>

<code>    </code><code>local</code> <code>have_last_insert_id = (res.insert_id and (res.insert_id &gt; 0))</code>

<code>       </code><code>not is_in_select_calc_found_rows and</code>

<code>       </code><code>not have_last_insert_id </code><code>then</code>

<code>        </code><code>-- release the backend</code>

<code>    </code><code>elseif is_debug </code><code>then</code>

<code>        </code><code>print(</code><code>"(read_query_result) staying on the same backend"</code><code>)</code>

<code>        </code><code>print(</code><code>"    have_insert_id  : "</code> <code>.. tostring(have_last_insert_id))</code>

<code>-- close the connections </code><code>if</code> <code>we have enough connections </code><code>in</code> <code>the pool</code>

<code>-- @</code><code>return</code> <code>nil - close connection</code>

<code>--         IGNORE_RESULT - store connection </code><code>in</code> <code>the pool</code>

<code>function</code> <code>disconnect_client()</code>

<code>        </code><code>print(</code><code>"[disconnect_client] "</code> <code>.. proxy.connection.client.src.name)</code>

<code>    </code><code>-- </code><code>make</code> <code>sure we are disconnection from the connection</code>

<code>    </code><code>-- to move the connection into the pool</code>

<code>    </code><code>proxy.connection.backend_ndx = 0</code>

啟動mysql-proxy

<code>[root@mysql-proxy ~]</code><code># service mysql-proxy start</code>

<code>Starting mysql-proxy:                                      [  OK  ]</code>

<code>[root@mysql-proxy ~]</code><code># ss -anptl</code>

<code>State       Recv-Q Send-Q                                                               Local Address:Port                                                                 Peer Address:Port</code>

<code>LISTEN      0      128                                                                             :::111                                                                            :::*      </code><code>users</code><code>:((</code><code>"rpcbind"</code><code>,1627,11))</code>

<code>LISTEN      0      128                                                                              *:111                                                                             *:*      </code><code>users</code><code>:((</code><code>"rpcbind"</code><code>,1627,8))</code>

<code>LISTEN      0      128                                                                             :::22                                                                             :::*      </code><code>users</code><code>:((</code><code>"sshd"</code><code>,2079,4))</code>

<code>LISTEN      0      128                                                                              *:22                                                                              *:*      </code><code>users</code><code>:((</code><code>"sshd"</code><code>,2079,3))</code>

<code>LISTEN      0      128                                                                      127.0.0.1:631                                                                             *:*      </code><code>users</code><code>:((</code><code>"cupsd"</code><code>,1938,7))</code>

<code>LISTEN      0      128                                                                            ::1:631                                                                            :::*      </code><code>users</code><code>:((</code><code>"cupsd"</code><code>,1938,6))</code>

<code>LISTEN      0      128                                                                              *:47000                                                                           *:*      </code><code>users</code><code>:((</code><code>"rpc.statd"</code><code>,1837,9))</code>

<code>LISTEN      0      100                                                                            ::1:25                                                                             :::*      </code><code>users</code><code>:((</code><code>"master"</code><code>,2174,13))</code>

<code>LISTEN      0      100                                                                      127.0.0.1:25                                                                              *:*      </code><code>users</code><code>:((</code><code>"master"</code><code>,2174,12))</code>

<code>LISTEN      0      128                                                                             :::53369                                                                          :::*      </code><code>users</code><code>:((</code><code>"rpc.statd"</code><code>,1837,11))</code>

<code>LISTEN      0      128                                                                              *:4040                                                                            *:*      </code><code>users</code><code>:((</code><code>"mysql-proxy"</code><code>,19973,10))</code>

<code>LISTEN      0      128                                                                              *:4041                                                                            *:*      </code><code>users</code><code>:((</code><code>"mysql-proxy"</code><code>,19973,11))</code>

在mysql-proxy上登入

<code>[root@mysql-proxy ~]</code><code># mysql -u root -pmypass -h 192.168.30.115 -P4040</code>

<code>mysql&gt; create database testdb;</code>

<code>Query OK, 1 row affected (0.01 sec)</code>

<code>mysql&gt; use testdb</code>

<code>    </code><code>server default db:</code>

<code>    </code><code>client default db: testdb</code>

<code>    </code><code>syncronizing</code>

<code>Database changed</code>

<code>mysql&gt; create table testA(</code><code>id</code> <code>int);</code>

<code>Query OK, 0 rows affected (0.43 sec)</code>

<code># 分别在主從伺服器上使用tcpdump指令抓包</code>

<code>[root@master ~]</code><code># tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.116</code>

<code>tcpdump: verbose output suppressed, use -</code><code>v</code> <code>or -vv </code><code>for</code> <code>full protocol decode</code>

<code>listening on eth0, link-</code><code>type</code> <code>EN10MB (Ethernet), capture size 65535 bytes</code>

<code>[root@slave ~]</code><code># tcpdump -i eth0 -s0 -nn -A tcp dst port 3306 and dst host 192.168.30.117</code>

批量插入和查詢資料

<code>[root@mysql-proxy ~]</code><code># for i in {1..50}; do mysql -u root -pmypass -h 192.168.30.115 -P4040 -e 'use testdb; insert into testA values(5);'; done</code>

<code>18:47:09.733244 IP 192.168.30.115.33567 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>301:333, ack 155, win 131, options [nop,nop,TS val 21000991 ecr 23848994], length 32</code>

<code>E..Tqe@.@.</code>

<code>....s...t....[...Jb".....mh.....</code>

<code>[email protected].".....insert into testA values(5)</code>

<code>18:47:09.749683 IP 192.168.30.115.33570 &gt; 192.168.30.116.3306: Flags [.], ack 144, win 131, options [nop,nop,TS val 21001008 ecr 23848972], length 0</code>

<code>E..4..@.@..|...s...t."..r....u.h....&gt;Z.....</code>

<code>[email protected]..</code>

<code>18:47:09.775507 IP 192.168.30.117.38626 &gt; 192.168.30.116.3306: Flags [.], ack 5185, win 499, options [nop,nop,TS val 22264873 ecr 23849027], length 0</code>

<code>E..4..@[email protected].....$......</code>

<code>.S.).k.C</code>

<code>18:47:09.778730 IP 192.168.30.115.33572 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>269:301, ack 144, win 115, options [nop,nop,TS val 21001037 ecr 23848927], length 32</code>

<code>E..TA.@.@.:....s...t.$..+L.D\..n...s+0.....</code>

<code>[email protected]</code>

<code>.....J.....:c.P..</code>

<code>18:47:09.779117 IP 192.168.30.115.33572 &gt; 192.168.30.116.3306: Flags [.], ack 155, win 115, options [nop,nop,TS val 21001037 ecr 23849040], length 0</code>

<code>E..4A.@.@.:....s...t.$..+L.d\..y...ss......</code>

<code>[email protected]</code>

<code>18:47:09.783718 IP 192.168.30.115.33570 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>290:301, ack 144, win 131, options [nop,nop,TS val 21001040 ecr 23848972], length 11</code>

<code>E..?..@[email protected]."..r....u.h...........</code>

插入50條資料發現，隻有master伺服器抓到資料更新的包，slave沒有

下面模拟50次查詢請求，檢視效果

<code>18:55:32.032655 IP 192.168.30.115.33567 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>204:215, ack 100, win 131, options [nop,nop,TS val 21503291 ecr 24351256], length 11</code>

<code>E..?sf@.@.  ....s...t....[..{Jb-............</code>

<code>.H.;.s.......testdb</code>

<code>18:55:32.048136 IP 192.168.30.115.33572 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>183:215, ack 100, win 115, options [nop,nop,TS val 21503307 ecr 24351259], length 32</code>

<code>E..TC.@[email protected].$..+M..\..n...s.f.....</code>

<code>.H.K.s.......root....f</code>

<code>18:55:32.048802 IP 192.168.30.115.33572 &gt; 192.168.30.116.3306: Flags [.], ack 111, win 115, options [nop,nop,TS val 21503308 ecr 24351310], length 0</code>

<code>E..4C.@[email protected].$..+M..\..y...s.T.....</code>

<code>.H.L.s.N</code>

<code>18:55:32.051857 IP 192.168.30.115.33570 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>204:215, ack 100, win 131, options [nop,nop,TS val 21503310 ecr 24351274], length 11</code>

<code>E..?..@[email protected]."..r..9.u.].....E.....</code>

<code>.H.N.s.*.....testdb</code>

<code>18:55:32.056674 IP 192.168.30.115.33574 &gt; 192.168.30.116.3306: Flags [.], ack 100, win 115, options [nop,nop,TS val 21503314 ecr 24351277], length 0</code>

<code>E..4.n@[email protected].&amp;....M2..x....s.......</code>

<code>.H.R.s.-</code>

<code>18:55:32.071103 IP 192.168.30.115.33574 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>183:215, ack 100, win 115, options [nop,nop,TS val 21503329 ecr 24351277], length 32</code>

<code>E..T.o@[email protected].&amp;....M2..x....s.......</code>

<code>.H.a.s.-.....root...Z.5o....|........</code><code>#".</code>

<code>18:55:32.073535 IP 192.168.30.115.33567 &gt; 192.168.30.116.3306: Flags [.], ack 111, win 131, options [nop,nop,TS val 21503332 ecr 24351294], length 0</code>

<code>E..4sg@.@.  ....s...t....[...Jb-............</code>

<code>.H.d.s.&gt;</code>

<code>18:55:32.074669 IP 192.168.30.115.33576 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>204:215, ack 100, win 115, options [nop,nop,TS val 21503333 ecr 24351291], length 11</code>

<code>E..?..@[email protected].(...'..L=`M...s.......</code>

<code>.H.e.s.;.....testdb</code>

<code>18:55:32.075503 IP 192.168.30.115.33576 &gt; 192.168.30.116.3306: Flags [.], ack 111, win 115, options [nop,nop,TS val 21503334 ecr 24351336], length 0</code>

<code>E..4..@[email protected].(...'..L=`X...sB......</code>

<code>.H.f.s.h</code>

<code>18:55:32.090373 IP 192.168.30.115.33567 &gt; 192.168.30.116.3306: Flags [P.], </code><code>seq</code> <code>215:247, ack 111, win 131, options [nop,nop,TS val 21503349 ecr 24351294], length 32</code>

<code>E..Tsh@[email protected]....[...Jb-.....L......</code>

<code>.H.u.s.&gt;.....root..S....9r.=*........ct.</code>

<code>18:55:32.091299 IP 192.168.30.115.33567 &gt; 192.168.30.116.3306: Flags [.], ack 122, win 131, options [nop,nop,TS val 21503349 ecr 24351352], length 0</code>

<code>E..4si@.@.  ....s...t....[...Jb.......9.....</code>

<code>18:55:33.701885 IP 192.168.30.115.60055 &gt; 192.168.30.117.3306: Flags [P.], </code><code>seq</code> <code>3683:3720, ack 36062, win 501, options [nop,nop,TS val 21504886 ecr 22768728], length 37</code>

<code>E..Y3E@[email protected]....{.....UJ.....8.....</code>

<code>.H</code><code>#v.[lX!....select @@version_comment limit 1</code>

<code>18:55:33.702932 IP 192.168.30.115.60053 &gt; 192.168.30.117.3306: Flags [P.], </code><code>seq</code> <code>3693:3698, ack 36058, win 501, options [nop,nop,TS val 21504887 ecr 22768696], length 5</code>

<code>E..9..@.@._....s...u.....A...........{.....</code>

<code>.H</code><code>#w.[l8.....</code>

<code>18:55:33.703341 IP 192.168.30.115.60053 &gt; 192.168.30.117.3306: Flags [P.], </code><code>seq</code> <code>3698:3720, ack 36091, win 501, options [nop,nop,TS val 21504888 ecr 22768729], length 22</code>

<code>E..J..@.@._....s...u.....A.....7....\......</code>

<code>.H</code><code>#x.[lY.....SELECT DATABASE()</code>

<code>18:55:33.704708 IP 192.168.30.115.60049 &gt; 192.168.30.117.3306: Flags [P.], </code><code>seq</code> <code>3696:3720, ack 35492, win 501, options [nop,nop,TS val 21504889 ecr 22768711], length 24</code>

<code>E..L..@[email protected].....</code>

<code>.H</code><code>#y.[lG.....select * from testA</code>

<code>18:55:33.725988 IP 192.168.30.115.60057 &gt; 192.168.30.117.3306: Flags [.], ack 36161, win 501, options [nop,nop,TS val 21504911 ecr 22768712], length 0</code>

<code>E..4i.@[email protected].=........o......</code>

<code>.H</code><code>#..[lH</code>

<code>18:55:33.726996 IP 192.168.30.115.60051 &gt; 192.168.30.117.3306: Flags [.], ack 36161, win 501, options [nop,nop,TS val 21504912 ecr 22768714], length 0</code>

<code>E..4..@[email protected].............</code>

<code>.H</code><code>#..[lJ</code>

<code>18:55:33.740992 IP 192.168.30.115.60055 &gt; 192.168.30.117.3306: Flags [.], ack 36161, win 501, options [nop,nop,TS val 21504926 ecr 22768728], length 0</code>

<code>E..43F@[email protected]=...s...u....{.....U......_.....</code>

<code>.H</code><code>#..[lX</code>

<code>18:55:33.742949 IP 192.168.30.115.60053 &gt; 192.168.30.117.3306: Flags [.], ack 36161, win 501, options [nop,nop,TS val 21504928 ecr 22768730], length 0</code>

<code>E..4..@.@._....s...u.....A.....}...........</code>

<code>.H</code><code>#..[lZ</code>

<code>18:55:33.744968 IP 192.168.30.115.60049 &gt; 192.168.30.117.3306: Flags [.], ack 36161, win 501, options [nop,nop,TS val 21504930 ecr 22768731], length 0</code>

<code>E..4..@[email protected]...........</code>

<code>.H</code><code>#..[l[</code>

測試發現讀請求被平均配置設定到倆台伺服器

到此，MySQL 5.6.13基于MySQL-Proxy的讀寫分離配置完成

     本文轉自ljl_19880709 51CTO部落格，原文連結：http://blog.51cto.com/luojianlong/1393030，如需轉載請自行聯系原作者