load balancing and fail over handling

query analysis and logging

SQL macros

query rewriting

executing shell commands

For R/W Splitting we need a connection pooling. We only switch to another backend if we already have a authenticated connection open to that backend.The MySQL protocol first does a challenge-response handshake. When we enter the query/result stage it is too late to authenticate new connections. We have to make sure that we have enough open connections to operate nicely.



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



MySQL-Proxy, announced in June , is a binary application that sits between your MySQL client and server, and supports the embedded scripting language Lua . The proxy can be used to analyze, monitor and transform communication, and supports a wide range of scenarios including:One of the more powerful features of MySQL Proxy is the ability to do " Read/Write Splitting ". The basic concept is to have a master database handle transactional queries while slaves handle SELECT queries. Replication is used to synchronize the changes due to transactional queries with the slaves in the cluster.Jan Kneschke writing about the technique in " MySQL Proxy learns R/W Splitting ", discusses connection pooling:The LUA script to handle read/write splitting is straightforward:Jan notes that the technique can also be used to implement other data distribution strategies, such as sharding.