How do I use MySQL for dynamic doc root with Nginx?

Question

I've been trying to find out a way to first capture environment variable HOSTNAME and then use a MySQL query to fetch and return back to the Nginx conf the document root for our vhosts. We use them for dynamic doc roots currently in Apache but are migrating to Nginx.

example nginx.conf (might look something like this):

server {
    listen   80;
     # grab Environment variable HOSTNAME
     $hostname= ENV(HOSTNAME);
     # execute mysql query
    $doc_root = mysql(select docroot from table where host = '$hostname' );
     # set document root
    root           /var/www/$doc_root;

..... I was exploring using Lua and https://github.com/openresty/lua-resty-mysql but have been unable to figure out how this could be done to capture HOSTNAME and mysql query as a variable and return the results back.

Answer 1

Thanks for your help. It didn't work for me, but after a lot of work, I finally got something working. This is for someone else if they ever need it

it turns out $http_host is already defined globally in nginx - so that was fixed.

        set $httphost $http_host; # create and initialize var
        set $docroot "";

    # begin LUA scripting
    rewrite_by_lua '
                    -- make sure http host is defined
                    if not ngx.var.httphost then
                      ngx.log(ngx.ERR,"ERROR - no httphost defined")
                      return
                    end

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

                    db:set_timeout(1000) -- 1 sec

                    local ok, err, errno, sqlstate = db:connect
                    {
                            host = "127.0.0.1",
                            port = 3306,
                            database = "db",
                            user = "user",
                            password = "password",
                            max_packet_size = 1024 * 1024
                    }

                    if not ok then
                      ngx.log(ngx.ERR,"MySQL failed to connect: ", err, ": ", errno, " ", sqlstate)
                      return
                    end

                    -- prevent injection attack
                    local hname = ngx.unescape_uri(client)
                    local quoted_name = ngx.quote_sql_str(hname)

                    local sql = "select docroot from users where customer =" .. quoted_name
                    result,err,errno,sqlstate = db:query(sql,1)
                    if not result then
                       ngx.log(ngx.ERR,"MySQL bad result: ", err, ": ", errno, ": ", sqlstate, ".")
                       return
                    end

                    if not result[1].docroot then
                      ngx.log(ngx.ERR,"MySQL ERROR - no docroot was returned")
                      return
                    end

                    ngx.var.docroot = result[1].docroot
            ';
           # now we can set the docroot for this host
          root           /var/www/$docroot;

Answer 2

First of all, using a database for doing basic routing does not sound like a very good idea - I would recommend having the results cached in memory and maybe refreshing them periodically from the database.

Second, the basic Nginx config file will get you only so far - in order to get more advanced functionality you will need to use a scripting language (like Lua) on top of it. One of the things this allows you is reading environment variables. I wrote about how to do it here:

https://docs.apitools.com/blog/2014/07/02/using-environment-variables-in-nginx-conf.html

The usual way to get Lua working on Nginx is using Openresty, a version of Nginx which comes with several modules pre-installed, including the Lua one. You can add lua-resty-mysql to the mix, and then do everything you want directly from Lua. I have never used lua-resty-mysql so I can't code that for you. If you are set on using Mysql, you will have to study its docs. Give a look at Redis while you are at it, it might be a better fit than Mysql.