在nginx中使用lua直接访问mysql和memcaced达到数据接口的统一

安装nginx参见《nginx+lua+redis构建高并发应用》

让nginx 中的nginx_lua_module支持mysql 和memcache
下载
https://github.com/agentzh/lua-resty-memcached
https://github.com/agentzh/lua-resty-mysql

对于访问接口的统一有很多的处理方式，这里介绍使用nginx lua 访问mysql并用memcache缓存起来。

配置如下：

        ...
        location /getinfo {
            default_type 'text/plain';
            content_by_lua '

                local args = ngx.req.get_uri_args()

                if args["appleid"] == nil then
                    ngx.say("param appleid is nil")
                    return
                end

                local memcached = require "memcached"
                local memc, err = memcached:new()
                if not memc then
                    ngx.say("failed to instantiate memc: ", err)
                    return
                end


                memc:set_timeout(1000) -- 1 sec

                local ok, err = memc:connect("172.16.18.114", 11211)
                if not ok then
                    ngx.say("failed to connect: ", err)
                    return
                end

                local res, flags, err = memc:get(args["appleid"])
                if err then
                    ngx.say("failed to get memc: ", err)
                    return
                end

                if not res then

                    local mysql = require "mysql"
                    local db, err = mysql:new()
                    if not db then
                        ngx.say("failed to instantiate mysql: ", err)
                        return
                    end

                    db:set_timeout(1000) -- 1 sec

                    local ok, err, errno, sqlstate = db:connect{
                        host = "172.16.18.162",
                        port = 3306,
                        database = "test",
                        user = "root",
                        password = "cpyf",
                        max_packet_size = 1024 * 1024
                    }

                    if not ok then
                        ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
                        return
                    end

                    -- ngx.say("connected to mysql.")

                    sql = "select * from tagval where tag = \'" .. args["appleid"] .. "\'"

                    res, err, errno, sqlstate = db:query(sql)
                    if not res then
                        ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
                        return
                    end

                    local json = require "json"
                    ngx.say("mysql found")
                    ngx.say(json.encode(res))

                    local ok, err = memc:set(args["appleid"], json.encode(res))
                    if not ok then
                        ngx.say("failed to set memc: ", err)
                        return
                    end

                    local ok, err = db:set_keepalive(0, 100)
                    if not ok then
                        ngx.say("failed to set keepalive: ", err)
                        return
                    end

                    return

                end

                ngx.say("memc found")
                ngx.say(res)

                memc:set_keepalive(0, 100)

            ';
        }
        ...

第二次运行：

curl --get http://app.ca-sim.com/getinfo?appleid=jfy

mysql found
[{"val":"123","tag":"jfy"}]

第二次后运行：

curl --get http://app.ca-sim.com/getinfo?appleid=jfy

memc found
[{"val":"123","tag":"jfy"}]

结果已被缓存

在nginx中使用lua直接访问mysql和memcaced达到数据接口的统一,古老的榕树,5-wow.com