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

Use time: 2014-05-so far.

Upgrade & change: no changes have been made after putting into operation, stocking status.

Operation: used in internal environment, sharing DBA workload 500times per day + query.

Background: DBA number-3 DBA not only do routine maintenance and database optimization, but also need to spend a lot of time to help developers query non-sensitive data.

Demand: 1. Query can not affect the online database service; 2. Restful API docking with existing operation and maintenance system

3. Can not receive data more than 1000 + query, in case it is easy to mislead data; 4. Prevent malicious operations (ddl such as delete, update, create table, etc.).

Comparison object: PhpMyAdmin (1. Found that there are too many functions and security is not limited; 2. Web and corresponding php services and code need to be installed, which requires a lot of maintenance (for example, java web container system, restart the service for each additional data source)

3. Can not meet all the needs for immediate use)

Because there is an operation and maintenance platform at the front end, the database query function can be implemented in two ways:

1. Develop multiple functional modules in the original operation and maintenance platform; but it will lead to bloated operation and maintenance platform; if a large amount of resources are needed, it is very troublesome to expand.

two。 The third-party tool that uses nginx to query the database is used to return the results of sql operations. At the same time, it can achieve the effect of quasi-hot deployment mode, but when you find a new configuration and need to dynamically add nginx, you can reconfigure nginx to provide formal services.

For the above reasons: the approximate things used have been determined. Nginx, lua (do illegal character check + authority authentication), nginx module ngx_postgres (used to query pgsql), nginx module drizzle-nginx (used to query mysql), form-input-nginx-module (directly get the contents of post, lazy, actually can be obtained in lua code).

Current usage diagram:

1. Work area

two。 Historical record area

Implementation architecture:

Operation and maintenance platform Web interface-> nginx+lua (database routing function)-> nginx+lua + drizzle-nginx + ngx_postgres + form-input-nginx-module (if the performance is insufficient, you need to copy, clone and add resources, as long as you register on the routing side of the database) the common API query interface

Achieve:

1. Compile.

Add-- add-module=PATH/ngx_devel_kit-0.2.19-- add-module=PATH/lua-nginx-module-0.9.7-- add-module=PATH/rds-json-nginx-module-master to nginx (mainly to automatically json the output)-- add-module=PATH/form-input-nginx-module-- add-module=PATH/ngx_postgres-master-- add-module=PATH/drizzle-nginx-module-0.1.7

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

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

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

2.nginx looks up the configuration in the database:

MySQL

Upstream name {

Drizzle_server db ip:db port dbname=xx

Password=xx user=xx protocol=mysql charset=utf8

}

Show table function

Location ~ (database routing identification) {

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

}

Look-up table operation

Location ~ (database routing information) {

Default_type 'text/plain'

Set_form_input $qsql

Access_by_lua $sql'

Permission and illegal character verification function module, verifying 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

}

Look up the table

Location ~ (routing Information) {

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'

}

Query

Location ~ (database routing information) {

Add_header Content-Type 'text/html; charset=utf-8'

Set_form_input $qsql

Access_by_lua $sql'

Permission and illegal character verification function module, verifying 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'

}

Page implementation and test launch time: 3daystransfernginx routing + nginx query: implementation + test 1day.

For more articles, please follow Wechat Subscription account light operation and maintenance.

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.