目的:
用mysql proxy做DB连接池使用环境:
Linux+mysql proxy(0.6.1)使用脚本db_pool.lua,内容如下:-- connection pool
local max_total_conn =30
-- debug
local is_debug = true--- end of configif not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
cur_num = 0,
is_debug =true
}
endlocal query_time = 0
local query_response_time =0
local query_sum_time=0local client_cur_num=0 ---
-- read/write splitting sends all non-transactional SELECTs to the slaves
--
-- is_in_transaction tracks the state of the transactions
local is_in_transaction = 0---
-- get a connection to a backend
--
-- as long as we don't have enough connections in the pool, create new connections
--
function connect_server()
if is_debug then
print()
print("[connect_server] ")
endclient_cur_num=client_cur_num+1
print("connect client connection,client_cur_num="..client_cur_num)if proxy.global.config.rwsplit.cur_num >= max_total_conn then
proxy.connection.backend_ndx = 1
print("in connect: proxy.global.conn_counter arrive max, not send to mysql,proxy.global.config.rwsplit.cur_num=" .. proxy.gl
obal.config.rwsplit.cur_num .. "max_total_conn=" .. max_total_conn)
print("useing pool...")
return proxy.PROXY_IGNORE_RESULT
endprint("in connect proxy.global.config.rwsplit.cur_num=" .. proxy.global.config.rwsplit.cur_num .."max_total_conn=" .. max_total_conn
)
proxy.connection.backend_ndx = 1 if is_debug then
print(" opening new connection on: " .. proxy.connection.backend_ndx)
end -- open a new connection
end-- add by tipyluo
function read_handshake( auth )
-- print("read auto result="..auth.packet:byte())
proxy.global.config.rwsplit.cur_num = proxy.global.config.rwsplit.cur_num + 1
print("handshake come in , proxy.global.config.rwsplit.cur_num=" .. proxy.global.config.rwsplit.cur_num)
endfunction read_auth()
print("readauth come in")
end--cy
function read_auth_result( auth )
--proxy.global.config.rwsplit.cur_num = proxy.global.config.rwsplit.cur_num + 1
print("read auto result="..auth.packet:byte())
print("read auth result come in")
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
-- auth was fine,disconnect client connection,and add to pool
print("read auth result come in,client connection add to pool..")
proxy.connection.backend_ndx = 0
endend
---
-- read/write splitting
function read_query( packet )
--if is_debug then
-- print("[read_query]")
-- print(" authed backend = " .. proxy.connection.backend_ndx)
-- print(" used db = " .. proxy.connection.client.default_db)
--end proxy.connection.backend_ndx=1 if packet:byte() == proxy.COM_QUIT then
-- don't send COM_QUIT to the backend. We manage the connection
-- in all aspects. if proxy.global.config.rwsplit.cur_num <= max_total_conn then
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
}
print("ignore disconnect msg")
return proxy.PROXY_SEND_RESULT
end
end --don't delete the below row
proxy.queries:append(1, packet) --proxy.connection.backend_ndx=1
return proxy.PROXY_SEND_QUERY
endfunction read_query_result( inj )
query_time=inj.query_time / 1000
query_response_time=inj.response_time / 1000
--query_sum_time=query_time+query_response_time
query_sum_time=query_response_time if query_sum_time>1000 then
print("sum time:"..query_sum_time.."ms")
print("---"..inj.query)
end
if proxy.connection.backend_ndx >0 then
proxy.connection.backend_ndx = 0
endendfunction disconnect_client()
--proxy.global.config.rwsplit.cur_num = proxy.global.config.rwsplit.cur_num - 1
client_cur_num=client_cur_num-1
print("disconnect client connection,client_cur_num="..client_cur_num) if proxy.global.config.rwsplit.cur_num <= max_total_conn then
print("disconnect client,relase connection and to pool")
proxy.connection.backend_ndx = 0
return proxy.PROXY_IGNORE_RESULT
end
print("adjust proxy.global.config.rwsplit.cur_num value: -1")
proxy.connection.backend_ndx =1
proxy.global.config.rwsplit.cur_num = proxy.global.config.rwsplit.cur_num - 1end存在问题:
当输出下述日志时,proxy与客户端的连接数会自动增加,用netstat在proxy服务器上查看端口,其状态为"ESTABLISHED",实际上,而在客户端主机上用netstat查看,并没有建立socket连接,这是为什么?
network-mysqld-proxy.c.3022: I have no server backend, closing connection
network-mysqld.c.1411: plugin_call(CON_STATE_READ_QUERY) failed
用mysql proxy做DB连接池使用环境:
Linux+mysql proxy(0.6.1)使用脚本db_pool.lua,内容如下:-- connection pool
local max_total_conn =30
-- debug
local is_debug = true--- end of configif not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
cur_num = 0,
is_debug =true
}
endlocal query_time = 0
local query_response_time =0
local query_sum_time=0local client_cur_num=0 ---
-- read/write splitting sends all non-transactional SELECTs to the slaves
--
-- is_in_transaction tracks the state of the transactions
local is_in_transaction = 0---
-- get a connection to a backend
--
-- as long as we don't have enough connections in the pool, create new connections
--
function connect_server()
if is_debug then
print()
print("[connect_server] ")
endclient_cur_num=client_cur_num+1
print("connect client connection,client_cur_num="..client_cur_num)if proxy.global.config.rwsplit.cur_num >= max_total_conn then
proxy.connection.backend_ndx = 1
print("in connect: proxy.global.conn_counter arrive max, not send to mysql,proxy.global.config.rwsplit.cur_num=" .. proxy.gl
obal.config.rwsplit.cur_num .. "max_total_conn=" .. max_total_conn)
print("useing pool...")
return proxy.PROXY_IGNORE_RESULT
endprint("in connect proxy.global.config.rwsplit.cur_num=" .. proxy.global.config.rwsplit.cur_num .."max_total_conn=" .. max_total_conn
)
proxy.connection.backend_ndx = 1 if is_debug then
print(" opening new connection on: " .. proxy.connection.backend_ndx)
end -- open a new connection
end-- add by tipyluo
function read_handshake( auth )
-- print("read auto result="..auth.packet:byte())
proxy.global.config.rwsplit.cur_num = proxy.global.config.rwsplit.cur_num + 1
print("handshake come in , proxy.global.config.rwsplit.cur_num=" .. proxy.global.config.rwsplit.cur_num)
endfunction read_auth()
print("readauth come in")
end--cy
function read_auth_result( auth )
--proxy.global.config.rwsplit.cur_num = proxy.global.config.rwsplit.cur_num + 1
print("read auto result="..auth.packet:byte())
print("read auth result come in")
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
-- auth was fine,disconnect client connection,and add to pool
print("read auth result come in,client connection add to pool..")
proxy.connection.backend_ndx = 0
endend
---
-- read/write splitting
function read_query( packet )
--if is_debug then
-- print("[read_query]")
-- print(" authed backend = " .. proxy.connection.backend_ndx)
-- print(" used db = " .. proxy.connection.client.default_db)
--end proxy.connection.backend_ndx=1 if packet:byte() == proxy.COM_QUIT then
-- don't send COM_QUIT to the backend. We manage the connection
-- in all aspects. if proxy.global.config.rwsplit.cur_num <= max_total_conn then
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
}
print("ignore disconnect msg")
return proxy.PROXY_SEND_RESULT
end
end --don't delete the below row
proxy.queries:append(1, packet) --proxy.connection.backend_ndx=1
return proxy.PROXY_SEND_QUERY
endfunction read_query_result( inj )
query_time=inj.query_time / 1000
query_response_time=inj.response_time / 1000
--query_sum_time=query_time+query_response_time
query_sum_time=query_response_time if query_sum_time>1000 then
print("sum time:"..query_sum_time.."ms")
print("---"..inj.query)
end
if proxy.connection.backend_ndx >0 then
proxy.connection.backend_ndx = 0
endendfunction disconnect_client()
--proxy.global.config.rwsplit.cur_num = proxy.global.config.rwsplit.cur_num - 1
client_cur_num=client_cur_num-1
print("disconnect client connection,client_cur_num="..client_cur_num) if proxy.global.config.rwsplit.cur_num <= max_total_conn then
print("disconnect client,relase connection and to pool")
proxy.connection.backend_ndx = 0
return proxy.PROXY_IGNORE_RESULT
end
print("adjust proxy.global.config.rwsplit.cur_num value: -1")
proxy.connection.backend_ndx =1
proxy.global.config.rwsplit.cur_num = proxy.global.config.rwsplit.cur_num - 1end存在问题:
当输出下述日志时,proxy与客户端的连接数会自动增加,用netstat在proxy服务器上查看端口,其状态为"ESTABLISHED",实际上,而在客户端主机上用netstat查看,并没有建立socket连接,这是为什么?
network-mysqld-proxy.c.3022: I have no server backend, closing connection
network-mysqld.c.1411: plugin_call(CON_STATE_READ_QUERY) failed
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货