简介

OpenResty 能够使用Lua语言快速构造出足以胜任10K+并发连接响应的超高性能web应用, 构建web应用不可避免地会访问数据库，本篇介绍openresty如何访问mysql。

本教程适用于CentOS 6.x版本。

在使用Openresty访问mysql之前，您需要先建立Openresty工作环境，可以参考在CentOS 6上搭建openresty环境，参考在Centos 6上建立lnmp建立mysql环境。

建立mysql表

在mysql数据库中建立openresty数据库和user表，其中已设置mysql访问密码为'root':

$sudo mysql -uroot -proot
mysql> CREATE DATABASE IF NOT EXISTS openresty DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use openresty
Database changed
mysql> create table user ( id int(10) not null auto_increment, name varchar(80) not null default '', email varchar(80) not null default '', primary key(id)) DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.00 sec)

nginx的配置如下：

http {
    init_by_lua '
        mysql = require "resty.mysql"
        cjson = require "cjson"
        ';
    server {
        listen       80;
        server_name  localhost;
        location /insert {
            content_by_lua_file conf/user.lua;
        }
        location /delete {
            content_by_lua_file conf/user.lua;
        }
        location /query {
            content_by_lua_file conf/user.lua;
        }
    }
}

lua脚本文件user.lua如下：

function connect_mysql()
    local db, err = mysql:new()
    if not db then
        ngx.log(ngx.ERR, "failed to instantiate mysql: "..err)
        return
    end
    db:set_timeout(1000) -- 1 sec
    local ok, err, errno, sqlstate = db:connect{
                host = '127.0.0.1',
                port = 3306,
                database = 'openresty',
                user = 'root',
                password = 'root',
                max_packet_size = 1024 * 1024,
                pool = "openrestypool" }
    if not ok then
        ngx.log(ngx.ERR, "failed to connect: ", err, errno, sqlstate)
        return
    end
    return db
end

function keepalive(mydb)
    if httpdnsdb~=nil then
        local ok, err = mydb:set_keepalive(0, 1000)
        if not ok then
            ngx.log(ngx.ERR, "failed to set keepalive: ", err)
            return
        end
    end
end

function myquery(querystring)
    mydb=connect_mysql()
    if not mydb then
        ngx.say("mydb is nil.")
        return;
    end
    local res, err, errno, sqlstate = mydb:query(querystring)
    if err then
        ngx.say('"error":'..err)
    elseif res then
        ngx.say(cjson.encode(res))
    end
    keepalive(mydb)
end

ngx.req.read_body()
args=ngx.req.get_uri_args()
if (ngx.var.uri == '/insert') then
    ngx.req.set_header('Content-Type', 'text/html;charset=utf8');
    local queryString="INSERT INTO user(name, email) VALUES('".. args.name .. "','" .. args.email .. "')"
    myquery(queryString)
elseif (ngx.var.uri == '/delete') then
    ngx.req.set_header('Content-Type', 'text/html;charset=utf8');
    local queryString= "delete from user WHERE name='"..args.name.."'"
    myquery(queryString)
elseif (ngx.var.uri == '/query') then
    ngx.req.set_header('Content-Type', 'text/html;charset=utf8');
    local queryString= "select * from user WHERE name='"..args.name.."'"
    myquery(queryString)
end

启动nginx，并使用curl测试：

$ sudo /usr/local/openresty/nginx/sbin/nginx -c /usr/local/openresty/nginx/conf/nginx.conf
$ curl "http://127.0.0.1/insert?name=abc&email=abc@163.com"
{"insert_id":7,"server_status":2,"warning_count":0,"affected_rows":1}
$ curl "http://127.0.0.1/query?name=abc"
[{"email":"abc@163.com","name":"abc","id":7}]
$ curl "http://127.0.0.1/delete?name=abc"
{"insert_id":0,"server_status":34,"warning_count":0,"affected_rows":1}
$ curl "http://127.0.0.1/query?name=abc"
{}

总结

使用美团云主机，可以很方便地使用yum源获取软件，建立属于自己的openresty和lua环境，访问mysql，快速实现自己10k+的超高性能WEB应用。