效率篇-SQL自助查询平台

ngx_postgres = https://github.com/FRiCKLE/ngx_postgres

form-input-nginx-module = https://github.com/calio/form-input-nginx-module

2.nginx查找数据库的中配置：

MySQL

upstream name {

            drizzle_server db ip:db port dbname=xx

                           password=xx user=xx protocol=mysql charset=utf8;

    }

show table功能

            location ~ （数据库路由标识） {

                set $upname $1;

                set $my_sql ‘show tables‘;

                drizzle_query $my_sql;

                drizzle_pass $upname;

                drizzle_connect_timeout    10s; # default 60s

                drizzle_send_query_timeout 60s;    # default 60s

                drizzle_recv_cols_timeout  30s;    # default 60s

                drizzle_recv_rows_timeout  30s;    # default 60s

                rds_json on;

            }

查表操作

   location ~ （数据库路由信息) {

                default_type ‘text/plain‘;

                set_form_input $qsql;

               access_by_lua $sql ‘

                     权限和非法字符验证功能模块，验证qsql

                ‘;

                set $upname $1;

                lua_need_request_body on;

                client_max_body_size 50k;

                client_body_buffer_size 50k;

                drizzle_query $sql;

                drizzle_pass $upname;

                drizzle_connect_timeout    10s; # default 60s

                drizzle_send_query_timeout 30s;    # default 60s

                drizzle_recv_cols_timeout  30s;    # default 60s

                drizzle_recv_rows_timeout  30s;    # default 60s

                rds_json on;

            }

PG

   upstream 192.168.236.1725432huayouhui_app {

        postgres_server  db ip:db port dbname=xx

        user=xx password=xx;

        postgres_keepalive off;

    }

查表

location ~ （路由信息）  {

add_header  Content-Type ‘text/html; charset=utf-8‘;

set $dbname $2;

set $upname $1$2;

set_by_lua $quoted_name ‘

local name = ngx.var.dbname

local mysql = "select tablename from pg_tables where schemaname=\‘"..name.."\‘;"

return mysql

‘;

postgres_pass   $upname;

rds_json on;

postgres_query  ‘$quoted_name‘;

}

查询

        location ~ (数据库路由信息) {

            add_header  Content-Type ‘text/html; charset=utf-8‘;

            set_form_input $qsql;

               access_by_lua $sql ‘

                     权限和非法字符验证功能模块，验证qsql

                ‘;

                set $upname $1;

                lua_need_request_body on;

                client_max_body_size 50k;

                client_body_buffer_size 50k;

            postgres_pass   $upname;

            rds_json on;

            postgres_query  ‘$sql‘;

        }

页面实现和测试上线时间：3days；nginx 路由 + nginx查询 ： 实现+测试 1day。

更多文章请关注微信订阅号 轻度运维

本文出自 “轻度运维” 博客，请务必保留此出处http://qdywsky.blog.51cto.com/3129793/1904763

效率篇-SQL自助查询平台

标签：运维