OpenResty实战之数据库连接池

  Seves

本文链接:OpenResty实战之数据库连接池

本文主要介绍如何在OpenResty中使用数据库连接池,主要介绍MySQL和Redis连接池。

1.mysql数据库连接池

项目中经常用到多个数据的情况,可以定义多个函数实现不同数据库的调用,可以用数据库名作为函数名的一部分,示例代码如下:

local function mysql_query_connect_abc(sqlstr)
	local mysql = require "resty.mysql"
	local db, err = mysql:new()
	if not db then
	    ngx_log(ngx_ERR,"failed to instantiate mysql: ", err)
	    return nil
	end
	
	db:set_timeout(1000)
	local ok, err, errcode, sqlstate = db:connect{
	  host = "127.0.0.1",
	  port = 3306,
	  database = "abc",
	  user = "root",
	  password = "123456",
	  charset = "utf8",
	}
	
	if not ok then
	    ngx_log(ngx_ERR,"failed to connect: ", err, ": ", errcode, " ", sqlstate)
	    return nil
	end
	
	local res, err, errno, sqlstate = db:query(sqlstr)
	if not res or err then
	    ngx_log(ngx_ERR, "bad result: ", err, ": ", errno, ": ", sqlstate, ".")
	end
	
	local ok, err = db:set_keepalive(10000, 10)
	if not ok then
	    ngx_log(ngx_ERR, "failed to set keepalive: ", err)
	end
	
	return res, err, errno, sqlstate
end


local function mysql_query_connect_cde(sqlstr)
	local mysql = require "resty.mysql"
	local db, err = mysql:new()
	if not db then
	    ngx_log(ngx_ERR,"failed to instantiate mysql: ", err)
	    return nil
	end
	
	db:set_timeout(1000)
	local ok, err, errcode, sqlstate = db:connect{
	  host = "127.0.0.1",
	  port = 3306,
	  database = "cde",
	  user = "root",
	  password = "123456",
	  charset = "utf8",
	}
	
	if not ok then
	    ngx_log(ngx_ERR,"failed to connect: ", err, ": ", errcode, " ", sqlstate)
	    return nil
	end
	
	local res, err, errno, sqlstate = db:query(sqlstr)
	if not res or err then
	    ngx_log(ngx_ERR, "bad result: ", err, ": ", errno, ": ", sqlstate, ".")
	end
	
	local ok, err = db:set_keepalive(10000, 10)
	if not ok then
	    ngx_log(ngx_ERR, "failed to set keepalive: ", err)
	end
	
	return res, err, errno, sqlstate
end

调用方法:

local res, err, errno, sqlstate = mysql_query_connect_abc(sqlstr)
if not res or err then
	ngx_log(ngx_ERR, "mysql insert faild: " .. tostring(err))
else
	local cjson = require "cjson"
	ngx_log(ngx_INFO,"result: ", cjson.encode(res))
end

2.Redis连接池

同上面的mysql方法,示例代码如下:

local function abc_redis_connect()
	local redis = require "resty.redis"
	local red = redis:new()
	red:set_timeout(1000) -- 1 sec
	local ok, err = red:connect("127.0.0.1", 6379)
	if not ok then
		ngx_log(ngx_ERR,"failed to connect: ", err)
		return nil
	end
	local count
	count, err = red:get_reused_times()
	if 0 == count then
		ok, err = red:auth("123456")
		if not ok then
		    ngx_log(ngx_ERR,"failed to auth: ", err)
		    return nil
		end
	elseif err then
		ngx_log(ngx_ERR,"failed to get reused times: ", err)
		return nil
	end
	return red
end

local function cde_redis_connect()
	local redis = require "resty.redis"
	local red = redis:new()
	red:set_timeout(1000) -- 1 sec
	local ok, err = red:connect("127.0.0.1", 6379)
	if not ok then
		ngx_log(ngx_ERR,"failed to connect: ", err)
		return nil
	end
	local count
	count, err = red:get_reused_times()
	if 0 == count then
		ok, err = red:auth("123456")
		if not ok then
		    ngx_log(ngx_ERR,"failed to auth: ", err)
		    return nil
		end
	elseif err then
		ngx_log(ngx_ERR,"failed to get reused times: ", err)
		return nil
	end
	return red
end


调用方法:


local red = abc_redis_connect()

local result = nil
if red and red ~= null and red ~= ngx.null then
	result = red:get(keystr)
	local set_keepaliveok, set_keepaliveerr = red:set_keepalive(10000, 10)
	if not set_keepaliveok then  
		ngx_log(ngx_ERR,"set keepalive error : ", set_keepaliveerr)  
	end 
	if result and result ~= null and result ~= ngx.null then
		ngx_log(ngx_INFO,result)
	else
		ngx_log(ngx_INFO,"result empty")
	end
end

本文链接:https://www.fcblog.top/openresty-database-connection_pool.html

fc
268