MySQL Proxy RW Splitting
=rw-splitting.lua=
==说明==
* MySQL Proxy 0.8.1版本的读写分离脚本
* 下载地址
mysql-proxy-0.8.1-linux-glibc2.3-x86-64bit.tar.gz
* 参考文档
** 读写分离脚本说明
MySQL Proxy RW Splitting: http://forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting
MySQL Proxy learns R/W Splitting: http://jan.kneschke.de/projects/mysql/mysql-proxy-learns-r-w-splitting
MySQL Proxy: more R/W splitting: http://jan.kneschke.de/projects/mysql/mysql-proxy-more-r-w-splitting/
** Lua开发
Lua 5.1 Reference Manual: http://www.lua.org/manual/5.1/manual.html
Programming in Lua (first edition): http://www.lua.org/pil/
** MySQL Proxy脚本开发
MySQL Proxy Scripting: http://dev.mysql.com/doc/refman/5.5/en/mysql-proxy-scripting.html
MySQL Proxy Snippet: http://forge.mysql.com/tools/search.php?t=tag&k=mysqlproxy
MySQL Proxy Dev: http://forge.mysql.com/wiki/MySQL_Proxy_Dev
Advanced LUA Scripting: http://forge.mysql.com/wiki/Advanced_LUA_Scripting
ProxyCookbook: http://forge.mysql.com/wiki/ProxyCookbook
http://blog.suncrescent.net/2008/05/partial-mysql-proxy-api-doc/
==代码==
===1-20行===
1 --[[ $%BEGINLICENSE%$
2 Copyright (c) 2007, 2009, Oracle and/or its affiliates. All rights reserved.
3
4 This program is free software; you can redistribute it and/or
5 modify it under the terms of the GNU General Public License as
6 published by the Free Software Foundation; version 2 of the
7 License.
8
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU General Public License for more details.
13
14 You should have received a copy of the GNU General Public License
15 along with this program; if not, write to the Free Software
16 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
17 02110-1301 USA
18
19 $%ENDLICENSE%$ --]]
20
* 声明版权信息
* lua脚本中 "--\[\[" 和 "--\]\]" 之间表示多行注释块
===21-29行===
21 ---
22 -- a flexible statement based load balancer with connection pooling
23 --
24 -- * build a connection pool of min_idle_connections for each backend and maintain
25 -- its size
26 -- *
27 --
28 --
29
* 脚本说明
* lua脚本中 "--" 之后表示单行注释信息
===30-34行===
30 local commands = require("proxy.commands")
31 local tokenizer = require("proxy.tokenizer")
32 local lb = require("proxy.balance")
33 local auto_config = require("proxy.auto-config")
34
* require加载MySQL Proxy模块
* 模块文件所在缺省目录是$LUA_HOME/lib/mysql-proxy/lua/proxy/
* 各模块说明如下：
** commands.lua：解析网络包中的MySQL命令
*** 参考 http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Command_Packet_.28Overview.29
** tokenizer.lua：加载模块mysql.tokenizer；格式化SQL语句。没看懂
** balance.lua：负载均衡
*** function idle_failsafe_rw：获取一个空闲的可读写的连接（可读写的主库或备库）
*** function idle_ro：获取一个空闲的只读的连接（只读备库）
** auto-config.lua：加载模块lpeg，即"Parsing Expression Grammars For Lua"；管理配置参数
*** 语法
**** proxy show config 显示配置
**** proxy set global xxx.yyy=zzz 设置配置
**** proxy save config into "xxx" 保存配置
**** proxy load config from "xxx" 加载配置
*** 参考
**** http://www.inf.puc-rio.br/~roberto/lpeg/
**** http://www.gammon.com.au/scripts/doc.php?general=lua_lpeg
*** 问题
**** [[#proxy set global 报错 ERROR 1064 (42000): You have an error in your SQL syntax]]
**** [[#proxy set global 报错 ERROR 1105 (07000): (lua) proxy.response.errmsg is nil]]
**** [[#proxy save config 保存配置文件大小等于0]]
**** [[#proxy save config 保存配置文件名带双引号]]
===35-46行===
35 --- config
36 --
37 -- connection pool
38 if not proxy.global.config.rwsplit then
39 proxy.global.config.rwsplit = {
40 min_idle_connections = 4,
41 max_idle_connections = 8,
42
43 is_debug = false
44 }
45 end
46
* 设置读写分离的缺省配置参数
===47-55行===
47 ---
48 -- read/write splitting sends all non-transactional SELECTs to the slaves
49 --
50 -- is_in_transaction tracks the state of the transactions
51 local is_in_transaction = false
52
53 -- if this was a SELECT SQL_CALC_FOUND_ROWS ... stay on the same connections
54 local is_in_select_calc_found_rows = false
55
* 初始化变量
* is_in_transaction：false表示转发查询语句到只读连接上
* is_in_select_calc_found_rows：表示查询语句中是否有"SQL_CALC_FOUND_ROWS"
===56-72行===
56 ---
57 -- get a connection to a backend
58 --
59 -- as long as we don't have enough connections in the pool, create new connections
60 --
61 function connect_server()
62 local is_debug = proxy.global.config.rwsplit.is_debug
63 -- make sure that we connect to each backend at least ones to
64 -- keep the connections to the servers alive
65 --
66 -- on read_query we can switch the backends again to another backend
67
68 if is_debug then
69 print()
70 print("[connect_server] " .. proxy.connection.client.src.name)
71 end
72
* 函数connect_server说明： 获取一个到后端的连接
* 初始化is_debug变量
===75-121行===
73 local rw_ndx = 0
74
75 -- init all backends
76 for i = 1, #proxy.global.backends do
77 local s = proxy.global.backends[i]
78 local pool = s.pool -- we don't have a username yet, try to find a connections which is idling
79 local cur_idle = pool.users[""].cur_idle_connections
80
81 pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections
82 pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections
83
84 if is_debug then
85 print(" [".. i .."].connected_clients = " .. s.connected_clients)
86 print(" [".. i .."].pool.cur_idle = " .. cur_idle)
87 print(" [".. i .."].pool.max_idle = " .. pool.max_idle_connections)
88 print(" [".. i .."].pool.min_idle = " .. pool.min_idle_connections)
89 print(" [".. i .."].type = " .. s.type)
90 print(" [".. i .."].state = " .. s.state)
91 end
92
93 -- prefer connections to the master
94 if s.type == proxy.BACKEND_TYPE_RW and
95 s.state ~= proxy.BACKEND_STATE_DOWN and
96 cur_idle < pool.min_idle_connections then
97 proxy.connection.backend_ndx = i
98 break
99 elseif s.type == proxy.BACKEND_TYPE_RO and
100 s.state ~= proxy.BACKEND_STATE_DOWN and
101 cur_idle < pool.min_idle_connections then
102 proxy.connection.backend_ndx = i
103 break
104 elseif s.type == proxy.BACKEND_TYPE_RW and
105 s.state ~= proxy.BACKEND_STATE_DOWN and
106 rw_ndx == 0 then
107 rw_ndx = i
108 end
109 end
110
111 if proxy.connection.backend_ndx == 0 then
112 if is_debug then
113 print(" [" .. rw_ndx .. "] taking master as default")
114 end
115 proxy.connection.backend_ndx = rw_ndx
116 end
117
118 -- pick a random backend
119 --
120 -- we someone have to skip DOWN backends
121
* 创建一个新的或分配一个已有的MySQL Proxy到MySQL Server后端的连接
* 按backends顺序和下列次序选择：
** 优先选取一个可读写的后端，直到该后端空闲连接数达到最小空闲连接数
** 其次选取一个只读的后端，直到该后端空闲连接数达到最小空闲连接数
** 最后选取第一个可读写的后端
* backends顺序是：先rw后ro；命令行配置中出现的先后顺序
* 问题：分配算法和实际测试结果有差异，原因不明。比如min_idle_connections设置是1，但可以建min_idle_connections+1=2个连接
* connected_clients总是0，原因不明，此变量需要脚本自行赋值维护？
* 当客户端连接数小于min_idle_connections+1时，调试信息中cur_idle_connections总显示为0。超过min_idle_connections+1后才显示出数值，不会新建连接，等于min_idle_connections+1
===122-139行===
122 -- ok, did we got a backend ?
123
124 if proxy.connection.server then
125 if is_debug then
126 print(" using pooled connection from: " .. proxy.connection.backend_ndx)
127 end
128
129 -- stay with it
130 return proxy.PROXY_IGNORE_RESULT
131 end
132
133 if is_debug then
134 print(" [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle")
135 end
136
137 -- open a new connection
138 end
139
* 没看懂。估计是，如果已建立了连接池，则返回PROXY_IGNORE_RESULT，表示忽略接下来服务器发回的handshake，这样就不会建立起新连接
* 函数connect_server结束
===140-164行===
140 ---
141 -- put the successfully authed connection into the connection pool
142 --
143 -- @param auth the context information for the auth
144 --
145 -- auth.packet is the packet
146 function read_auth_result( auth )
147 if is_debug then
148 print("[read_auth_result] " .. proxy.connection.client.src.name)
149 end
150 if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
151 -- auth was fine, disconnect from the server
152 proxy.connection.backend_ndx = 0
153 elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
154 -- we received either a
155 --
156 -- * MYSQLD_PACKET_ERR and the auth failed or
157 -- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
158 print("(read_auth_result) ... not ok yet");
159 elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
160 -- auth failed
161 end
162 end
163
164
* 参考
** http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol
* 问题
** [[#read_auth_result没有显示调试信息]]
===165-171行===
165 ---
166 -- read/write splitting
167 function read_query( packet )
168 local is_debug = proxy.global.config.rwsplit.is_debug
169 local cmd = commands.parse(packet)
170 local c = proxy.connection.client
171
* read_query函数
* 解析网络包、语句
===172-174行===
172 local r = auto_config.handle(cmd)
173 if r then return r end
174
* 处理PROXY命令(show,set,save,load)，返回PROXY_SEND_RESULT和结果
===175-188行===
175 local tokens
176 local norm_query
177
178 -- looks like we have to forward this statement to a backend
179 if is_debug then
180 print("[read_query] " .. proxy.connection.client.src.name)
181 print(" current backend = " .. proxy.connection.backend_ndx)
182 print(" client default db = " .. c.default_db)
183 print(" client username = " .. c.username)
184 if cmd.type == proxy.COM_QUERY then
185 print(" query = " .. cmd.query)
186 end
187 end
188
* 声明本地变量
* 显示调试信息
===189-204行===
189 if cmd.type == proxy.COM_QUIT then
190 -- don't send COM_QUIT to the backend. We manage the connection
191 -- in all aspects.
192 proxy.response = {
193 type = proxy.MYSQLD_PACKET_OK,
194 }
195
196 if is_debug then
197 print(" (QUIT) current backend = " .. proxy.connection.backend_ndx)
198 end
199
200 return proxy.PROXY_SEND_RESULT
201 end
202
203 proxy.queries:append(1, packet, { resultset_is_needed = true })
204
* 截取QUIT命令，不发送到后端服务器
* 构造查询队列。命令放入查询队列
===205-255行===
205 -- read/write splitting
206 --
207 -- send all non-transactional SELECTs to a slave
208 if not is_in_transaction and
209 cmd.type == proxy.COM_QUERY then
210 tokens = tokens or assert(tokenizer.tokenize(cmd.query))
211
212 local stmt = tokenizer.first_stmt_token(tokens)
213
214 if stmt.token_name == "TK_SQL_SELECT" then
215 is_in_select_calc_found_rows = false
216 local is_insert_id = false
217
218 for i = 1, #tokens do
219 local token = tokens[i]
220 -- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed
221 -- on the same connection
222 -- print("token: " .. token.token_name)
223 -- print(" val: " .. token.text)
224
225 if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then
226 is_in_select_calc_found_rows = true
227 elseif not is_insert_id and token.token_name == "TK_LITERAL" then
228 local utext = token.text:upper()
229
230 if utext == "LAST_INSERT_ID" or
231 utext == "@@INSERT_ID" then
232 is_insert_id = true
233 end
234 end
235
236 -- we found the two special token, we can't find more
237 if is_insert_id and is_in_select_calc_found_rows then
238 break
239 end
240 end
241
242 -- if we ask for the last-insert-id we have to ask it on the original
243 -- connection
244 if not is_insert_id then
245 local backend_ndx = lb.idle_ro()
246
247 if backend_ndx > 0 then
248 proxy.connection.backend_ndx = backend_ndx
249 end
250 else
251 print(" found a SELECT LAST_INSERT_ID(), staying on the same backend")
252 end
253 end
254 end
255
* 没看懂
** 查看查询语句中是否含有SQL_CALC_FOUND_ROWS或LAST_INSERT_ID
** 如果没有LAST_INSERT_ID，则选一个空闲的只读后端，否则后端不变
===256-272行===
256 -- no backend selected yet, pick a master
257 if proxy.connection.backend_ndx == 0 then
258 -- we don't have a backend right now
259 --
260 -- let's pick a master as a good default
261 --
262 proxy.connection.backend_ndx = lb.idle_failsafe_rw()
263 end
264
265 -- by now we should have a backend
266 --
267 -- in case the master is down, we have to close the client connections
268 -- otherwise we can go on
269 if proxy.connection.backend_ndx == 0 then
270 return proxy.PROXY_SEND_QUERY
271 end
272
* 其它非查询语句，选择一个可读写后端
* 如无可选后端，不做后续操作，直接跳出函数
===273-302行===
273 local s = proxy.connection.server
274
275 -- if client and server db don't match, adjust the server-side
276 --
277 -- skip it if we send a INIT_DB anyway
278 if cmd.type ~= proxy.COM_INIT_DB and
279 c.default_db and c.default_db ~= s.default_db then
280 print(" server default db: " .. s.default_db)
281 print(" client default db: " .. c.default_db)
282 print(" syncronizing")
283 proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true })
284 end
285
286 -- send to master
287 if is_debug then
288 if proxy.connection.backend_ndx > 0 then
289 local b = proxy.global.backends[proxy.connection.backend_ndx]
290 print(" sending to backend : " .. b.dst.name);
291 print(" is_slave : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
292 print(" server default db: " .. s.default_db)
293 print(" server username : " .. s.username)
294 end
295 print(" in_trans : " .. tostring(is_in_transaction))
296 print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
297 print(" COM_QUERY : " .. tostring(cmd.type == proxy.COM_QUERY))
298 end
299
300 return proxy.PROXY_SEND_QUERY
301 end
302
* 保证后端数据库和客户端数据库一致
* 显示调试信息
===303-332行===
303 ---
304 -- as long as we are in a transaction keep the connection
305 -- otherwise release it so another client can use it
306 function read_query_result( inj )
307 local is_debug = proxy.global.config.rwsplit.is_debug
308 local res = assert(inj.resultset)
309 local flags = res.flags
310
311 if inj.id ~= 1 then
312 -- ignore the result of the USE
313 -- the DB might not exist on the backend, what do do ?
314 --
315 if inj.id == 2 then
316 -- the injected INIT_DB failed as the slave doesn't have this DB
317 -- or doesn't have permissions to read from it
318 if res.query_status == proxy.MYSQLD_PACKET_ERR then
319 proxy.queries:reset()
320
321 proxy.response = {
322 type = proxy.MYSQLD_PACKET_ERR,
323 errmsg = "can't change DB ".. proxy.connection.client.default_db ..
324 " to on slave " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name
325 }
326
327 return proxy.PROXY_SEND_RESULT
328 end
329 end
330 return proxy.PROXY_IGNORE_RESULT
331 end
332
* 处理COM_INIT_DB命令的返回结果
* 忽略其它语句的结果
===333-348行===
333 is_in_transaction = flags.in_trans
334 local have_last_insert_id = (res.insert_id and (res.insert_id > 0))
335
336 if not is_in_transaction and
337 not is_in_select_calc_found_rows and
338 not have_last_insert_id then
339 -- release the backend
340 proxy.connection.backend_ndx = 0
341 elseif is_debug then
342 print("(read_query_result) staying on the same backend")
343 print(" in_trans : " .. tostring(is_in_transaction))
344 print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
345 print(" have_insert_id : " .. tostring(have_last_insert_id))
346 end
347 end
348
* 参考：http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#OK_Packet
* 检查是否在事务内，是否有insert_id。设置backend_ndx=0，下次重新选取后端连接？
===349-364行===
349 ---
350 -- close the connections if we have enough connections in the pool
351 --
352 -- @return nil - close connection
353 -- IGNORE_RESULT - store connection in the pool
354 function disconnect_client()
355 local is_debug = proxy.global.config.rwsplit.is_debug
356 if is_debug then
357 print("[disconnect_client] " .. proxy.connection.client.src.name)
358 end
359
360 -- make sure we are disconnection from the connection
361 -- to move the connection into the pool
362 proxy.connection.backend_ndx = 0
363 end
364
* 不懂
==问题==
===ERROR 1047 (08S01): Unknown command===
现象：
* 用MySQL Proxy 0.8.1缺省的rw-splitting脚本，客户端连接多次，当每个backend都建立起一定空闲连接（连接数都达到min_idle_connects+1，原因不清）后，再次连接时报错"ERROR 1047 (08S01): Unknown command"。如下：
$ ./mysql -h 127.0.0.1 -P 4040 -u yyyy -pxxxx -e "proxy show config"
+---------+----------------------+-------+---------+
| module | option | value | type |
+---------+----------------------+-------+---------+
| rwsplit | min_idle_connections | 1 | number |
| rwsplit | is_debug | true | boolean |
| rwsplit | max_idle_connections | 4 | number |
+---------+----------------------+-------+---------+
$ ./mysql -h 127.0.0.1 -P 4040 -u yyyy -pxxxx -e "proxy show config"
+---------+----------------------+-------+---------+
| module | option | value | type |
+---------+----------------------+-------+---------+
| rwsplit | min_idle_connections | 1 | number |
| rwsplit | is_debug | true | boolean |
| rwsplit | max_idle_connections | 4 | number |
+---------+----------------------+-------+---------+
$ ./mysql -h 127.0.0.1 -P 4040 -u yyyy -pxxxx -e "proxy show config"
ERROR 1047 (08S01): Unknown command
* MySQL数据库版本是5.5的，改成5.1问题消失
原因：
* 好像认证有问题，5.5下read_auth_result了2次。原因待查
If a long-password capable client tries to authenticate to a server that supports long passwords, but the user password provided is actually short, read_auth_result() will be called twice. The first time, auth.packet:byte() will equal 254, indicating that the client should try again using the old password protocol. The second time time read_auth_result()/ is called, auth.packet:byte() will indicate whether the authentication actually succeeded.
解决：
* 无
* 不用5.5版本的数据库
===read_auth_result没有显示调试信息===
现象：
* is_debug设置为true，然而read_auth_result没有显示出debug信息
原因：
* 脚本问题，函数read_auth_result中的变量is_debug没有赋值
解决：
* 在147行之前增加如下代码：
local is_debug = proxy.global.config.rwsplit.is_debug
* 修改脚本后即可生效，不用重启MySQL Proxy
===proxy set global 报错 ERROR 1064 (42000): You have an error in your SQL syntax===
现象：
* 客户端执行命令"proxy set global xxx.yyy = zzz"，设置MySQL Proxy参数，报语法错误，如下：
mysql> proxy set global rwsplit.min_idle_connections = 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'proxy set global rwsplit.min_idle_connections = 2' at line 1
原因：
* auto-config.lua脚本问题。不能处理带下划线的参数名称。
解决：
* 修改脚本lib/mysql-proxy/lua/proxy/auto-config.lua
将
local literal = l.R("az", "AZ") ^ 1
改成
local literal = l.R("az", "AZ", "__") ^ 1
* 然后重启MySQL Proxy进程
===proxy set global 报错 ERROR 1105 (07000): (lua) proxy.response.errmsg is nil===
现象：
* 设置is_debug=false报错，如下：
mysql> proxy set global rwsplit.is_debug = false;
ERROR 1105 (07000): (lua) proxy.response.errmsg is nil
原因：
* auto-config.lua脚本问题。当参数值等于false时，parse_value函数返回false，导致"if not val then"判断出错
解决：
* 修改lib/mysql-proxy/lua/proxy/auto-config.lua，handle函数代码
将
if not val then
改为
if val == nil then
* 然后重启MySQL Proxy服务
===proxy save config 保存配置文件大小等于0===
现象：
* proxy save config 命令保存的配置文件，文件大小等于0，要等待一段时间，或重启MySQL Proxy后，其大小才不等于0
-rw-r----- 1 oracle oinstall 0 Jul 4 15:36 "a.log"
原因：
* auto-config.lua脚本问题。文件写操作之后，没有刷新缓冲区
解决：
* 修改lib/mysql-proxy/lua/proxy/auto-config.lua，save函数代码
在
file:write(content)
之后，增加
file:flush(content)
===proxy save config 保存配置文件名带双引号===
现象：
* proxy save config 命令保存的配置文件，文件名包括双引号
-rw-r----- 1 oracle oinstall 186 Jul 4 15:59 "a"
原因：
* auto-config.lua脚本问题。文件名匹配包括双引号
解决：
* 修改lib/mysql-proxy/lua/proxy/auto-config.lua代码
将
local string_quoted = l.P("\"") * ( 1 - l.P("\"") )^0 * l.P("\"") -- /".*"/
改成
local string_quoted = l.P("\"") * l.C(( 1 - l.P("\"") )^0) * l.P("\"") -- /".*"/
将
l.Ct( l.Cc("string") * l.C(string_quoted) +
...
...
(SAVE / "SAVE" * CONFIG * WS^1 * INTO * l.C(string_quoted)) +
(LOAD / "LOAD" * CONFIG * WS^1 * FROM * l.C(string_quoted))) * -1)
改成
l.Ct( l.Cc("string") * string_quoted +
...
...
(SAVE / "SAVE" * CONFIG * WS^1 * INTO * string_quoted) +
(LOAD / "LOAD" * CONFIG * WS^1 * FROM * string_quoted)) * -1)
-fin-
Posts
