mysql proxy lua脚本_mysql-proxy 按表分发查询的lua脚本

请修改这里：注意，本版本尚未经过严格测试与实际检验！！发出来只为提供一种思路，使用时请根据实际情况修改，风险自担。-- 将分到另一个库中的表明列在这里：local remote_tables = {"table1", "table2"}Copyright (C) 2007 MySQL ABThis 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--]]----- 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----  将分到另一个库中的表明列在这里：local remote_tables = {"table1", "table2"}local min_idle_connections = 1local max_idle_connections = 2-- 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.backends dolocal s = proxy.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 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 andcur_idle < least_idle_conns ) thenleast_idle_conns_ndx = ileast_idle_conns = s.idling_connectionsendendendif least_idle_conns_ndx > 0 thenproxy.connection.backend_ndx = least_idle_conns_ndxendif proxy.connection.backend_ndx > 0 thenlocal s = proxy.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 true or 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 connectionend----- 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,}return proxy.PROXY_SEND_RESULTendif proxy.connection.backend_ndx == 0 then-- we don't have a backend right now---- let's pick a master as a good defaultquery = string.sub(packet, 2)for i = 1, #remote_tables doprint (i, remote_tables)if string.find(query, remote_tables) thenproxy.connection.backend_ndx = 2breakendproxy.connection.backend_ndx = 1end-- if string.find(query, "auth_group") then--      print("Use Another Database")--      proxy.connection.backend_ndx = 2-- else--        print("Use local database")--      proxy.connection.backend_ndx = 1-- endendproxy.queries:append(1, packet)return proxy.PROXY_SEND_QUERYend---