How to use "mysql-proxy" to achieve read-write separation

Mysql-proxy is a program between MySQL database client and server, which supports the embedded scripting language Lua. This agent can be used to analyze, monitor, and transform (transform) communication data, which supports a very wide range of usage scenarios:

• Load balancing and failover processing

• Query analysis and logging

· SQL macros (SQL macros)

• Query rewrite (rewriting)

• Execute shell command

One of the most powerful features of MySQL Proxy is the implementation of read-write separation (Read/write splitting). 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:

-- 读写分离
--
-- 发送所有的非事务性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 fragmentation (Sharding).