Re: [Sqlrelay-discussion] r/w splitting configuration question

[David M. <dav...@fi...>]

Hi Jan,

I'm sorry for taking over two months to reply.  I hope you discovered 
the problem already, but if you didn't...

The problem is that there are no users and passwords defined for each 
instance.  Each instance requires a <users/> block and the <route/> tags 
must refer to them.

For example, you can configure the master instance to be authenticated 
by masteruser/masterpassword and the slave instance to be authenticated 
by slaveuser/slavepassword and the test instance to be authenticated by 
testuser/testpassword as follows:

<instance id="master" ...>
<users>
<user user="masteruser" password="masterpassword"/>
</users>
     ...
</instance>

<instance id="slave" ...>
<users>
<user user="slaveuser" password="slavepassword"/>
</users>
     ...
</instance>

<instance id="test" ...>
<users>
<user user="testuser" password="testpassword"/>
</users>
<route host="" port="9000" socket="/tmp/master.socke" user="masteruser" 
password="masterpassword">
         ...
</route>
     ...
<route host="" port="9001" socket="/tmp/slave.socke" user="slaveuser" 
password="slavepassword">
         ...
</route>
</intstance>


Note that the route tags use the appropriate user/password that was 
configured earlier.

I hope this information doesn't come too late.

Take care,

David Muse
dav...@fi...

On 03/09/2012 01:41 PM, jan...@id... wrote:
> ________________________________________
> Von: David Muse [dav...@fi...]
> Gesendet: Freitag, 9. März 2012 18:27
> An: Discussion of topics related to SQL Relay
> Betreff: Re: [Sqlrelay-discussion] r/w splitting configuration question
>
> Could you send me your sqlrelay.conf file?
>
> Dave
>
>
>
>
>
> ok Dave this is the one I use :
>
> $ cat /opt/sqlrelay-0.44/etc/sqlrelay.conf
> <?xml version="1.0"?>
> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
>
> <instances>
>
> 	<!-- Master -->
> 	<instance id="master" port="9000" socket="/tmp/master.socke" dbase="mysql" connections="1" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no" timequeriessec="-1" timequeriesusec="-1" fakeinputbindvariables="no" translatebindvariables="no" isolationlevel="read committed" ignoreselectdatabase="no">
> 		<connections>
> 			<connection connectionid="db1" string="user=root;password=;db=ilias;host=192.168.122.152;port=3306;" metric="1" behindloadbalancer="no"/>
> 		</connections>
> 	</instance>
>
> 	<!-- Slave -->
> 	<instance id="slave" port="9001" socket="/tmp/slave.socke" dbase="mysql" connections="1" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="connection" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no" timequeriessec="-1" timequeriesusec="-1" fakeinputbindvariables="no" translatebindvariables="no" isolationlevel="read committed" ignoreselectdatabase="no">
> 		<connections>
> 			<connection connectionid="db1" string="user=root;password=;db=ilias;host=192.168.122.153;port=3306;" metric="1" behindloadbalancer="no"/>
> 		</connections>
> 	</instance>
>
> 	<!-- Query Router/Filter -->
> 	<instance id="test" port="9002" socket="/tmp/router.socke" dbase="router" connections="1" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="listener_and_connection_and_sqltranslation" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no">
> 		<router>
> 			<!-- send all queries to master -->
> 			<route host="" port="9000" socket="/tmp/master.socke">
> 				<query pattern="^\s*update\s+.*"/>
> 				<query pattern="^\s*delete\s+from\s+.*"/>
> 				<query pattern="^\s*drop\s+table\s+.*"/>
> 				<query pattern="^\s*create\s+table\s+.*"/>
> 			</route>
> 			<!-- filter out any queries for table2 -->
> 			<!--
> 			<filter>
> 				<query pattern="^\s*select\s+.*\s+from\s+table2"/>
> 				<query pattern="^\s*insert\s+into\s+table2"/>
> 				<query pattern="^\s*update\s+table2"/>
> 				<query pattern="^\s*delete\s+from\s+table2"/>
> 				<query pattern="^\s*drop\s+table\s+table2"/>
> 				<query pattern="^\s*create\s+table\s+table2"/>
> 			</filter>  -->
> 			<!-- send any other queries to slave -->
> 			<route host="" port="9001" socket="/tmp/slave.socke">
> 				<!--<query pattern="^\s*select\s+.*\s+from\s+.*">  -->
> 				<query pattern=".*"/>
> 			</route>
> 		</router>
> 	</instance>
>
> </instances>
>
> ------------------------------------------------------------------------------
> Virtualization&  Cloud Management Using Capacity Planning
> Cloud computing makes use of virtualization - but cloud computing
> also focuses on allowing computing to be delivered as a service.
> http://www.accelacomm.com/jaw/sfnl/114/51521223/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com