MySQLProxylearnsR/WSplitting

--- config
--
-- connection pool
local min_idle_connections = 4
local max_idle_connections = 8

---
-- 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

 local least_idle_conns_ndx = 0
 local least_idle_conns = 0

 for i = 1, #proxy.servers do
 local s = proxy.servers[i]

 if s.state ~= proxy.BACKEND_STATE_DOWN then
 -- try to connect to each backend once at least
 if s.idling_connections == 0 then
 proxy.connection.backend_ndx = i
 return
 end

 -- try to open at least min_idle_connections
 if least_idle_conns_ndx == 0 or
 ( s.idling_connections < min_idle_connections and
 s.idling_connections < 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 and
 proxy.servers[proxy.connection.backend_ndx].idling_connections >= min_idle_connections then
 -- we have 4 idling connections in the pool, that's good enough

 return proxy.PROXY_IGNORE_RESULT
 end

 -- open a new connection
end

---
-- put the authed connection into the connection pool
function read_auth_result(packet)
 -- disconnect from the server
 proxy.connection.backend_ndx = 0
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 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.servers do
 local s = proxy.servers[i]

 if s.state ~= proxy.BACKEND_STATE_DOWN and
 s.idling_connections > max_idle_connections then
 -- try to disconnect a backend
 proxy.connection.backend_ndx = i
 return
 end
 end
 end
end

-- read/write splitting
function read_query( packet )
 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_ERR,
 errmsg = "ignored the COM_QUIT"
 }

 return proxy.PROXY_SEND_RESULT
 end

 -- as we switch between different connenctions we have to make sure that
 -- we use always the same DB
 if packet:byte() == proxy.COM_INIT_DB then
 -- default_db is connection global
 default_db = packet:sub(2)
 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
 for i = 1, #proxy.servers do
 local s = proxy.servers[i]

 if s.idling_connections > 0 and
 s.state ~= proxy.BACKEND_STATE_DOWN and
 s.type == proxy.BACKEND_TYPE_RW then
 proxy.connection.backend_ndx = i
 break
 end
 end
 end

 if packet:byte() == proxy.COM_QUERY and default_db then
 -- how can I know the db of the server connection ?
 proxy.queries:append(2, string.char(proxy.COM_INIT_DB) .. default_db)
 end
 proxy.queries:append(1, packet)

 -- read/write splitting
 --
 -- send all non-transactional SELECTs to a slave
 if is_in_transaction == 0 and
 packet:byte() == proxy.COM_QUERY and
 packet:sub(2, 7) == "SELECT" then
 local max_conns = -1
 local max_conns_ndx = 0

 for i = 1, #proxy.servers do
 local s = proxy.servers[i]

 -- pick a slave which has some idling connections
 if s.type == proxy.BACKEND_TYPE_RO and
 s.idling_connections > 0 then
 if max_conns == -1 or
 s.connected_clients < max_conns then
 max_conns = s.connected_clients
 max_conns_ndx = i
 end
 end
 end

 -- we found a slave which has a idling connection
 if max_conns_ndx > 0 then
 proxy.connection.backend_ndx = max_conns_ndx
 end
 else
 -- send to master
 end

 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 is_in_transaction == 0 then
 -- release the backend
 proxy.connection.backend_ndx = 0
 end
end

$ mysql-proxy /
 --proxy-backend-addresses=10.0.0.1:3306 /
 --proxy-read-only-backend-addresses=10.0.0.10:3306 /
 --proxy-read-only-backend-addresses=10.0.0.12:3306 /
 --proxy-lua-script=examples/tutorial-keepalive.lua