an nginx upstream module that talks to mysql and drizzle by libdrizzle
an nginx upstream module that talks to mysql and drizzle by libdrizzle
drizzle-nginx-module - Upstream module for talking to MySQL and Drizzle directly
This module is not distributed with the Nginx source. See the installation instructions.
This module is already production ready.
This document describes ngx_drizzle v0.1.11 released on 19 April 2018.
…This is an nginx upstream module integrating libdrizzle into Nginx in a non-blocking and streamming way.
Essentially it provides a very efficient and flexible way for nginx internals to access MySQL, Drizzle, as well as other RDBMS's that support the Drizzle or MySQL wired protocol. Also it can serve as a direct REST interface to those RDBMS backends.
This module does not generate human-readable outputs, rather, in a binary format called Resty-DBD-Stream (RDS) designed by ourselves. You usually need other components, like rds-json-nginx-module, rds-csv-nginx-module, or lua-rds-parser, to work with this module. See Output Format for details.
This module also provides a builtin per-worker connection pool mechanism for MySQL or Drizzle TCP connections.
Here's a sample configuration:
upstream backend {
drizzle_server 127.0.0.1:3306 dbname=test
password=some_pass user=monty protocol=mysql;
drizzle_keepalive max=100 mode=single overflow=reject;
}For now, the connection pool uses a simple LIFO algorithm to assign idle connections in the pool. That is, most recently (successfully) used connections will be reused first the next time. And new idle connections will always replace the oldest idle connections in the pool even if the pool is already full.
See the drizzle_keepalive directive for more details.
If you want to get LAST_INSERT_ID, then ngx_drizzle already returns that automatically for you when you're doing a SQL insert query. Consider the following sample nginx.conf snippet:
location /test {
echo_location /mysql "drop table if exists foo";
echo;
echo_location /mysql "create table foo (id serial not null, primary key (id), val real);";
echo;
echo_location /mysql "insert into foo (val) values (3.1415926);";
echo;
echo_location /mysql "select * from foo;";
echo;
}
location /mysql {
drizzle_pass backend;
drizzle_module_header off;
drizzle_query $query_string;
rds_json on;
}Then request GET /test gives the following outputs:
{"errcode":0}
{"errcode":0}
{"errcode":0,"insert_id":1,"affected_rows":1}
[{"id":1,"val":3.1415926}]You can see the insert_id field (as well as the affected_rows field in the 3rd JSON response.
syntax: drizzle_server <host> user=<user> password=<pass> dbname=<database>
syntax: drizzle_server <host>:<port> user=<user> password=<pass> dbname=<database> protocol=<protocol> charset=<charset>
default: no
context: upstream
Directive assigns the name and the parameters of server. For the name it is possible to use a domain name, an address, with an optional port (default: 3306). If domain name resolves to several addresses, then all are used.
The following options are supported:
user= MySQL/Drizzle user name for login.
password= Specify mysql passwordfor login. If you have special characters like # or spaces in your password text, then you'll have to quote the whole key-value pair with either single-quotes or double-quotes, as in
drizzle_server 127.0.0.1:3306 user=monty "password=a b#1"
dbname=test protocol=mysql;dbname= Specify default MySQL database for the connection. Note that MySQL does allow referencing tables belonging to different databases by qualifying table names with database names in SQL queries.
protocol=``
Specify which wire protocol to use, drizzle or mysql. Default to drizzle.
charset=``
Explicitly specify the character set for the MySQL connections. Setting this option to a non-empty value will make this module send out a set names '' query right after the mysql connection is established.
If the default character encoding of the MySQL connection is already what you want, you needn't set this option because it has extra runtime cost.
Here is a small example:
drizzle_server foo.bar.com:3306 user=monty password=some_pass
dbname=test protocol=mysql
charset=utf8;Please note that for the mysql server, "utf-8" is not a valid encoding name while utf8 is.
syntax: drizzle_keepalive max=<size> mode=<mode>
default: drizzle_keepalive max=0 mode=single
context: upstream
Configures the keep-alive connection pool for MySQL/Drizzle connections.
The following options are supported:
max=``
Specify the capacity of the connection pool for the current upstream block. The value must be non-zero. If set to 0, it effectively disables the connection pool. This option is default to 0.
mode=``
This supports two values, single and multi. The single mode means the pool does not distinguish various drizzle servers in the current upstream block while multi means the pool will merely reuse connections which have identical server host names and ports. Note that even under multi, differences between dbname or user parameters will be silently ignored. Default to single.
overflow=``
This option specifies what to do when the connection pool is already full while new database connection is required. Either reject or ignore can be specified. In case of reject, it will reject the current request, and returns the 503 Service Unavailable error page. For ignore, this module will go on creating a new database connection.
syntax: drizzle_query <sql>
default: no
context: http, server, location, location if
Specify the SQL queries sent to the Drizzle/MySQL backend.
Nginx variable interpolation is supported, but you must be careful with SQL injection attacks. You can use the set_quote_sql_str directive, for example, to quote values for SQL interpolation:
location /cat {
set_unescape_uri $name $arg_name;
set_quote_sql_str $quoted_name $name;
drizzle_query "select * from cats where name = $quoted_name";
drizzle_pass my_backend;
}syntax: drizzle_pass <remote>
default: no
context: location, location if
phase: content
This directive specifies the Drizzle or MySQL upstream name to be queried in the current location. The `` argument can be any upstream name defined with the drizzle_server directive.
Nginx variables can also be interpolated into the `` argument, so as to do dynamic backend routing, for example:
upstream moon { drizzle_server ...; }
server {
location /cat {
set $backend 'moon';
drizzle_query ...;
drizzle_pass $backend;
}
}syntax: drizzle_connect_time <time>
default: drizzle_connect_time 60s
context: http, server, location, location if
Specify the (total) timeout for connecting to a remote Drizzle or MySQL server.
The `` argument can be an integer, with an optional time unit, like s (second), ms (millisecond), m (minute). The default time unit is s, i.e., "second". The default setting is 60s.
syntax: drizzle_send_query_timeout <time>
default: drizzle_send_query_timeout 60s
context: http, server, location, location if
Specify the (total) timeout for sending a SQL query to a remote Drizzle or MySQL server.
The `` argument can be an integer, with an optional time unit, like s (second), ms (millisecond), m (minute). The default time unit is s, ie, "second". The default setting is 60s.
syntax: drizzle_recv_cols_timeout <time>
default: drizzle_recv_cols_timeout 60s
context: http, server, location, location if
Specify the (total) timeout for receiving the columns metadata of the result-set to a remote Drizzle or MySQL server.
The `` argument can be an integer, with an optional time unit, like s (second), ms (millisecond), m (minute). The default time unit is s, ie, "second". The default setting is 60s.
syntax: drizzle_recv_rows_timeout <time>
default: drizzle_recv_rows_timeout 60s
context: http, server, location, location if
Specify the (total) timeout for receiving the rows data of the result-set (if any) to a remote Drizzle or MySQL server.
The `` argument can be an integer, with an optional time unit, like s (second), ms (millisecond), m (minute). The default time unit is s, ie, "second". The default setting is 60s.
syntax: drizzle_buffer_size <size>
default: drizzle_buffer_size 4k/8k
**co
No open issues yet, or sync has not completed.