最近刚刚接手同事的OpenResty的项目，发现对mysql，redis的操作没有用连接池，故对此进行了改造。
MYSQL
主要是通过mysql_pool.lua 和 dbutil.lua 来封装对数据库的操作
mysql_pool.lua:
1 module("mysql_pool", package.seeall) 2 3 local dbConfig = require"config" 4 local mysql = require("resty.mysql") 5 6 local mysql_pool = {} 7 8 --[[ 9 先从连接池取连接,如果没有再建立连接. 10 返回: 11 false,出错信息. 12 true,数据库连接 13 --]] 14 function mysql_pool:get_connect() 15 if ngx.ctx[mysql_pool] then 16 return true, ngx.ctx[mysql_pool] 17 end 18 19 local client, errmsg = mysql:new() 20 if not client then 21 return false, "mysql.socket_failed: " .. (errmsg or "nil") 22 end 23 24 client:set_timeout(10000) --10秒 25 26 local options = { 27 host = dbConfig.DBHOST, 28 port = dbConfig.DBPORT, 29 user = dbConfig.DBUSER, 30 password = dbConfig.DBPASSWORD, 31 database = dbConfig.DBNAME 32 } 33 34 local result, errmsg, errno, sqlstate = client:connect(options) 35 if not result then 36 return false, "mysql.cant_connect: " .. (errmsg or "nil") .. ", errno:" .. (errno or "nil") .. 37 ", sql_state:" .. (sqlstate or "nil") 38 end 39 40 local query = "SET NAMES " .. dbConfig.DEFAULT_CHARSET 41 local result, errmsg, errno, sqlstate = client:query(query) 42 if not result then 43 return false, "mysql.query_failed: " .. (errmsg or "nil") .. ", errno:" .. (errno or "nil") .. 44 ", sql_state:" .. (sqlstate or "nil") 45 end 46 47 ngx.ctx[mysql_pool] = client 48 return true, ngx.ctx[mysql_pool] 49 end 50 51 --[[ 52 把连接返回到连接池 53 用set_keepalive代替close() 将开启连接池特性,可以为每个nginx工作进程，指定连接最大空闲时间，和连接池最大连接数 54 --]] 55 function mysql_pool:close() 56 if ngx.ctx[mysql_pool] then 57 ngx.ctx[mysql_pool]:set_keepalive(60000, 1000) 58 ngx.ctx[mysql_pool] = nil 59 end 60 end 61 62 --[[ 63 查询 64 有结果数据集时返回结果数据集 65 无数据数据集时返回查询影响 66 返回: 67 false,出错信息,sqlstate结构. 68 true,结果集,sqlstate结构. 69 --]] 70 function mysql_pool:query(sql, flag) 71 local ret, client = self:get_connect(flag) 72 if not ret then 73 return false, client, nil 74 end 75 76 local result, errmsg, errno, sqlstate = client:query(sql) 77 self:close() 78 79 if not result then 80 errmsg = concat_db_errmsg("mysql.query_failed:", errno, errmsg, sqlstate) 81 return false, errmsg, sqlstate 82 end 83 84 return true, result, sqlstate 85 end 86 87 return mysql_pool
dbutil.lua
1 module("dbutil", package.seeall) 2 local mysql_pool = require("mysql_pool") 3 4 function query(sql) 5 6 local ret, res, _ = mysql_pool:query(sql) 7 if not ret then 8 ngx.log(ngx.ERR, "query db error. res: " .. (res or "nil")) 9 return nil 10 end 11 12 return res 13 end 14 15 function execute(sql) 16 17 local ret, res, sqlstate = mysql_pool:query(sql) 18 if not ret then 19 ngx.log(ngx.ERR, "mysql.execute_failed. res: " .. (res or ‘nil‘) .. ",sql_state: " .. (sqlstate or ‘nil‘)) 20 return -1 21 end 22 23 return res.affected_rows 24 end
REDIS
redis_pool.lua:
1 module("redis_pool", package.seeall) 2 3 local redisConfig = require"config" 4 local redis = require("resty.redis") 5 6 local redis_pool = {} 7 8 --[[ 9 先从连接池取连接,如果没有再建立连接. 10 返回: 11 false,出错信息. 12 true,redis连接 13 --]] 14 function redis_pool:get_connect() 15 if ngx.ctx[redis_pool] then 16 return true, ngx.ctx[redis_pool] 17 end 18 19 local client, errmsg = redis:new() 20 if not client then 21 return false, "redis.socket_failed: " .. (errmsg or "nil") 22 end 23 24 client:set_timeout(10000) --10秒 25 26 local result, errmsg = client:connect(redisConfig.REDIS_HOST, redisConfig.REDIS_PORT) 27 if not result then 28 return false, errmsg 29 end 30 31 ngx.ctx[redis_pool] = client 32 return true, ngx.ctx[redis_pool] 33 end 34 35 function redis_pool:close() 36 if ngx.ctx[redis_pool] then 37 ngx.ctx[redis_pool]:set_keepalive(60000, 300) 38 ngx.ctx[redis_pool] = nil 39 end 40 end 41 42 return redis_pool