openesty嵌入lua脚本操作mysql

友儿

January 26, 2022

993 views

No comments

3544 words

架构 LUA

openesty嵌入lua脚本连接mysql

下载地址

nginx.conf文件

worker_processes  1;
error_log logs/error.log;
events {
  worker_connections 1024;
}
http {
  # 设置纯 Lua 扩展库的搜寻路径(';;' 是默认路径)
  lua_package_path "/data/www/code/nginx+lua/config/lua_p/?.lua;;";
  # 设置 C 编写的 Lua 扩展模块的搜寻路径(也可以用 ';;')
  lua_package_cpath "/data/www/code/nginx+lua/config/lua_p_c/?.so;;";

  server {
     listen 8080;
     location /mysql {
           default_type 'text/html';
           lua_code_cache off;
           content_by_lua_file   ./config/lua/mysql.lua;
           charset utf-8;
     }
  }
}

mysql.lua文件

local mysql = require "resty.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

-- or connect to a unix domain socket file listened
-- by a mysql server:
--     local ok, err, errcode, sqlstate =
--           db:connect{
--              path = "/path/to/mysql.sock",
--              database = "ngx_test",
--              user = "ngx_test",
--              password = "ngx_test" }

local ok, err, errcode, sqlstate = db:connect{
  host = "127.0.0.1",
  port = 3306,
  database = "test",
  user = "root",
  password = "123456",
  charset = "utf8",
  max_packet_size = 1024 * 1024,
};

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

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

local res, err, errcode, sqlstate =
db:query("drop table if exists cats");
if not res then
  ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".");
  return
end

res, err, errcode, sqlstate =
db:query("create table cats "
      .. "(id serial primary key, "
      .. "name varchar(5))");
if not res then
  ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".");
  return
end

ngx.say("table cats created.</br>");

res, err, errcode, sqlstate =
db:query("insert into cats (name) "
      .. "values (\'Bob\'),(\'老王\'),(\'老张\')");
if not res then
  ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".");
  return
end

ngx.say(res.affected_rows, " rows inserted into table cats </br>",
  "(last insert id: ", res.insert_id, ")</br>");

-- run a select query, expected about 10 rows in
-- the result set:
res, err, errcode, sqlstate =
db:query("select * from cats order by id asc", 10);
if not res then
  ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".");
  return
end

local cjson = require "cjson";
ngx.say("result: ", cjson.encode(res));

-- put it into the connection pool of size 100,
-- with 10 seconds max idle timeout
local ok, err = db:set_keepalive(10000, 100);
if not ok then
  ngx.say("failed to set keepalive: ", err);
  return
end

-- or just close the connection right away:
-- local ok, err = db:close()
-- if not ok then
--     ngx.say("failed to close: ", err)
--     return
-- end