=encoding utf-8 =head1 Name drizzle-nginx-module - Upstream module for talking to MySQL and Drizzle directly I See L. This module is already production ready and is powering L. =head1 Version This document describes ngx_drizzle L released on 22 June 2015. =head1 Synopsis http { ... upstream cluster { # simple round-robin drizzle_server 127.0.0.1:3306 dbname=test password=some_pass user=monty protocol=mysql; drizzle_server 127.0.0.1:1234 dbname=test2 password=pass user=bob protocol=drizzle; } upstream backend { drizzle_server 127.0.0.1:3306 dbname=test password=some_pass user=monty protocol=mysql; } server { location /mysql { set $my_sql 'select * from cats'; drizzle_query $my_sql; drizzle_pass backend; drizzle_connect_timeout 500ms; # default 60s drizzle_send_query_timeout 2s; # default 60s drizzle_recv_cols_timeout 1s; # default 60s drizzle_recv_rows_timeout 1s; # default 60s } ... # for connection pool monitoring location /mysql-pool-status { allow 127.0.0.1; deny all; drizzle_status; } } } =head1 Description This is an nginx upstream module integrating L 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 L, L, or L, to work with this module. See L for details. =head2 Keepalive connection pool 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 L directive for more details. =head2 Last Insert ID 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 C 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 C 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 C field (as well as the C field in the 3rd JSON response. =head1 Directives =head2 drizzle_server B IhostE user=EuserE password=EpassE dbname=EdatabaseE> B IhostE:EportE user=EuserE password=EpassE dbname=EdatabaseE protocol=EprotocolE charset=EcharsetE> B I B I 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: BC<< >> MySQL/Drizzle user name C<< >> for login. BC<< >> Specify mysql password C<< >>for login. If you have special characters like C<#> 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; BC<< >> Specify default MySQL database C<< >> for the connection. Note that MySQL does allow referencing tables belonging to different databases by qualifying table names with database names in SQL queries. BC<< >> Specify which wire protocol to use, C or C. Default to C. BC<< >> Explicitly specify the character set for the MySQL connections. Setting this option to a non-empty value will make this module send out a C<< 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 C is. =head2 drizzle_keepalive B IsizeE mode=EmodeE> B I B I Configures the keep-alive connection pool for MySQL/Drizzle connections. The following options are supported: BC<< >> Specify the capacity of the connection pool for the current upstream block. The EnumE value I be non-zero. If set to C<0>, it effectively disables the connection pool. This option is default to C<0>. BC<< >> This supports two values, C and C. The C mode means the pool does not distinguish various drizzle servers in the current upstream block while C means the pool will merely reuse connections which have identical server host names and ports. Note that even under C, differences between C or C parameters will be silently ignored. Default to C. BC<< >> This option specifies what to do when the connection pool is already full while new database connection is required. Either C or C can be specified. In case of C, it will reject the current request, and returns the C<503 Service Unavailable> error page. For C, this module will go on creating a new database connection. =head2 drizzle_query B IsqlE> B I B I 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 L 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; } =head2 drizzle_pass B IremoteE> B I B I B I This directive specifies the Drizzle or MySQL upstream name to be queried in the current location. The C<< >> argument can be any upstream name defined with the L directive. Nginx variables can also be interpolated into the C<< >> 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; } } =head2 drizzle_connect_timeout B ItimeE> B I B I Specify the (total) timeout for connecting to a remote Drizzle or MySQL server. The C<<