When used in conjunction with the dbase="router" parameter, router modules allow the SQL Relay server to route queries to one database or another based on various conditions.
The routers section of the sqlrelay.conf file indicates which router modules to load and what parameters to use when executing them.
<?xml version="1.0"?>
<instances>
...
<instance ... dbase="router" ...>
...
<routers>
<router module="regex" connectionid="master">
<pattern pattern="^drop "/>
<pattern pattern="^create "/>
<pattern pattern="^insert "/>
<pattern pattern="^update "/>
<pattern pattern="^delete "/>
</router>
<router module="regex" connectionid="slave">
<pattern pattern=".*"/>
</router>
</routers>
...
<connections>
<connection connectionid="master" string="..."/>
<connection connectionid="slave" string="..."/>
</connections>
...
</instance>
...
</instances>
The module attribute specifies which module to load.
Module configurations may have attributes and/or nested tags. How these elements are interpreted is module-specific.
Currently, all router modules have an enabled parameter, allowing the module to be temporarily disabled. If enabled="no" is configured, then the module is disabled. If set to any other value, or omitted, then the module is enabled.
Router modules can be "stacked". Multiple modules may be loaded and multiple instances of the same type of module, with different configurations, may also be loaded.
In fact, the example above shows a stacked configuration. The first instance of the router module sends DDL/DML queries to a "master" database and the second instance of the router module sends all other queries to a "slave" database.
At startup, the SQL Relay server creates instances of the specified router modules and initializes them. When the client sends a query to the SQL Relay server, the server consults each router module, in the order that they were specified in the config file. Each module applies its routing rules to determine which connection to run the query on. If a module returns a connection then the remaining modules are ignored. If the query makes it through all modules without being routed to a particular connection, then the query is ignored.
Currently, the following router modules are available:
The regex module routes queries by matching them against regular expressions.
An example configuration follows.
<?xml version="1.0"?>
<instances>
...
<instance ... dbase="router" ...>
...
<routers>
<router module="regex" connectionid="master">
<pattern pattern="^drop "/>
<pattern pattern="^create "/>
<pattern pattern="^insert "/>
<pattern pattern="^update "/>
<pattern pattern="^delete "/>
</router>
<router module="regex" connectionid="slave">
<pattern pattern=".*"/>
</router>
</routers>
...
<connections>
<connection connectionid="master" string="..."/>
<connection connectionid="slave" string="..."/>
</connections>
...
</instance>
...
</instances>
In this example DDL/DML queries are routed to the connectionid "master", and all other queries are routed to the connectionid "slave".
The string parameter for the "master" connection refers to a separate instance of SQL Relay which maintains connections to the master database. The string parameter for the "slave" connection refers to a separate instance of SQL Relay which maintains connections to the slave databases.
This example is explained in greater detail in the SQL Relay Configuration Guide, in the section on Master-Slave Query Routing.
See Query Routing for more information about query routing in general.
The userlist module routes queries by matching the user that ran the query against a list of users.
An example configuration follows.
<?xml version="1.0"?>
<instances>
...
<instance ... dbase="router" ...>
...
<routers>
<router module="userlist" connectionid="master">
<user user="master"/>
</router>
<router module="userlist" connectionid="slave">
<user user="*"/>
</router>
</routers>
...
<connections>
<connection connectionid="master" string="..."/>
<connection connectionid="slave" string="..."/>
</connections>
...
</instance>
...
</instances>
In this example queries run by user named "master" are routed to the connectionid "master", and queries run by any other user are routed to the connectionid "slave".
The string parameter for the "master" connection refers to a separate instance of SQL Relay which maintains connections to the master database. The string parameter for the "slave" connection refers to a separate instance of SQL Relay which maintains connections to the slave databases.
A similar example is explained in greater detail in the SQL Relay Configuration Guide, in the section on Master-Slave Query Routing.
See Query Routing for more information about query routing in general.
The clientiplist module routes queries by matching the client that ran the query against a list of IP addresses.
An example configuration follows.
<?xml version="1.0"?>
<instances>
...
<instance ... dbase="router" ...>
...
<routers>
<router module="clientiplist" connectionid="master">
<client ip="192.0-168.*.213"/>
</router>
<router module="clientiplist" connectionid="slave">
<client ip="*"/>
</router>
</routers>
...
<connections>
<connection connectionid="master" string="..."/>
<connection connectionid="slave" string="..."/>
</connections>
...
</instance>
...
</instances>
In this example queries run by clients at IP addresses 192.0-168.*.213 are routed to the connectionid "master", and queries run by clients at any other IP address are routed to the connectionid "slave".
Each octet of the ip parameter may be specfied as a number, a dash-separated range of numbers, or a * meaning "all possible values".
The string parameter for the "master" connection refers to a separate instance of SQL Relay which maintains connections to the master database. The string parameter for the "slave" connection refers to a separate instance of SQL Relay which maintains connections to the slave databases.
A similar example is explained in greater detail in the SQL Relay Configuration Guide, in the section on Master-Slave Query Routing.
See Query Routing for more information about query routing in general.
Custom modules may also be developed.
For more information, please contact David Muse at david.muse@firstworks.com.