OpenResty连接mysql

介绍

主要介绍了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地址

参考

github.com/openresty/l…

www.moguhu.com/article/det…

github.com/shixinke/op…