One of the most powerful functions of MySQL proxy is to implement read/write splitting )". The basic principle is to allow the primary database to process transactional queries, but to process select queries from the database. Database Replication is used to synchronize changes caused by transactional queries to the slave database in the cluster.
Jan kneschke detailed this technique and connection pool problems in MySQL proxy learns R/W Splitting:
To achieve read/write splitting, we need a connection pool. We can switch to the backend only when an authenticated connection has been enabled to a backend. The MySQL Protocol first shakes hands. It is too late to authenticate the new connection when the query/return result is reached. We must ensure that we have enough open connections to maintain normal operation.
Lua script for read/write Splitting:
CopyCode The Code is as follows: -- read/write splitting
--
-- Send all non-transactional select statements to a slave Database
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]
-- Select a slave database with idle 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
-- So far, we have found a slave database with idle connections.
If max_conns_ndx> 0 then
Proxy. Connection. backend_ndx = max_conns_ndx
End
Else
-- Send to primary database
End
Return proxy. proxy_send_query
Note: this technique can also be used to implement other data distribution policies, such as sharding ).
