使用mysql-proxy 快速實現mysql 集群 讀寫分離
目前較為常見的mysql讀寫分離分為兩種：
1、 基于程序代碼內部實現：在代碼中對select操作分發到從庫；其它操作由主庫執行；這類方法也是目前生產環境應用最廣泛，知名的如DISCUZ X2。優點是性能較好，因為在程序代碼中實現，不需要增加額外的設備作為硬件開支。缺點是需要開發人員來實現，運維人員無從下手。
2、 基于中間代理層實現：我們都知道代理一般是位于客戶端和服務器之間，代理服務器接到客戶端請求后通過判斷然后轉發到后端數據庫。在這有兩個代表性程序
mysql-proxy：mysql-proxy為mysql開源項目，通過其自帶的lua腳本進行sql判斷，雖然是mysql官方產品，但是mysql官方并不建議將mysql-proxy用到生產環境。
amoeba：由陳思儒開發，作者曾就職于阿里巴巴，現就職于盛大。該程序由java語言進行開發，目前只聽說阿里巴巴將其用于生產環境。另外，此項目嚴重缺少維護和推廣（作者有個官方博客，很多用戶反饋的問題發現作者不理睬）
經 過上述簡單的比較，通過程序代碼實現mysql讀寫分離自然是一個不錯的選擇。但是并不是所有的應用都適合在程序代碼中實現讀寫分離，像大型SNS、 B2C這類應用可以在代碼中實現，因為這樣對程序代碼本身改動較小；像一些大型復雜的java應用，這種類型的應用在代碼中實現對代碼改動就較大了。所 以，像這種應用一般就會考慮使用代理層來實現。
下面我們看一下如何搭建mysql-proxy來實現mysql讀寫分離
環境拓撲如下：
關于mysql、mysql主從的搭建，在此不再演示，如下的操作均在mysql-proxy（192.168.1.200）服務器進行
一、安裝mysql-proxy
1、安裝lua (mysql-proxy需要使用lua腳本進行數據轉發)
tar zxvf lua-5.1.4.tar.gz
cd lua-5.1.4
vi Makefile，修改INSTALL_TOP= /usr/local/lua
make posix
make install
2、安裝libevent
tar zxvf libevent-2.0.8-rc.tar.gz
cd libevent-2.0.8-rc
./configure --prefix=/usr/local/libevent
make && make install
3、安裝check
tar zxvf check-0.9.8.tar.gz
cd check-0.9.8
./configure && make && make install
4、安裝mysql客戶端
tar zxvf mysql-5.0.92.tar.gz
cd mysql-5.0.92
./configure --without-server && make && make install
5、設置環境變量 （安裝mysql-proxy所需變量）
vi /etc/profile
export LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl" LDFLAGS="-L/usr/local/libevent/lib -lm"
export CPPFLAGS="-I/usr/local/libevent/include"
export CFLAGS="-I/usr/local/libevent/include"
source /etc/profile
6、安裝mysql-proxy
tar zxvf mysql-proxy-0.6.0.tar.gz
cd mysql-proxy-0.6.0
./configure --prefix=/usr/local/mysql-proxy --with-mysql --with-lua
make && make install
7、啟動mysql-proxy
本次對兩臺數據庫實現了讀寫分離；mysql-master為可讀可寫，mysql-slave為只讀
/usr/local/mysql-proxy/sbin/mysql-proxy --proxy-backend-addresses=192.168.1.201:3306 --proxy-read-only-backend-addresses=192.168.1.202:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua &
注：如果正常情況下啟動后終端不會有任何提示信息，mysql-proxy啟動后會啟動兩個端口4040和4041，4040用于SQL轉發，4041用于管理mysql-proxy。如有多個mysql-slave可以依次在后面添加
二、測試
1、連接測試
因為默認情況下mysql數據庫不允許用戶在遠程連接
mysql>grant all privileges on . to identified by '123456';
mysql>flush privileges;
客戶端連接
mysql -uroot -p123456 -h192.168.1.200 -P4040
2、讀寫分離測試
為了測試出mysql讀寫分離的真實性，在測試之前，需要開啟兩臺mysql的log功能，然后在mysql-slave服務器停止復制
① 、在兩臺mysql配置文件my.cnf中加入log=query.log，然后重啟 </p>
② 、在mysql-slave上執行SQL語句stop slave
③ 、在兩臺mysql上執行#tail -f /usr/local/mysql/var/query.log
④ 、在客戶端上連接mysql（三個連接以上），然后執行create、select等SQL語句，觀察兩臺mysql的日志有何變化
注：生產環境中除了進行程序調試外，其它不要開啟mysql查詢日志，因為查詢日志記錄了客戶端的所有語句，頻繁的IO操作將會導致mysql整體性能下降
總 結：在上述環境中，mysql-proxy和mysql-master、mysql-slave三臺服務器均存在單點故障。如果在可用性要求較高的場合， 單點隱患是絕對不允許的。為了避免mysql-proxy單點隱患有兩種方法，一種方法是mysql-proxy配合keepalived做雙機，另一種 方法是將mysql-proxy和應用服務安裝到同一臺服務器上；為了避免mysql-master單點故障可以使用DRBD+heartbear做雙 機；避免mysql-slave單點故障增加多臺mysql-slave即可，因為mysql-proxy會自動屏蔽后端發生故障的mysql- slave。
附: mysql-proxy LUA 讀寫分離腳本代碼:
--[[
--
-- author : KDr2
-- version 0.01
-- SYNOPSIS:
--- 1.維護了一個連接池
--- 2.讀寫分離，簡單的將select開頭的語句放到slave上執行
--- 3.事務支持，所有事務放到master上執行，事務中不更改連接
--- 4.簡單日志
--
--]]
--- config vars
local min_idle_connections = 4
local max_idle_connections = 8
local log_level=1
local encoding="utf8"
--- end of config
-- 事務標識，在事務內不歸還連接
local transaction_flags={}
setmetatable(transaction_flags,{__index=function() return 0 end})
-- log system
log={
level={debug=1,info=2,warn=3,error=4},
funcs={"debug","info","warn","error"},
}
function log.log(level,m)
if level >= log_level then
local msg="[" .. os.date("%Y-%m-%d %X") .."] ".. log.funcs[level] .. ": " .. tostring(m)
print(msg) -- TODO write msg into a log file.
end
end
for i,v in ipairs(log.funcs) do
log[v]=function(m) log.log(log.level[v],m) end
end
-- connect to server
function connect_server()
log.info(" starting connect_server ... ")
local least_idle_conns_ndx = 0
local least_idle_conns = 0
for i = 1, #proxy.backends do
local s = proxy.backends[i]
local pool = s.pool
local cur_idle = pool.users[""].cur_idle_connections
log.debug("[".. s.address .."].connected_clients = " .. s.connected_clients)
log.debug("[".. s.address .."].idling_connections = " .. cur_idle)
log.debug("[".. s.address .."].type = " .. s.type)
log.debug("[".. s.address .."].state = " .. s.state)
if s.state ~= proxy.BACKEND_STATE_DOWN then
-- try to connect to each backend once at least
if cur_idle == 0 then
proxy.connection.backend_ndx = i
log.info("server [".. proxy.backends[i].address .."] open new connection")
return
end
-- try to open at least min_idle_connections
if least_idle_conns_ndx == 0 or
( cur_idle < min_idle_connections and
cur_idle < least_idle_conns ) then
least_idle_conns_ndx = i
least_idle_conns = cur_idle
end
end
end
if least_idle_conns_ndx > 0 then
proxy.connection.backend_ndx = least_idle_conns_ndx
end
if proxy.connection.backend_ndx > 0 then
local s = proxy.backends[proxy.connection.backend_ndx]
local pool = s.pool
local cur_idle = pool.users[""].cur_idle_connections
if cur_idle >= min_idle_connections then
-- we have 4 idling connections in the pool, that's good enough
log.debug("using pooled connection from: " .. proxy.connection.backend_ndx)
return proxy.PROXY_IGNORE_RESULT
end
end
-- open a new connection
log.info("opening new connection on: " .. proxy.backends[proxy.connection.backend_ndx].address)
end
---
-- auth.packet is the packet
function read_auth_result( auth )
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
-- 連接正常
proxy.connection.backend_ndx = 0
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
-- we received either a
-- * MYSQLD_PACKET_ERR and the auth failed or
-- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
log.error("(read_auth_result) ... not ok yet");
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
log.error("auth failed!")
end
end
---
-- read/write splitting
function read_query( packet )
log.debug("[read_query]")
log.debug("authed backend = " .. proxy.connection.backend_ndx)
log.debug("used db = " .. proxy.connection.client.default_db)
if packet:byte() == proxy.COM_QUIT then
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
}
return proxy.PROXY_SEND_RESULT
end
if proxy.connection.backend_ndx == 0 then
local is_read=(string.upper(packet:sub(2))):match("^SELECT")
local target_type=proxy.BACKEND_TYPE_RW
if is_read then target_type=proxy.BACKEND_TYPE_RO end
for i = 1, #proxy.backends do
local s = proxy.backends[i]
local pool = s.pool
local cur_idle = pool.users[proxy.connection.client.username].cur_idle_connections
if cur_idle > 0 and
s.state ~= proxy.BACKEND_STATE_DOWN and
s.type == target_type then
proxy.connection.backend_ndx = i
break
end
end
end
-- sync the client-side default_db with the server-side default_db
if proxy.connection.server and proxy.connection.client.default_db ~= proxy.connection.server.default_db then
local server_db=proxy.connection.server.default_db
local client_db=proxy.connection.client.default_db
local default_db= (#client_db > 0) and client_db or server_db
if #default_db > 0 then
proxy.queries:append(2, string.char(proxy.COM_INIT_DB) .. default_db)
proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "set names '" .. encoding .."'")
log.info("change database to " .. default_db);
end
end
if proxy.connection.backend_ndx > 0 then
log.debug("Query[" .. packet:sub(2) .. "] Target is [" .. proxy.backends[proxy.connection.backend_ndx].address .."]")
end
proxy.queries:append(1, packet)
return proxy.PROXY_SEND_QUERY
end
---
-- as long as we are in a transaction keep the connection
-- otherwise release it so another client can use it
function read_query_result( inj )
local res = assert(inj.resultset)
local flags = res.flags
if inj.id ~= 1 then
-- ignore the result of the USE
return proxy.PROXY_IGNORE_RESULT
end
is_in_transaction = flags.in_trans
if flags.in_trans then
transaction_flags[proxy.connection.server.thread_id] = transaction_flags[proxy.connection.server.thread_id] + 1
elseif inj.query:sub(2):lower():match("^%s*commit%s*$") or inj.query:sub(2):lower():match("^%s*rollback%s*$") then
transaction_flags[proxy.connection.server.thread_id] = transaction_flags[proxy.connection.server.thread_id] - 1
if transaction_flags[proxy.connection.server.thread_id] < 0 then transaction_flags[proxy.connection.server.thread_id] = 0 end
end
log.debug("transaction res : " .. tostring(transaction_flags[proxy.connection.server.thread_id]));
if transaction_flags[proxy.connection.server.thread_id]==0 or transaction_flags[proxy.connection.server.thread_id] == nil then
-- isnot in a transaction, need to release the backend
proxy.connection.backend_ndx = 0
end
end
-- close the connections if we have enough connections in the pool
--
-- @return nil - close connection
-- IGNORE_RESULT - store connection in the pool
function disconnect_client()
log.debug("[disconnect_client]")
if proxy.connection.backend_ndx == 0 then
for i = 1, #proxy.backends do
local s = proxy.backends[i]
local pool = s.pool
local cur_idle = pool.users[proxy.connection.client.username].cur_idle_connections
if s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle > max_idle_connections then
-- try to disconnect a backend
proxy.connection.backend_ndx = i
log.info("[".. proxy.backends[i].address .."] closing connection, idling: " .. cur_idle)
return
end
end
return proxy.PROXY_IGNORE_RESULT
end
end