Shulou(Shulou.com)06/02 Report--

This article shows you how to do OpenResty connection Mysql, the content is concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

centos install mysl

To install mysql on Centos system, download mysql-community-release-el7 -5.noarch.rpm first, and then install it through yum. The installation process is always determined by [Y].

cd /usr/downloads/wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpmrpm -ivh mysql-community-release-el7-5.noarch.rpmyum install mysql-community-server

After successful installation, restart mysql and enter mysql database, set a password for root user, password is "123".

service mysqld restartmysql -u root -pset password for root@localhost = password('123'); openrest Connect to mysql

Lua-rest-mysql module official document address: https://github.com/openresty/lua-resty-mysql

lua-resty-mysql - Lua MySQL client driver for ngx_lua based on the cosocket API

The lua-rest-mysql module is a Lua MySQL client for ngx_lua based on the cosocket API. It guarantees 100% non-blocking.

vim /usr/example/lua/test_mysql.lua, add the following code:

local function close_db(db) if not db then return end db:close() end local mysql = require("resty.mysql") local db, err = mysql:new() if not db then ngx.say("new mysql error : ", err) return end db:set_timeout(1000) local props = { host = "127.0.0.1", port = 3306, database = "mysql", user = "root", password = "123" } local res, err, errno, sqlstate = db:connect(props) if not res then ngx.say("connect to mysql error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end local drop_table_sql = "drop table if exists test" res, err, errno, sqlstate = db:query(drop_table_sql) if not res then ngx.say("drop table error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end local create_table_sql = "create table test(id int primary key auto_increment, ch varchar(100))" res, err, errno, sqlstate = db:query(create_table_sql) if not res then ngx.say("create table error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end local insert_sql = "insert into test (ch) values('hello')" res, err, errno, sqlstate = db:query(insert_sql) if not res then ngx.say("insert error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end res, err, errno, sqlstate = db:query(insert_sql) ngx.say("insert rows : ", res.affected_rows, " , id : ", res.insert_id, "") local update_sql = "update test set ch = 'hello2' where id =" .. res.insert_id res, err, errno, sqlstate = db:query(update_sql) if not res then ngx.say("update error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end ngx.say("update rows : ", res.affected_rows, "") local select_sql = "select id, ch from test" res, err, errno, sqlstate = db:query(select_sql) if not res then ngx.say("select error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end for i, row in ipairs(res) do for name, value in pairs(row) do ngx.say("select row ", i, " : ", name, " = ", value, "") end end ngx.say("") local ch_param = ngx.req.get_uri_args()["ch"] or '' local query_sql = "select id, ch from test where ch = " .. ngx.quote_sql_str(ch_param) res, err, errno, sqlstate = db:query(query_sql) if not res then ngx.say("select error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end for i, row in ipairs(res) do for name, value in pairs(row) do ngx.say("select row ", i, " : ", name, " = ", value, "") end end local delete_sql = "delete from test" res, err, errno, sqlstate = db:query(delete_sql) if not res then ngx.say("delete error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate) return close_db(db) end ngx.say("delete rows : ", res.affected_rows, "") close_db(db)

In the code above, we show some basic functions of creating tables, inserting data, modifying data, querying data, and deleting data.

Some API methods of lua-rest-mysql used:

syntax: db, err = mysql:new() Create a mysql database connection object

syntax: ok, err = db:connect(options) Try to connect to mysql remotely

host mysql host name

port

database name

user username

password password

charset encoding

syntax: db:set_timeout(time) Set database connection timeout

syntax: ok, err = db:set_keepalive(max_idle_timeout, pool_size) Set connection pool

syntax: ok, err = db:close() Close database

syntax: bytes, err = db:send_query(query) Send query

Some key API methods of lua-rest-mysql, see github.com/openresty/lua-resty-mysql#table-of-contents

vim /usr/example/example.conf is configured in the configuration file:

location /lua_mysql { default_type 'text/html'; lua_code_cache on; content_by_lua_file /usr/example/lua/test_mysql.lua; }

The browser visits http://116.196.177.123/lua_mysql and displays the following:

insert rows : 1 , id : 2update rows : 1select row 1 : ch = helloselect row 1 : id = 1select row 2 : ch = hello2select row 2 : id = 2delete rows : 2The above is how to do OpenResty connection Mysql, have you learned knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.