16hot 的博客

请修改这里：注意，本版本尚未经过严格测试与实际检验！！发出来只为提供一种思路，使用时请根据实际情况修改，风险自担。
-- 将分到另一个库中的表明列在这里：
local remote_tables = {"table1", "table2"}

在启动的时候：

mysql-proxy 
    --proxy-backend-addresses=10.0.0.2:3306 
    --proxy-backend-addresses=10.0.0.3:3306 
    --proxy-lua-script=./mysql_proxy_store.lua

lua脚本：

-- mysql_proxy_store.lua
-- for mercury
-- author: Albert Lee
--
-- for billions samples store requirement, we split the samples & store table 
--
--[[
 
   Copyright (C) 2007 MySQL AB
 
   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the 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 of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.
 
   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, 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 = 1
local max_idle_connections = 2
 
-- debug
local 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 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() 
        -- 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 backend
 
        if is_debug then
                print()
                print("[connect_server] ")
        end
 
        local least_idle_conns_ndx = 0
        local least_idle_conns = 0
 
        for i = 1, #proxy.backends do
                local s = proxy.backends
                local pool     = s.pool -- we don't have a username yet, try to find a connections which is idling
                local cur_idle = pool.users[""].cur_idle_connections
 
                if is_debug then
                        print("  [".. i .."].connected_clients = " .. s.connected_clients)
                        print("  [".. i .."].idling_connections = " .. cur_idle)
                        print("  [".. i .."].type = " .. s.type)
                        print("  [".. i .."].state = " .. s.state)
                end
 
                if s.state ~= proxy.BACKEND_STATE_DOWN then
                        -- try to connect to each backend once at least
                        if cur_idle == 0 then
                                proxy.connection.backend_ndx = i
                                if is_debug then
                                        print("  [".. i .."] open new connection")
                                end
                                return
                        end
 
                        -- try to open at least min_idle_connections
                        if least_idle_conns_ndx == 0 or
                           ( cur_idle < min_idle_connections and 
                             cur_idle < least_idle_conns ) then
                                least_idle_conns_ndx = i
                                least_idle_conns = s.idling_connections
                        end
                end
        end
 
        if least_idle_conns_ndx > 0 then
                proxy.connection.backend_ndx = least_idle_conns_ndx
        end
 
        if proxy.connection.backend_ndx > 0 then 
                local 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 idling
                local cur_idle = pool.users[""].cur_idle_connections
 
                if cur_idle >= min_idle_connections then
                        -- we have 4 idling connections in the pool, that's good enough
                        if true or is_debug then
                                print("  using pooled connection from: " .. proxy.connection.backend_ndx)
                        end
 
                        return proxy.PROXY_IGNORE_RESULT
                end
        end
 
        if is_debug then
                print("  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 packet
function read_auth_result( auth )
        if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
                -- auth was fine, disconnect from the server
                proxy.connection.backend_ndx = 0
        elseif 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 sent
                print("(read_auth_result) ... not ok yet");
        elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
                -- auth failed
        end
end
 
 
--- 
-- 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
 
        if 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_RESULT
        end
 
        if proxy.connection.backend_ndx == 0 then
                -- we don't have a backend right now
                -- 
                -- let's pick a master as a good default
 
                query = string.sub(packet, 2)
 
 
                for i = 1, #remote_tables do
                        print (i, remote_tables)
                        if string.find(query, remote_tables) then
                                proxy.connection.backend_ndx = 2
                                break
                        end
                        proxy.connection.backend_ndx = 1
                end
 
                -- 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
                -- end
 
        end
 
 
        proxy.queries:append(1, packet)
 
        return proxy.PROXY_SEND_QUERY
end
 
---
-- as long as we are in a transaction keep the connection
-- otherwise release it so another client can use it
function read_query_result( inj ) 
        local res      = assert(inj.resultset)
        local flags    = res.flags
 
        if inj.id ~= 1 then
                -- ignore the result of the USE 
                return proxy.PROXY_IGNORE_RESULT
        end
        is_in_transaction = flags.in_trans
 
        if not is_in_transaction then
                -- release the backend
                proxy.connection.backend_ndx = 0
        end
end
 
--- 
-- close the connections if we have enough connections in the pool
--
-- @return nil - close connection 
--         IGNORE_RESULT - store connection in the pool
function disconnect_client()
        if is_debug then
                print("[disconnect_client]")
        end
 
        if 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 one
                for i = 1, #proxy.backends do
                        local s = proxy.backends
                        local pool     = s.pool -- we don't have a username yet, try to find a connections which is idling
                        local cur_idle = pool.users[proxy.connection.client.username].cur_idle_connections
 
                        if s.state ~= proxy.BACKEND_STATE_DOWN and
                           cur_idle > max_idle_connections then
                                -- try to disconnect a backend
                                proxy.connection.backend_ndx = i
                                if is_debug then
                                        print("  [".. i .."] closing connection, idling: " .. cur_idle)
                                end
                                return
                        end
                end
        end
end