一个支持主从，事务以及连接池功能的mysql-proxy脚本

qq591577286

　　看了点例子，根据mysql-proxy里的keepalive例子修改了一个支持主从分离，事务到主库，非事务查询到从库，以及连接断开时自动回滚的脚本，分享一下。
　　
　　--[[ $%BEGINLICENSE%$Copyright (C) 2007-2008 MySQL AB, 2008 Sun Microsystems, IncThis program is free software; you can redistribute it and/or modifyit under the terms of the GNU General Public License as published bythe Free Software Foundation; version 2 of the License.This program is distributed in the hope that it will be useful,but WITHOUT ANY WARRANTY; without even the implied warranty ofMERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See theGNU General Public License for more details.You should have received a copy of the GNU General Public Licensealong with this program; if not, write to the Free SoftwareFoundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA$%ENDLICENSE%$ --]]--[[--]]----- a flexible statement based load balancer with connection pooling---- * build a connection pool of min_idle_connections for each backend and --   maintain its size-- * reusing a server-side connection when it is idling-- --- config---- connection poollocal min_idle_connections = 4local max_idle_connections = 8-- debuglocal is_debug = true--- end of config----- read/write splitting sends all non-transactional SELECTs to the slaves---- is_in_transaction tracks the state of the transactionslocal 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() -- make sure that we connect to each backend at least ones to -- keep the connections to the servers alive---- on read_query we can switch the backends again to another backendif is_debug thenprint()print("[connect_server] ")endlocal least_idle_conns_ndx = 0local least_idle_conns = 0for i = 1, #proxy.global.backends dolocal s = proxy.global.backendslocal pool     = s.pool -- we don't have a username yet, try to find a connections which is idlinglocal cur_idle = pool.users[""].cur_idle_connectionsif cur_idle == nil thencur_idle = 0endif is_debug thenprint("  [".. i .."].connected_clients = " .. s.connected_clients)print("  [".. i .."].idling_connections = " .. cur_idle)print("  [".. i .."].type = " .. s.type)print("  [".. i .."].state = " .. s.state)endif s.state ~= proxy.BACKEND_STATE_DOWN then-- try to connect to each backend once at leastif cur_idle == 0 thenproxy.connection.backend_ndx = iif is_debug thenprint("  [".. i .."] open new connection")endreturnend-- try to open at least min_idle_connectionsif least_idle_conns_ndx == 0 or( cur_idle < min_idle_connections and cur_idle < least_idle_conns ) thenleast_idle_conns_ndx = ileast_idle_conns = s.idling_connectionsif least_idle_conns == nil thenleast_idle_conns = 0endendendendif least_idle_conns_ndx > 0 thenproxy.connection.backend_ndx = least_idle_conns_ndxendif proxy.connection.backend_ndx > 0 then local s = proxy.global.backends[proxy.connection.backend_ndx]local pool     = s.pool -- we don't have a username yet, try to find a connections which is idlinglocal cur_idle = pool.users[""].cur_idle_connectionsif cur_idle >= min_idle_connections then-- we have 4 idling connections in the pool, that's good enoughif is_debug thenprint("  using pooled connection from: " .. proxy.connection.backend_ndx)endreturn proxy.PROXY_IGNORE_RESULTendendif is_debug thenprint("  opening new connection on: " .. proxy.connection.backend_ndx)end-- open a new connection end--- -- put the successfully authed connection into the connection pool---- @param auth the context information for the auth---- auth.packet is the packetfunction read_auth_result( auth )if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then-- auth was fine, disconnect from the serverproxy.connection.backend_ndx = 0elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then-- we received either a -- -- * MYSQLD_PACKET_ERR and the auth failed or-- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sentprint("(read_auth_result) ... not ok yet");elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then-- auth failedendend--- -- read/write splittingfunction read_query( packet ) if is_debug thenprint("[read_query]")print("  authed backend = " .. proxy.connection.backend_ndx)print("  used db = " .. proxy.connection.client.default_db)endif packet:byte() == proxy.COM_QUIT then-- don't send COM_QUIT to the backend. We manage the connection-- in all aspects.proxy.response = {type = proxy.MYSQLD_PACKET_OK,}if is_in_transaction thenprint("  transaction on, rollback now")proxy.queries.append(1, string:char(proxy.COM_QUERY) .. "ROLLBACK", { resultset_is_needed = false}) endreturn proxy.PROXY_SEND_RESULTendif proxy.connection.backend_ndx == 0 thenif is_debug thenprint("  no connection, select now")endis_master = trueif packet:byte() == proxy.COM_QUERY thencommand = packet:sub(2, 7)print("  command:" .. command)if string.lower(command) == "select" thenprint("  session not in transaction, select go to slave")is_master = falseendend-- we don't have a backend right now-- -- let's pick a master as a good defaultfor i = 1, #proxy.global.backends dolocal s = proxy.global.backendslocal pool     = s.pool -- we don't have a username yet, try to find a connections which is idlinglocal cur_idle = pool.users[proxy.connection.client.username].cur_idle_connectionsif cur_idle > 0 and s.state ~= proxy.BACKEND_STATE_DOWN thenif s.type == proxy.BACKEND_TYPE_RW and is_master == true thenproxy.connection.backend_ndx = ibreakelseif s.type == proxy.BACKEND_TYPE_RO and is_master == false thenproxy.connection.backend_ndx = ibreakendendendendif is_debug thenprint("  connection:" .. proxy.connection.backend_ndx)endif true or proxy.connection.client.default_db and proxy.connection.client.default_db ~= proxy.connection.server.default_db then-- sync the client-side default_db with the server-side default_dbproxy.queries:append(2, string.char(proxy.COM_INIT_DB) .. proxy.connection.client.default_db,  { resultset_is_needed = true })endproxy.queries:append(1, packet,  { resultset_is_needed = true })return proxy.PROXY_SEND_QUERYend----- as long as we are in a transaction keep the connection-- otherwise release it so another client can use itfunction read_query_result( inj ) local res      = assert(inj.resultset)local flags    = res.flagsif inj.id ~= 1 then-- ignore the result of the USE <default_db>return proxy.PROXY_IGNORE_RESULTendis_in_transaction = flags.in_transif not is_in_transaction then-- release the backendproxy.connection.backend_ndx = 0endend--- -- close the connections if we have enough connections in the pool---- @return nil - close connection --         IGNORE_RESULT - store connection in the poolfunction disconnect_client()if is_debug thenprint("[disconnect_client]")endif proxy.connection.backend_ndx == 0 then-- currently we don't have a server backend assigned---- pick a server which has too many idling connections and close onefor i = 1, #proxy.global.backends dolocal s = proxy.global.backendslocal pool     = s.pool -- we don't have a username yet, try to find a connections which is idlinglocal cur_idle = pool.users[proxy.connection.client.username].cur_idle_connectionsif s.state ~= proxy.BACKEND_STATE_DOWN andcur_idle > max_idle_connections then-- try to disconnect a backendproxy.connection.backend_ndx = iif is_debug thenprint("  [".. i .."] closing connection, idling: " .. cur_idle)endreturnendendendend