MySQL Read and write separation (Basic) _mysql

The basic principle is to have the primary database handle transactional queries and select queries from the database. Database replication is used to synchronize changes caused by transactional queries to the database from the cluster.

The Kneschke in MySQL Proxy learns r/w splitting describes this technique and connection pooling issues in detail:

In order to achieve read/write separation we need to connect the pool. We switch to the back end only if a certified connection to a backend has been opened. The MySQL protocol first shakes hands. It is too late to authenticate the new connection when entering into the query/return result phase. We have to make sure that we have enough open connections to stay on track.
Lua scripts to implement read-write separation:
--Read and write separation
--
--Send all non-transactional Select to one from the database
　　

Copy Code code 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]
--You need to select a database that has an idle connection
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, we've found a database with an idle connection.
If max_conns_ndx > 0 Then
Proxy.connection.backend_ndx = Max_conns_ndx
End
Else
--Send to main database
End
Return proxy. Proxy_send_query

Note: This technique can also be used to implement other data distribution strategies, such as fragmentation (Sharding).