介绍
主要介绍了openresty连接mysql并进行简单的单表的增删查改功能
mysql连接lua简单封装
对于mysql连接的创建和关闭我们进行了简单的封装
libs.mysql.lua
local mysql = require "resty.mysql"
local db_config = ngx.shared.db_config;
local _M = {}
local config = {
host = db_config:get("mysql.host"),
port = db_config:get("mysql.port"),
database = db_config:get("mysql.database"),
user = db_config:get("mysql.user"),
password = db_config:get("mysql.password"),
max_package_size = db_config:get("mysql.max_package_size"),
}
function _M.new( self )
local db, err = mysql:new()
if not db then
ngx.log(ngx.ERR, "failed to instantiate mysql: ", err)
return nil
end
-- 1 sec
db:set_timeout(1000)
local ok, err, errcode, sqlstate = db:connect(config)
if not ok then
ngx.log(ngx.ERR, "failed to connect: ", err, errcode, sqlstate)
return nil
end
ngx.log(ngx.ERR, "connected to mysql.")
return db
end
function _M.close( self )
local sock = self.sock
if not sock then
return nil, "not initialized"
end
if self.subscribed then
return nil, "subscribed state"
end
-- put it into the connection pool of size 100,
-- with 10 seconds max idle timeout
local ok, err = self.sock:set_keepalive(10000, 100)
if not ok then
ngx.log(ngx.ERR, "failed to set keepalive:", err)
return
end
end
return _M
复制代码
其中mysql配置参数db_config单独放在了一个配置文件中,如下所示:
mysql_conf.properties.
{
"mysql.host" : "127.0.0.1",
"mysql.port" : 3306,
"mysql.database" : "test",
"mysql.user" : "root",
"mysql.password" : "root",
"mysql.max_package_size" : 1024,
"mysql.charset" : "utf-8",
"table": "user"
}
复制代码
配置文件会在openresty启动的时候加载,我们需要在nginx.conf上配置:
init_by_lua_block {
require "init";
}
复制代码
其中init为init.lua文件(lua/init.lua),内容如下:
local cjson = require("cjson");
local dbConfig = ngx.shared.db_config;
if dbConfig:get('isload') then
return
end
local confFile = io.open("/Users/Desktop/tool/openresty-test/src/common/config/mysql_conf.properties", "r");
local confStr = confFile:read("*a");
confFile:close();
local confJson = cjson.decode(confStr);
ngx.log(ngx.ERR, ">>>>>>>>>>>>>Begin Config>>>>>>>>>>>");
for k,v in pairs(confJson) do
dbConfig:set(k, v, 0)
ngx.log(ngx.ERR, "key:" .. k .. ", value:" .. v);
end
ngx.log(ngx.ERR, "<<<<<<<<<<<<<<<<<<<<<<<<<<<End Config>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
dbConfig:set('isload', 1)
复制代码
简单增删改查操作
如下所示进行对user表的简单增删改查:
local cjson = require("cjson");
local db_config = ngx.shared.db_config;
local args = ngx.req.get_uri_args()
local action = args['action']
local mysql = require("libs.mysql")
local db = mysql:new()
-- 查询列表操作
function lists()
local data = {}
ngx.req.read_body()
local posts = ngx.req.get_post_args()
local page, pagesize, offset = 0, 15, 0
if posts.page then
page = posts.page
end
if posts.pagesize then
pagesize = posts.pagesize
end
if page > 1 then
offset = (page -1)*pagesize
end
local res, err, errno, sqlstate = db:query('SELECT * FROM `'.. db_config:get("table") ..'` LIMIT '..offset..','..pagesize)
db:close()
if not res then
ngx.say(cjson.encode({code=200, message=err, data=nil}))
else
ngx.say(cjson.encode({code=200, message="", data=res}))
end
end
-- 添加操作
function add()
ngx.req.read_body()
local data = ngx.req.get_post_args()
if data.name ~= nil then
local sql = 'INSERT INTO '..db_config:get("table")..'(name) VALUES ("'..data.name..'")';
local res, err, errno, sqlstate = db:query(sql)
db:close()
if not res then
ngx.say(cjson.encode({code=501, message="添加失败"..err..';sql:'..sql, data=nil}))
else
ngx.say(cjson.encode({code=200, message="添加成功", data=res.insert_id}))
end
else
ngx.say(cjson.encode({code=501, message="参数不对", data=nil}))
end
end
-- 详情页
function detail()
ngx.req.read_body()
local post_args = ngx.req.get_post_args()
if post_args.id ~= nil then
local data, err, errno, sqlstate = db:query('SELECT * FROM '..db_config:get("table")..' WHERE id='..post_args.id..' LIMIT 1', 1)
db:close()
local res = {}
if data ~= nil then
res.code = 200
res.message = '请求成功'
res.data = data[1]
else
res.code = 502
res.message = '没有数据'
res.data = data
end
ngx.say(cjson.encode(res))
else
ngx.say(cjson.encode({code = 501, message = '参数错误', data = nil}))
end
end
-- 删除操作
function delete()
ngx.req.read_body()
local data = ngx.req.get_post_args()
if data.id ~= nil then
local res, err, errno, sqlstate = db:query('DELETE FROM '..db_config:get("table")..' WHERE id='..data.id)
db:close()
if not res or res.affected_rows < 1 then
ngx.say(cjson.encode({code = 504, message = '删除失败', data = nil}))
else
ngx.say(cjson.encode({code = 200, message = '修改成功', data = nil}))
end
else
ngx.say(cjson.encode({code = 501, message = '参数错误', data = nil}))
end
end
-- 修改操作
function update()
ngx.req.read_body()
local post_args = ngx.req.get_post_args()
if post_args.id ~= nil and post_args.name ~= nil then
local res, err, errno, sqlstate = db:query('UPDATE '..db_config:get("table")..' SET `name` = "'..post_args.name..'" WHERE id='..post_args.id)
db:close()
if not res or res.affected_rows < 1 then
ngx.say(cjson.encode({code = 504, message = '修改失败', data = nil}));
else
ngx.say(cjson.encode({code = 200, message = '修改成功', data = nil}))
end
else
ngx.say(cjson.encode({code = 501, message = '参数错误', data = nil}));
end
end
if action == 'lists' then
lists()
elseif action == 'detail' then
detail()
elseif action == 'add' then
add()
elseif action == 'delete' then
delete()
elseif action == 'update' then
update()
end
复制代码
其中需要注意,上面的db:close()方法其实并不是resty.mysql的close方法,这个方法在libs.mysql.lua文件中重写为调用set_keepalive方法。还有就是编码的问题,数据库,nginx.conf的配置参数中需要统一设置为utf8的编码,防止因不一致而乱码。
nginx.conf配置
我们把nginx.conf配置配置在lua/目录下:
nginx.conf
worker_processes 1;
error_log logs/error.log;
events {
worker_connections 1024;
}
http {
init_by_lua_block {
require "init";
}
lua_package_path "$prefix/lua/?.lua;$prefix/libs/?.lua;;";
lua_code_cache off;
lua_shared_dict db_config 1m;
server {
server_name localhost;
listen 8080;
charset utf-8;
set $LESSON_ROOT lua/;
error_log logs/error.log;
access_log logs/access.log;
location /mysql {
default_type text/html;
content_by_lua_file $LESSON_ROOT/operation.lua;
}
}
}
复制代码
上面配置完成以后便可以通过如下curl来进行数据库的增删改查操作:
curl -H "Content-Type: application/json" -X POST -d '{"name": "唐僧"}' "http://127.0.0.1:8080/mysql?action=lists"
curl -H "Content-Type: application/json" -X POST -d 'id=1' "http://127.0.0.1:8080/mysql?action=detail"
curl -H "Content-Type: application/json" -X POST -d 'name=唐三藏' "http://127.0.0.1:8080/mysql?action=add"
curl -H "Content-Type: application/json" -X POST -d 'name=武警&id=3' "http://127.0.0.1:8080/mysql?action=update"
curl -H "Content-Type: application/json" -X POST -d 'id=3' "http://127.0.0.1:8080/mysql?action=delete"
复制代码
根据参数不同路由到不同机器
我们需要在nginx.conf中配置路由
添加如下配置:
location /hello {
set $backend '';
rewrite_by_lua_file lua/router.lua;
proxy_pass http://$backend;
}
复制代码
可以看到$backend即为我们需要请求的地址,我们只要在router.lua中根据相关参数来判定
router.lua
local cjson = require "cjson"
ngx.req.read_body()
local body_data = ngx.req.get_body_data()
local unjson = cjson.decode(body_data)
if unjson["routerId"] == 1 then
res = '127.0.0.1:9001'
else
res = '127.0.0.1:9002'
end
ngx.var.backend = res
复制代码
配置好以后重启openresty,我们来简单测试下,发起如下请求:
curl -H "Content-Type: application/json" -X POST -d '{"routerId":1}' "http://127.0.0.1:8080/hello"
复制代码
发起请求后我们可以观察到日志中打印如下内容:
2022/02/27 22:40:10 [error] 44248#2240230: *4 kevent() reported that connect() failed (61: Connection refused) while connecting to upstream, client: 127.0.0.1, server: localhost, request: "POST /hello HTTP/1.1", upstream: "http://127.0.0.1:9001/hello", host: "127.0.0.1:8080"
复制代码
当前我们并没有启动相关9001端口的应用,但从日志中我们可以看到已经向http://127.0.0.1:9001/hello 进行了转发。
遇到的问题
测试期间遇到8080端关闭不了的情况,可以使用如下命令查找占用的PID
sudo lsof -i :8080
复制代码
然后进行将PID进行kill,例如PID为:123456
sudo kill -9 123456
复制代码
总结
openresty连接mysql进行lua的简单封装, 并提了下根据参数不同路由到不同机器的情况。具体代码可查看github地址