文档章节

使用mysql-proxy 快速实现mysql 集群 读写分离

JavaOlder
 JavaOlder
发布于 2012/12/02 00:56
字数 1962
阅读 7083
收藏 69

使用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,然后重启

②在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
<default_db>
      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

© 著作权归作者所有

JavaOlder
粉丝 84
博文 55
码字总数 39398
作品 0
广州
产品经理
私信 提问
加载中

评论(7)

ZhouLS
ZhouLS
我再用这个软件时经常出现锁表问题,请问楼主有没有解决办法。谢谢了。
JavaOlder
JavaOlder 博主

引用来自“lengspring”的评论

引用来自“JavaNewPer”的评论

引用来自“lengspring”的评论

这个在生产环境上靠谱吗?

你那边有具体的需求?

目前生产上用的是mmm_mysql,mysql proxy还没试过,不过看网上些资料,貌似mysql proxy还不成熟~~~~

你说的对,这个还不太成熟。mmm_mysql我有一篇博文介绍到了。你可以对比一下http://my.oschina.net/barter/blog/89858
lengspring
lengspring

引用来自“JavaNewPer”的评论

引用来自“lengspring”的评论

这个在生产环境上靠谱吗?

你那边有具体的需求?

mmm_mysql感觉也不咋靠谱,发现状态老变来变去~~~
lengspring
lengspring

引用来自“JavaNewPer”的评论

引用来自“lengspring”的评论

这个在生产环境上靠谱吗?

你那边有具体的需求?

目前生产上用的是mmm_mysql,mysql proxy还没试过,不过看网上些资料,貌似mysql proxy还不成熟~~~~
JavaOlder
JavaOlder 博主

引用来自“lengspring”的评论

这个在生产环境上靠谱吗?

你那边有具体的需求?
lengspring
lengspring
这个在生产环境上靠谱吗?
baobao777
baobao777
非常好!
数据库读写分离研究

如何提高大型网站的访问速度,根据理解和经验一般常用的方法如SQL优化、缓存、集群等等,NewEgg的专家提示说做过大型网站的话应该知道数据库读写分离的。 读写分离(Read/Write Splitting)”...

hans汉斯
2010/01/13
385
0
OneProxy实现MySQL读写分离与负载均衡

OneProxy实现MySQL读写分离与负载均衡 简介 Part1:写在最前 OneProxy平民软件完全自主开发的分布式数据访问层,帮助用户在MySQL/PostgreSQL集群上快速搭建支持分库分表的分布式数据库中间件,...

dbapower
2016/12/21
0
0
mysql集群(二)

4、mysql-proxy完成负载均衡与读写分离 1、基于程序代码内部实现 在代码中对select操作分发到从库;其它操作由主库执行;这类方法也是目前生产环境应用最广泛,知名的如DISCUZ X2。优点是性能...

我心中有猛狗
2016/05/30
67
2
Mysql-proxy实现MariaDB读写分离

Mysql Proxy 简介 MySQL Proxy是一个处于你的client端和MySQL server端之间的简单程序,它可以监测、分析、或改变它们的通信。它使用灵活,没有限制,常见的用途包括:负载平衡,故障、查询分...

jungege1216
2014/05/14
0
0
用MySQL-Proxy实现读写分离

MySQL-Proxy, 6月份发布的MySQL-Proxy是处在你的MySQL数据库客户和服务端之间的程序,它还支持嵌入性脚本语言Lua。这个代理可以用来分析、监控和变换(transform)通信数据,它支持非常广泛的...

oschina
2008/09/14
2.6K
4

没有更多内容

加载失败,请刷新页面

加载更多

centos7上部署dubbo管理控制台dubbo-admin

centos7上部署dubbo管理控制台dubbo-admin 1 准备工作 服务器:系统centos7, 内存4G, 存储60G, ip 192.168.159.128 软件环境: 安装有jdk1.8, 具体安装方式参见《centos7上安装jdk1.8》博...

flygrk
18分钟前
3
0
工作中一些原则体会

尽可能让一切变得简单,用最简单的方式完成工作 能用最少的概念,最精简易懂的概念模型来抽象系统,多一个概念就多一份别人了解系统以及维护系统的复杂度,别人也会质疑多一个概念的意义所在...

小强的进阶之路
21分钟前
4
0
Android -------- kotlin插件神器Json直接生成javaBean

这是一个data class从JSON字符串生成Kotlin 的插件,换句话说,是一个将JSON字符串转换为Kotlin data class(Json到Kotlin)的插件 在使用Kotlin进行开发的时候,我们需要经常对Json数据做解析...

切切歆语
47分钟前
31
0
1、Spring注解开发,第一天

第一天:Spring annotation开发 目录:1、@Configuration与@Bean给容器注册组件 2、@ConponentScan自动扫描注解 一、@Configuration与@Bean给容器注册组件 1、旧版本中创建配置文件和Bean //...

有一个小阿飞
54分钟前
24
0
斯坦福博弈论笔记整理活动的任务已重新划分,望周知

参与方式:https://github.com/apachecn/stanford-game-theory-notes-zh/blob/master/CONTRIBUTING.md 整体进度:https://github.com/apachecn/stanford-game-theory-notes-zh/issues/1 项目......

ApacheCN_飞龙
55分钟前
23
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部