http://my.oschina.net/barter/blog/93354?fromerr=6xlevmHC
目前较为常见的mysql读写分离分为两种：
1、 基于程序代码内部实现：在代码中对select操作分发到从库；其它操作由主库执行；这类方法也是目前生产环境应用最广泛，知名的如DISCUZ X2。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支。缺点是需要开发人员来实现，运维人员无从下手。2、 基于中间代理层实现：我们都知道代理一般是位于客户端和服务器之间，代理服务器接到客户端请求后通过判断然后转发到后端数据库。在这有两个代表性程序
②在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 = 4local max_idle_connections = 8local log_level=1local encoding="utf8"--- end of config-- 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. endendfor i,v in ipairs(log.funcs) do log[v]=function(m) log.log(log.level[v],m) endend-- 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_connectionslog.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 endif 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_connectionsif 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!") endendif packet:byte() == proxy.COM_QUIT then
proxy.response = { type = proxy.MYSQLD_PACKET_OK, } return proxy.PROXY_SEND_RESULT endif 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_QUERYend---
-- as long as we are in a transaction keep the connection-- otherwise release it so another client can use itfunction read_query_result( inj ) local res = assert(inj.resultset) local flags = res.flagsif inj.id ~= 1 then
-- ignore the result of the USE <default_db> return proxy.PROXY_IGNORE_RESULT end is_in_transaction = flags.in_transif 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 endend---
-- close the connections if we have enough connections in the pool---- nil - close connection -- IGNORE_RESULT - store connection in the poolfunction 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 endend