华为官方手表网站,wordpress文章图片链接,购买的网站怎么看网站空间大小,在线推广企业网站的方法有为什么80%的码农都做不了架构师#xff1f; 使用mysql-proxy 快速实现mysql 集群 读写分离 目前较为常见的mysql读写分离分为两种#xff1a; 1、 基于程序代码内部实现#xff1a;在代码中对select操作分发到从库#xff1b;其它操作由主库执行#xff1… 为什么80%的码农都做不了架构师 使用mysql-proxy 快速实现mysql 集群 读写分离 目前较为常见的mysql读写分离分为两种 1、 基于程序代码内部实现在代码中对select操作分发到从库其它操作由主库执行这类方法也是目前生产环境应用最广泛知名的如DISCUZ X2。优点是性能较好因为在程序代码中实现不需要增加额外的设备作为硬件开支。缺点是需要开发人员来实现运维人员无从下手。 2、 基于中间代理层实现我们都知道代理一般是位于客户端和服务器之间代理服务器接到客户端请求后通过判断然后转发到后端数据库。在这有两个代表性程序 mysql-proxymysql-proxy为mysql开源项目通过其自带的lua脚本进行sql判断虽然是mysql官方产品但是mysql官方并不建议将mysql-proxy用到生产环境。 amoeba由陈思儒开发作者曾就职于阿里巴巴现就职于盛大。该程序由java语言进行开发目前只听说阿里巴巴将其用于生产环境。另外此项目严重缺少维护和推广作者有个官方博客很多用户反馈的问题发现作者不理睬 经过上述简单的比较通过程序代码实现mysql读写分离自然是一个不错的选择。但是并不是所有的应用都适合在程序代码中实现读写分离像大型SNS、B2C这类应用可以在代码中实现因为这样对程序代码本身改动较小像一些大型复杂的java应用这种类型的应用在代码中实现对代码改动就较大了。所以像这种应用一般就会考虑使用代理层来实现。 下面我们看一下如何搭建mysql-proxy来实现mysql读写分离 环境拓扑如下 关于mysql、mysql主从的搭建在此不再演示如下的操作均在mysql-proxy192.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-addresses192.168.1.201:3306 --proxy-read-only-backend-addresses192.168.1.202:3306 --proxy-lua-script/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua 注如果正常情况下启动后终端不会有任何提示信息mysql-proxy启动后会启动两个端口4040和40414040用于SQL转发4041用于管理mysql-proxy。如有多个mysql-slave可以依次在后面添加 二、测试 1、连接测试 因为默认情况下mysql数据库不允许用户在远程连接 mysqlgrant all privileges on *.* to identified by 123456; mysqlflush privileges; 客户端连接 #mysql -uroot -p123456 -h192.168.1.200 -P4040 2、读写分离测试 为了测试出mysql读写分离的真实性在测试之前需要开启两台mysql的log功能然后在mysql-slave服务器停止复制 ① 在两台mysql配置文件my.cnf中加入logquery.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单点故障可以使用DRBDheartbear做双机避免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 varslocal min_idle_connections 4local max_idle_connections 8local log_level1local encodingutf8--- end of config -- 事务标识在事务内不归还连接local transaction_flags{}setmetatable(transaction_flags,{__indexfunction() return 0 end}) -- log systemlog{ level{debug1,info2,warn3,error4}, 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 serverfunction 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, thats 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 packetfunction 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!) endend --- -- read/write splittingfunction 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_typeproxy.BACKEND_TYPE_RW if is_read then target_typeproxy.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_dbproxy.connection.server.default_db local client_dbproxy.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.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 endend --- -- close the connections if we have enough connections in the pool---- return 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 转载于:https://my.oschina.net/barter/blog/93354
