- COMMAND LINE OPTIONS
- SAMPLE RULES
perl examples/myserver.pl --config=examples/myserver.conf --port=1234 --dsn="dbi:mysql:" mysql -h127.0.0.1 -P1234 -umyuser -e 'info'
This is a simple server that listens for incoming connections from MySQL clients or connectors.
Each query received is processed according to a set of configuration files, which can rewrite the query, forward it to a DBI handle or construct a response or a result set on the fly from any data.
--port=XXXX - port to listen on. Default is
23306, which is the default MySQL port with a 2 in front.
--interface=AAA.BBB.CCC.DDD - interface to listen to. Default is
127.0.0.1 which means that only connections from the localhost will be accepted. To enable connections from the outside use
--interface=0.0.0.0. In this case, please make sure you have some other form of access protection, e.g. like the first rule in the
myserver.conf example configuration file.
--config=config.file - a configuration file containing rules to be executed. The option can be specified multiple times and the rules will be checked in the order specified.
--dsn - specifies a DBI DSN. All queries that did not match a rule or where the rule rewrote the query or did not return any response or a result set on its own will be forwarded to that database. Individual rules can forward specific queries to specific DSNs. If you do not want non-matching queries to be forwarded, either create a match-all rule at the bottom of your last configuration file or omit the
--dsn option. If you omit the option, an error message will be sent to the client.
--dsn_password can be used to specify username and password for DBI drivers where those can not be specified in the DSN string.
Rules to be executed are contained in configuration files. The configuration files are actually standard perl scripts and are executed as perl subroutines. Therefore, they can contain any perl code -- the only requirement is that the last statement in the file (that is, the return value of the file) is an array containing the rules to be executed.
The actions from a rule will be executed for all queries that match a specific pattern. Rules are processed in order and processing is terminated at the first rule that returns some data to the client. This allows you to rewrite a query numerous times and have a final default rule that forwards the query to another server. If
forward is defined, further rules are not processed.
Each rule can have the following attributes:
command The rule will match if the MySQL command issued by the client matches
command can either be an integer from the list found at
DBIx::MyServer.pm or a reference to a
SUB that returns such an integer. This is mainly useful for processing incoming
match The rule will match if the test of the query matches a regular expression or is identical to a string.
match can also be a reference to a
SUB in which case the sub is executed and can either return a string or a regular expression.
match are specified, both must match for the rule to be executed.
dbh if specified, any matching query will be forwarded to this database handle (possibly after a
rewrite), rather than the default handle specifeid on the command line.
dsn behaves identically, however a database handle is contructed from the
dsn provided and an attempt is made to connect to the database. If
dsn is a reference to an array, the first item from the array is used as a DSN, the second one is used as username and the third one is used as password.
before this can be a reference to a subroutine that will be called after a matching query has been encountered but before any further processing has taken place. The subroutine will be called with the text of the query as the first argument, followed by extra arguments containing the strings matched from any parenthesis found in the
match regular expression. You can use
before to execute any extra queries before the main query, such as
EXPLAIN. The return value from the
before subroutine is discarded and is not used.
rewrite is a string that will replace the original query that matches the rule, or a reference to a subroutine that will produce such a string. If
rewrite is not defined, and
match was a string, the query is passed along unchanged. If
match was a regular expression, the string matched by the first set of parenthesis is used. This way, if the rule does not specify any
ok clauses, but a valid DBI handle is defined, the query will be forwarded to that handle automatically.
error can be either an array reference containing three arguments for
DBIx::MyServer::sendError() or a reference to a subroutine returning such an array (or array reference). If this is the case, the error message will be sent to the client. If
error is not defined or the subroutine returns
undef, no error message will be sent. In this case, you need to send at some point either an
ok or a result set, otherwise the client will hang forever waiting for a response.
ok behaves identically to
error -- if it is defined or points to a subroutine which, when called, returns a true value, an OK response will be sent to the client.
ok can also be a reference to an array, or the subroutine can return such an array -- in this case the first item is the message to be sent to the client, the second one is the number of affected rows, the third is the insert_id and the last one is the warning count.
columns must contain either an array reference or a reference to a subroutine which returns and array or array reference. The column names from the array will be sent to the client. By default, all columns are defined as
data must contain either a reference to the data to be returned to the client or a reference to subroutine that will produce the data. "Data" can be a reference to a
HASH, in which case the hash will be sent with the key names in the first column and the key values in the second. It can be a flat array, in which case the array items will be sent as a single column, or it can be a reference to a nested array, with each sub-array being a single row from the response.
after is called after all other parts of the rule have been processed.
forward if defined, the query will be immediately forwarded to the server and no further rules will be processed.
All subroutine references that are called will have the text of the query passed as the first argument and the subsequent arguments will be any strings matched by parenthesis in the
match regular expression.
Your code in the configuration file can save and retrieve state by using
set($variable, $value). State is retained as long as the connection is open. Each new connection starts with a clean state. The following variables are maintained by the system:
myserver contains a reference to the DBIx::MyServer object being used to service the connection. You can use this to inject data and packets directly into the network stream.
username contains the username provided by the client at connection establishment.
database contains the database requested by the client at connection establishment. By default,
myserver.pl will not automatically handle any database changes requested by the client. You are responsible for handling those either by responding with a simple OK or by updating the variables.
remote_host contains the IP of the client.
dsn will contain a reference to the default DBI handle and the DSN string it was produced from, as taken from the command line. Even if a specific rule has its own
dsn, the value of those variables will always refer to the default
dsn. If you change the <dsn> variable, the system will attempt to connect to the new dsn string and will produce a new
dbh handle from it. If you set
dsn to an array reference, the first item will be used as a DSN, the second one as a username and the third one as a password.
dsn_password can be used for the same purpose.
args contains a reference to the
@ARGV array, that is, the command line options that evoked myserver.pl
remote_dsn_password are convenience variables that can also be specified on the command line. It is not used by
myserver.pl however you can use it in your rules, the way
IMPORTANT NOTICE: THIS SCRIPT IS MEANT FOR DEMONSTRATION PURPOSES ONLY AND SHOULD NOT BE ASSUMED TO BE SECURE BY ANY MEANS!
By default the script will only accept incoming connections from the local host. If you relax that via the
--interface command-line option, all connections will be accepted. However, once the connection has been established, you can implement access control as demonstrated in the first rule of the
myserver.conf file -- it returns "Access denied" for every query unless the username is "myuser". Future versions of the script will allow connections to be rejected during handshake.
The script expects that the password is equal to the username. This is currently hard-coded.
The following rule sets are provided in the
This configuration provides some simple query rewriting examples as suggested by Giuseppe Maxia and Jan Kneschke, e.g. commands like
cd as well as fixing spelling mistakes. In addition, some very simple access control is demonstrated at the top of the file.
This rule set implements a
SELECT REMOTE select_query ON 'dsn' operator which will execute the query on <dsn> specified, bring the results back into a temporary table on the default server and substitute the
REMOTE_SELECT part in the orignal query with a reference to the temoporary table. The following scenarios are possible:
# Standalone usage mysql> SELECT REMOTE * FROM mysql.user ON 'dbi:mysql:host=remote:user=foo:password=bar' # CREATE ... SELECT usage mysql> CREATE TABLE local_table SELECT REMOTE * FROM remote_table ON 'dbi:mysql:host=remote' # Non-correlated subquery mysql> SELECT * mysql> FROM (SELECT REMOTE * FROM mysql_user ON 'dbi:mysql:host=remote:user=foo:password=bar') mysql> WHERE user = 'mojo' # Specify remote dsn on the command line shell> ./myserver.pl --config=remotequery.conf --dsn=dbi:mysql: --remote_dsn=dbi:mysql:host=host2 mysql> select remote 1; # Specify remote dsn as variable mysql> set @@@remote_dsn=dbi:mysql:host=host2 mysql> select remote NOW(); mysql> set @@@devel_dsn=dbi:mysql:host=dev3 mysql> select remote NOW() ON @@@devel_dsn;
This is different from using the Federated storage handler because the entire
REMOTE_SELECT query is executed on the remote server and only the result is sent back to the default server for further processing. This is useful if a lot of processing is to be done on the remote server -- the Federated engine will bring most of the data to the connecting server and will process it there, which can potentially be very time consuming.
Please note that since a temporary table is created and it must reside somewhere, you need to be in a working database on the default server. Updateable
VIEWa are not supported.
This configuration provides the following operators:
shell - can be used to execute shell commands, e.g.
shell ls -la.
env - returns the operating environment of
stats - executes
SHOW STATUS before and after each query and returns the difference. First you execute
stats select a from b and then
devel - can be used to send specfic queries to a different server. You can execute a single query as
devel select a from b or use a standalone
devel to redirect all future queries until you issue
You specify the server to send "development" queries to via
set('devel_dsn') at the top of
odbc.conf contains an example on how to unintelligently answer generic queries sent by the MySQL ODBC driver and the applications that use it, up to the point where real data can be sent over the connection and imported into the client application.