MySQL Database Cache example

This example is to show how like with the MySQL Database usage you can cache results to improve performance and save your MySQL server from heavy traffic loads. https://github.com/C0nw0nk/Nginx-Lua-Anti-DDoS/wiki/MySQL-Database-example

With MySQL Cache Example

Spoiler : rewrite_by_lua works with New Nginx Lua versions ONLY

#http block
#Initialize a shared Lua cache for SQL queries
lua_shared_dict SqlQueries 10m;
# 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 Query = ngx.shared.SqlQueries
			if not result then
				result = nil
			end
			local QResult = Query:get(result) -- disable this line disables the cache
			if not QResult then

				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

				result, err, errcode, sqlstate = db:query("select ...") --result is a global var so cache can work
				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

			else
				localized_global = {} --define global var that script can read
				localized_global.secret = QResult --nginx will now set secret key via Cached MySQL database result
			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
#Initialize a shared Lua cache for SQL queries
lua_shared_dict SqlQueries 10m;
# 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 Query = ngx.shared.SqlQueries
			if not result then
				result = nil
			end
			local QResult = Query:get(result) -- disable this line disables the cache
			if not QResult then

				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

				result, err, errcode, sqlstate = db:query("select ...") --result is a global var so cache can work
				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

			else
				localized_global = {} --define global var that script can read
				localized_global.secret = QResult --nginx will now set secret key via Cached MySQL database result
			end

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

		'; #end set_by_lua tag

	} #end location block

} #end server block