mysql reading and writing separation (Practical)

One of the most powerful features of MySQL Proxy is to achieve "Read/Write Splitting". The basic principle is to let the master database handle transactional queries, while the slave database handles SELECT queries. Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster.

Jan Kneschke introduced this technique and the connection pool issue in detail in "MySQL Proxy learns R/W Splitting":
In order to achieve read and write separation, we need a connection pool. We only switch to a backend if we have an authenticated connection open to that backend. The MySQL protocol starts with a handshake. When entering the query/return result stage, it is too late to authenticate the new connection. We must ensure that we have enough open connections to keep things functioning properly.
　LUA script to implement read-write separation:

- 读写分离 
　　-- 
　　-- 发送所有的非事务性Select到一个从数据库 
　　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] 
　　-- 需要选择一个拥有空闲连接的从数据库 
　　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 
　　-- 至此，我们找到了一个拥有空闲连接的从数据库 
　　if max_conns_ndx > 0 then 
　　proxy.connection.backend_ndx = max_conns_ndx 
　　end 
　　else 
　　-- 发送到主数据库 
　　end 
　　return proxy.PROXY_SEND_QUERY

Note: This technique can also be used to implement other data distribution strategies, such as sharding.

The above is the content of mysql reading and writing separation (practical article). For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!