MySQL Database example

To use this script with MySQL, MariaDB or a NON blocking database is very easy / simple.

Spoiler : rewrite_by_lua works with New Nginx Lua versions ONLY

#http block
# you do not need the following line if you are using
# the ngx_openresty bundle:
# https://github.com/openresty/lua-resty-mysql
lua_package_path "./conf/lua/lua-resty-mysql/lib/?.lua;;";

lua_shared_dict antiddos 70m; #Anti-DDoS shared memory zone to track requests per each unique user
lua_shared_dict antiddos_blocked 70m; #Anti-DDoS shared memory where blocked users are put
lua_shared_dict ddos_counter 10m; #Anti-DDoS shared memory zone to track total number of blocked users
lua_shared_dict jspuzzle_tracker 70m; #Anti-DDoS shared memory zone monitors each unique ip and number of times they stack up failing to solve the puzzle
access_by_lua_file conf/lua/anti_ddos_challenge.lua;

server {

	location /test {

		#open rewrite by lua tag
		# for large configurations use
		#rewrite_by_lua_file custom_settings.lua #you can make your configurations as big as you like without hitting limits
		rewrite_by_lua_block {

			local mysql = require "resty.mysql";
			local db, err = mysql:new()

			db:set_timeout(1000) -- 1 second

			local ok, err, errcode, sqlstate = db:connect{
				host = "127.0.0.1",
				port = 3306,
				database = "my_db",
				user = "my_user",
				password = "my_pwd",
				--pool = "my_connection_pool", --only needed if you use pools
			}

			if not ok then
				--ngx.say("Connection to MySQL failed: ", err)
				return
			end

			if localized_global == nil then --if global not exists
				localized_global = {} --define global var that script can read
			end

			local result, err, errcode, sqlstate = db:query("select ...")
			if not result then
				ngx.say("MySQL error: ", err, ".")
				return
			else
				--result found do something
				localized_global.secret = result --nginx will now set secret key via MySQL database
			end

			-- 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)
				db:close()
				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

		} #end rewrite_by_lua_block

	} #end location block

} #end server block

Spoiler : set_by_lua works with All Old and New Nginx Lua versions

#http block
# you do not need the following line if you are using
# the ngx_openresty bundle:
# https://github.com/openresty/lua-resty-mysql
lua_package_path "./conf/lua/lua-resty-mysql/lib/?.lua;;";

lua_shared_dict antiddos 70m; #Anti-DDoS shared memory zone to track requests per each unique user
lua_shared_dict antiddos_blocked 70m; #Anti-DDoS shared memory where blocked users are put
lua_shared_dict ddos_counter 10m; #Anti-DDoS shared memory zone to track total number of blocked users
lua_shared_dict jspuzzle_tracker 70m; #Anti-DDoS shared memory zone monitors each unique ip and number of times they stack up failing to solve the puzzle
access_by_lua_file conf/lua/anti_ddos_challenge.lua;

server {

	location /test {

		#open set by lua tag
		# for large configurations use
		#set_by_lua_file $localized_global custom_settings.lua #you can make your configurations as big as you like without hitting limits
		set_by_lua $localized_global '

			local load = load
			local localized_global = [[

			local mysql = require "resty.mysql";
			local db, err = mysql:new()

			db:set_timeout(1000) -- 1 second

			local ok, err, errcode, sqlstate = db:connect{
				host = "127.0.0.1",
				port = 3306,
				database = "my_db",
				user = "my_user",
				password = "my_pwd",
				--pool = "my_connection_pool", --only needed if you use pools
			}

			if not ok then
				--ngx.say("Connection to MySQL failed: ", err)
				return
			end

			if localized_global == nil then --if global not exists
				localized_global = {} --define global var that script can read
			end

			local result, err, errcode, sqlstate = db:query("select ...")
			if not result then
				ngx.say("MySQL error: ", err, ".")
				return
			else
				--result found do something
				localized_global.secret = result --nginx will now set secret key via MySQL database
			end

			-- 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)
				db:close()
				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

			]]
			return load(localized_global,"=localized_global_mysql1")() --loadstring to run lua code from our set ngx variable

		'; #end set_by_lua tag

	} #end location block

} #end server block

Well done you are on your way to building your very own CDN just like Cloudflare.

How to setup MySQL with caching example https://github.com/C0nw0nk/Nginx-Lua-Anti-DDoS/wiki/MySQL-Database-Cache-example

You can find more examples on how to Build your own control panel and database here https://github.com/openresty/lua-resty-mysql#synopsis