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:
-- Read/write splitting
--
-- Send all non-transactional select statements to a slave Database
CopyCode The Code is as follows: 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 ).