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

发表于
阅读时长 ≈ 11

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

目前较为常见的mysql读写分离分为两种：
  1. 基于程序代码内部实现：在代码中对select操作分发到从库；其它操作由主库执行；这类方法也是目前生产环境应用最广泛，知名的如DISCUZ X2。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支。缺点是需要开发人员来实现，运维人员无从下手。
  1. 基于中间代理层实现：我们都知道代理一般是位于客户端和服务器之间，代理服务器接到客户端请求后通过判断然后转发到后端数据库。在这有两个代表性程序。

mysql-proxy： mysql-proxy为mysql开源项目，通过其自带的lua脚本进行sql判断，虽然是mysql官方产品，但是mysql官方并不建议将mysql-proxy用到生产环境。

amoeba： 由陈思儒开发，作者曾就职于阿里巴巴，现就职于盛大。该程序由java语言进行开发，目前只听说阿里巴巴将其用于生产环境。另外，此项目严重缺少维护和推广（作者有个官方博客，很多用户反馈的问题发现作者不理睬）

经过上述简单的比较，通过程序代码实现mysql读写分离自然是一个不错的选择。但是并不是所有的应用都适合在程序代码中实现读写分离，像大型SNS、B2C这类应用可以在代码中实现，因为这样对程序代码本身改动较小；像一些大型复杂的java应用，这种类型的应用在代码中实现对代码改动就较大了。所以，像这种应用一般就会考虑使用代理层来实现。

下面我们看一下如何搭建mysql-proxy来实现mysql读写分离。

环境拓扑如下：

image

关于mysql、mysql主从的搭建，在此不再演示，如下的操作均在mysql-proxy（192.168.1.200）服务器进行。

1. 安装mysql-proxy

i. 安装lua (mysql-proxy需要使用lua脚本进行数据转发) 

1
2
3
4
5
6
7
tar zxvf 
lua-5.1.4.tar.gz 
cd lua-5.1.4 
vi Makefile，修改INSTALL_TOP= 
/usr/local/lua 
make posix 
make install

ii. 安装libevent 

1
2
3
4
5
tar zxvf libevent-2.0.8-rc.tar.gz 
cd libevent-2.0.8-rc 
./configure 
--prefix=/usr/local/libevent 
make && make install

iii. 安装check

1
2
3
tar zxvf check-0.9.8.tar.gz 
cd check-0.9.8 
./configure && make && make install

iv. 安装mysql客户端 

1
2
3
tar zxvf mysql-5.0.92.tar.gz 
cd mysql-5.0.92 
./configure --without-server && make && make install

v. 设置环境变量 （安装mysql-proxy所需变量） 

1
2
3
4
5
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

vi. 安装mysql-proxy

1
2
3
4
5
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

vii. 启动mysql-proxy

本次对两台数据库实现了读写分离；mysql-master为可读可写，mysql-slave为只读

1
2
3
4
/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可以依次在后面添加

2. 测试

i. 连接测试

因为默认情况下mysql数据库不允许用户在远程连接

1
2
3
4
mysql>grant 
all privileges on *.* to identified by '123456'; 

mysql>flush privileges;

客户端连接

1
mysql -uroot -p123456 -h192.168.1.200 -P4040

ii. 读写分离测试

为了测试出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 读写分离脚本代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
--[[
--
-- 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