Thread: [Sqlrelay-discussion] r/w splitting configuration question
Brought to you by:
mused
Menu
▾
▴
sqlrelay-discussion
|
From: <jan...@id...> - 2012-03-08 11:33:48
|
dear sqlrelay list, i try to configure r/w splitting. i am able to start the following instances "master" & "slave" but not the router instance called "test". The error message is : unexpected tag <query> within <route> Couldn't parse config file /opt/sqlrelay-0.44/etc/sqlrelay.conf. Couldn't find id test. By the way what is the purpose of "sqlr-connection-router" ? Should "sqlr-connection-router" or "sqlr-start" be used to start the router instance ? Anyway it fails with both programs. The config file used is nearly the same as stated on http://sqlrelay.sourceforge.net/sqlrelay/router.html # sqlr-start -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf [...] # sqlr-start -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf [...] # pgrep -lf sql 27998 sqlr-listener -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 28028 sqlr-connection-mysql -id master -connectionid db1 -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 28060 sqlr-scaler -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 28067 sqlr-cachemanager 29266 sqlr-listener -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 29271 sqlr-connection-mysql -id slave -connectionid db1 -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 29288 sqlr-scaler -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf What am I doing wrong ? Any hints ? kind regards, Jan |
|
From: <jan...@id...> - 2012-03-08 11:32:13
|
ok sorry. If I omit the query statements (which is senseless) I am able to start the router instance. # sqlr-start -id test -config /opt/sqlrelay-0.44/etc/sqlrelay.conf Starting listener: sqlr-listener -id test -config /opt/sqlrelay-0.44/etc/sqlrelay.conf Starting 1 connections to defaultid : sqlr-connection-router -id test -connectionid defaultid -config /opt/sqlrelay-0.44/etc/sqlrelay.conf Starting scaler: sqlr-scaler -id test -config /opt/sqlrelay-0.44/etc/sqlrelay.conf cache manager already running. [...] But how can I use the query patterns sucessfully ? kind regards, --- Jan ________________________________________ Von: Zeller, Jan (ID) Gesendet: Donnerstag, 8. März 2012 12:14 An: sql...@li... Betreff: r/w splitting configuration question dear sqlrelay list, i try to configure r/w splitting. i am able to start the following instances "master" & "slave" but not the router instance called "test". The error message is : unexpected tag <query> within <route> Couldn't parse config file /opt/sqlrelay-0.44/etc/sqlrelay.conf. Couldn't find id test. By the way what is the purpose of "sqlr-connection-router" ? Should "sqlr-connection-router" or "sqlr-start" be used to start the router instance ? Anyway it fails with both programs. The config file used is nearly the same as stated on http://sqlrelay.sourceforge.net/sqlrelay/router.html # sqlr-start -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf [...] # sqlr-start -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf [...] # pgrep -lf sql 27998 sqlr-listener -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 28028 sqlr-connection-mysql -id master -connectionid db1 -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 28060 sqlr-scaler -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 28067 sqlr-cachemanager 29266 sqlr-listener -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 29271 sqlr-connection-mysql -id slave -connectionid db1 -config /opt/sqlrelay-0.44/etc/sqlrelay.conf 29288 sqlr-scaler -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf What am I doing wrong ? Any hints ? kind regards, Jan |
|
From: David M. <dav...@fi...> - 2012-03-09 02:42:36
|
Hi Jan,
It looks like I introduced a bug in the last release. Here is a quick fix:
Edit src/util/sqlrconfigfile.cpp and look for the following code, near
line 558:
// Filter section, nested (runquery*)
case FILTER_TAG:
currentname="filter";
if (!charstring::compare(name,"runquery")) {
thistag=RUNQUERY_TAG;
} else {
ok=false;
}
break;
// Filter section, nested (runquery*)
case ROUTE_TAG:
currentname="route";
if (!charstring::compare(name,"runquery")) {
thistag=RUNQUERY_TAG;
} else {
ok=false;
}
break;
Change instances of "runquery" to "query" and RUNQUERY_TAG to QUERY_TAG,
as follows:
// Filter section, nested (query*)
case FILTER_TAG:
currentname="filter";
if (!charstring::compare(name,"query")) {
thistag=QUERY_TAG;
} else {
ok=false;
}
break;
// Filter section, nested (query*)
case ROUTE_TAG:
currentname="route";
if (!charstring::compare(name,"query")) {
thistag=QUERY_TAG;
} else {
ok=false;
}
break;
I guess I got a little too aggressive with search and replace. With
those modifications, it should work as expected.
Sorry for the confusion.
David Muse
dav...@fi...
On 03/08/2012 06:31 AM, jan...@id... wrote:
> ok sorry.
> If I omit the query statements (which is senseless) I am able to start the router instance.
>
> # sqlr-start -id test -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
> Starting listener:
> sqlr-listener -id test -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
>
> Starting 1 connections to defaultid :
> sqlr-connection-router -id test -connectionid defaultid -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
>
> Starting scaler:
> sqlr-scaler -id test -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
>
> cache manager already running.
> [...]
>
> But how can I use the query patterns sucessfully ?
>
> kind regards,
>
> ---
>
> Jan
> ________________________________________
> Von: Zeller, Jan (ID)
> Gesendet: Donnerstag, 8. März 2012 12:14
> An: sql...@li...
> Betreff: r/w splitting configuration question
>
> dear sqlrelay list,
>
> i try to configure r/w splitting.
>
> i am able to start the following instances "master"& "slave" but not the router instance called "test".
>
> The error message is :
>
> unexpected tag<query> within<route>
> Couldn't parse config file /opt/sqlrelay-0.44/etc/sqlrelay.conf.
> Couldn't find id test.
>
>
> By the way what is the purpose of "sqlr-connection-router" ?
> Should "sqlr-connection-router" or "sqlr-start" be used to start the router instance ?
> Anyway it fails with both programs.
>
> The config file used is nearly the same as stated on http://sqlrelay.sourceforge.net/sqlrelay/router.html
>
>
> # sqlr-start -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
> [...]
> # sqlr-start -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
> [...]
>
> # pgrep -lf sql
> 27998 sqlr-listener -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
> 28028 sqlr-connection-mysql -id master -connectionid db1 -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
> 28060 sqlr-scaler -id master -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
> 28067 sqlr-cachemanager
> 29266 sqlr-listener -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
> 29271 sqlr-connection-mysql -id slave -connectionid db1 -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
> 29288 sqlr-scaler -id slave -config /opt/sqlrelay-0.44/etc/sqlrelay.conf
>
> What am I doing wrong ?
> Any hints ?
>
> kind regards,
>
> Jan
>
> ------------------------------------------------------------------------------
> 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
|
|
From: <jan...@id...> - 2012-03-09 10:43:02
|
thanks David for your fix. This solved the problem ! Very cool.
Unfortunately I can't use the router instance 'test'.
# query -config /opt/sqlrelay-0.44/etc/sqlrelay.conf -id test "select * from ut_online;"
(If using 'master' or 'slave' instance query works.)
The connections are as follows :
# lsof -ni4 |grep sql
sqlr-list 12967 nobody 6u IPv4 116943 0t0 TCP *:9000 (LISTEN)
sqlr-conn 12986 nobody 3u IPv4 116940 0t0 TCP 192.168.122.13:34141->192.168.122.152:mysql (ESTABLISHED)
sqlr-list 13081 nobody 6u IPv4 117139 0t0 TCP *:9001 (LISTEN)
sqlr-conn 13096 nobody 4u IPv4 117114 0t0 TCP 192.168.122.13:33377->192.168.122.153:mysql (ESTABLISHED)
sqlr-list 23694 nobody 7u IPv4 127196 0t0 TCP *:9002 (LISTEN)
192.168.122.13
----------------------
port 9000 'master' instance
port 9001 'slave' instance
port 9002 'test' instance (router)
192.168.122.152 MySQL master
192.168.122.153 MySQL slave
Furthermore I can't use neither
$ mysql -h 192.168.122.13 -P 9000 -p -e "select * from ut_online;"
Enter password:
nor -P 9001
(output is missing)
MySQL client version is :
$ mysql --version
mysql Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (x86_64) using readline 6.2
Could you please point me to the right direction.
kind regards,
---
Jan
________________________________________
Von: David Muse [dav...@fi...]
Gesendet: Freitag, 9. März 2012 03:40
An: Discussion of topics related to SQL Relay
Betreff: Re: [Sqlrelay-discussion] r/w splitting configuration question
Hi Jan,
It looks like I introduced a bug in the last release. Here is a quick fix:
Edit src/util/sqlrconfigfile.cpp and look for the following code, near
line 558:
// Filter section, nested (runquery*)
case FILTER_TAG:
currentname="filter";
if (!charstring::compare(name,"runquery")) {
thistag=RUNQUERY_TAG;
} else {
ok=false;
}
break;
// Filter section, nested (runquery*)
case ROUTE_TAG:
currentname="route";
if (!charstring::compare(name,"runquery")) {
thistag=RUNQUERY_TAG;
} else {
ok=false;
}
break;
Change instances of "runquery" to "query" and RUNQUERY_TAG to QUERY_TAG,
as follows:
// Filter section, nested (query*)
case FILTER_TAG:
currentname="filter";
if (!charstring::compare(name,"query")) {
thistag=QUERY_TAG;
} else {
ok=false;
}
break;
// Filter section, nested (query*)
case ROUTE_TAG:
currentname="route";
if (!charstring::compare(name,"query")) {
thistag=QUERY_TAG;
} else {
ok=false;
}
break;
I guess I got a little too aggressive with search and replace. With
those modifications, it should work as expected.
Sorry for the confusion.
David Muse
dav...@fi...
|
|
From: David M. <dav...@fi...> - 2012-03-09 17:29:45
|
Could you send me your sqlrelay.conf file?
Dave
On 03/09/2012 05:42 AM, jan...@id... wrote:
> thanks David for your fix. This solved the problem ! Very cool.
> Unfortunately I can't use the router instance 'test'.
>
> # query -config /opt/sqlrelay-0.44/etc/sqlrelay.conf -id test "select * from ut_online;"
>
> (If using 'master' or 'slave' instance query works.)
>
> The connections are as follows :
>
> # lsof -ni4 |grep sql
> sqlr-list 12967 nobody 6u IPv4 116943 0t0 TCP *:9000 (LISTEN)
> sqlr-conn 12986 nobody 3u IPv4 116940 0t0 TCP 192.168.122.13:34141->192.168.122.152:mysql (ESTABLISHED)
> sqlr-list 13081 nobody 6u IPv4 117139 0t0 TCP *:9001 (LISTEN)
> sqlr-conn 13096 nobody 4u IPv4 117114 0t0 TCP 192.168.122.13:33377->192.168.122.153:mysql (ESTABLISHED)
> sqlr-list 23694 nobody 7u IPv4 127196 0t0 TCP *:9002 (LISTEN)
>
>
> 192.168.122.13
> ----------------------
> port 9000 'master' instance
> port 9001 'slave' instance
> port 9002 'test' instance (router)
>
> 192.168.122.152 MySQL master
> 192.168.122.153 MySQL slave
>
> Furthermore I can't use neither
>
> $ mysql -h 192.168.122.13 -P 9000 -p -e "select * from ut_online;"
> Enter password:
>
> nor -P 9001
>
> (output is missing)
>
>
> MySQL client version is :
>
> $ mysql --version
> mysql Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (x86_64) using readline 6.2
>
>
> Could you please point me to the right direction.
>
> kind regards,
>
> ---
>
> Jan
>
>
> ________________________________________
> Von: David Muse [dav...@fi...]
> Gesendet: Freitag, 9. März 2012 03:40
> An: Discussion of topics related to SQL Relay
> Betreff: Re: [Sqlrelay-discussion] r/w splitting configuration question
>
> Hi Jan,
>
> It looks like I introduced a bug in the last release. Here is a quick fix:
>
> Edit src/util/sqlrconfigfile.cpp and look for the following code, near
> line 558:
>
> // Filter section, nested (runquery*)
> case FILTER_TAG:
> currentname="filter";
> if (!charstring::compare(name,"runquery")) {
> thistag=RUNQUERY_TAG;
> } else {
> ok=false;
> }
> break;
>
> // Filter section, nested (runquery*)
> case ROUTE_TAG:
> currentname="route";
> if (!charstring::compare(name,"runquery")) {
> thistag=RUNQUERY_TAG;
> } else {
> ok=false;
> }
> break;
>
> Change instances of "runquery" to "query" and RUNQUERY_TAG to QUERY_TAG,
> as follows:
>
> // Filter section, nested (query*)
> case FILTER_TAG:
> currentname="filter";
> if (!charstring::compare(name,"query")) {
> thistag=QUERY_TAG;
> } else {
> ok=false;
> }
> break;
>
> // Filter section, nested (query*)
> case ROUTE_TAG:
> currentname="route";
> if (!charstring::compare(name,"query")) {
> thistag=QUERY_TAG;
> } else {
> ok=false;
> }
> break;
>
> I guess I got a little too aggressive with search and replace. With
> those modifications, it should work as expected.
>
> Sorry for the confusion.
>
> David Muse
> dav...@fi...
> ------------------------------------------------------------------------------
> 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
|
|
From: <jan...@id...> - 2012-03-09 18:41:28
|
________________________________________ 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> |
|
From: David M. <dav...@fi...> - 2012-05-19 22:05:44
|
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
|
|
From: <jan...@id...> - 2012-05-29 13:39:42
|
> -----Ursprüngliche Nachricht----- > Von: David Muse [mailto:dav...@fi...] > Gesendet: Sonntag, 20. Mai 2012 00:05 > An: Discussion of topics related to SQL Relay > Betreff: Re: [Sqlrelay-discussion] r/w splitting configuration question > > 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... [Zeller, Jan (ID)] Hi David, thanks a lot for your help. Unfortunately I'am too dumb to use your software. I started from scratch, followed your guidlines on http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/mysql.html#sqlrelay used the following config file # cat /opt/sqlrelay-0.45/etc/sqlrelay.conf <?xml version="1.0"?> <!DOCTYPE instances SYSTEM "sqlrelay.dtd"> <instances> <instance id="mysqltest" port="9000" socket="/tmp/mysqltest.socket" dbase="mysql" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass"> <users> <user user="mysqltest" password="mysqltest"/> </users> <connections> <connection connectionid="mysqltest" string="user=testuser;password=testpassword;db=testdb;host=192.168.122.152" metric="1"/> </connections> </instance> </instances> and with that I was able to do the following : # query localhost 9000 /tmp/mysqltest.socket mysqltest mysqltest "desc testtable;" "col1","char(40)","YES","","","" "col2","int(11)","YES","","","" # sqlrsh -id mysqltest SQLRShell - Version 0.22 Connected to: localhost:9000 as mysqltest type help; for a help. [...] 0> show tables; Tables_in_testdb ================ testtable Rows Returned : 1 Fields Returned : 1 System time : 10000 1> currentdb; testdb [...] But how can my regular mysql client (finally the application) can connect to ? Did I misunderstood something ? kind regards, Jan |
|
From: David M. <dav...@fi...> - 2012-05-31 20:33:16
|
On 05/29/2012 09:20 AM, jan...@id... wrote: >> -----Ursprüngliche Nachricht----- >> Von: David Muse [mailto:dav...@fi...] >> Gesendet: Sonntag, 20. Mai 2012 00:05 >> An: Discussion of topics related to SQL Relay >> Betreff: Re: [Sqlrelay-discussion] r/w splitting configuration question >> >> 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... > [Zeller, Jan (ID)] > Hi David, > > thanks a lot for your help. > Unfortunately I'am too dumb to use your software. > > I started from scratch, followed your guidlines on http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/mysql.html#sqlrelay used the following config file > > # cat /opt/sqlrelay-0.45/etc/sqlrelay.conf > <?xml version="1.0"?> > <!DOCTYPE instances SYSTEM "sqlrelay.dtd"> > <instances> > <instance id="mysqltest" port="9000" socket="/tmp/mysqltest.socket" dbase="mysql" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass"> > <users> > <user user="mysqltest" password="mysqltest"/> > </users> > <connections> > <connection connectionid="mysqltest" string="user=testuser;password=testpassword;db=testdb;host=192.168.122.152" metric="1"/> > </connections> > </instance> > </instances> > > and with that I was able to do the following : > > # query localhost 9000 /tmp/mysqltest.socket mysqltest mysqltest "desc testtable;" > "col1","char(40)","YES","","","" > "col2","int(11)","YES","","","" > > # sqlrsh -id mysqltest > SQLRShell - Version 0.22 > Connected to: localhost:9000 as mysqltest > > type help; for a help. > [...] > > 0> show tables; > Tables_in_testdb > ================ > testtable > Rows Returned : 1 > Fields Returned : 1 > System time : 10000 > > 1> currentdb; > testdb > [...] > > But how can my regular mysql client (finally the application) can connect to ? Did I misunderstood something ? > > kind regards, > > Jan Hi Jan, I assume that you mean the MySQL command line client "mysql"... Since that program uses the MySQL Native API, rather than the SQL Relay API, you must use the SQL Relay Drop-in Replacement Library for MySQL which maps MySQL Native API calls to SQL Relay API calls. Directions for using it are here: http://sqlrelay.sourceforge.net/sqlrelay/dropin/mysql.html But basically, you run: export LD_PRELOAD=/usr/local/firstworks/lib/libmysql51sqlrelay.so Then aim the mysql program at SQL Relay rather than at the database directly: mysql --host=localhost --port=9000 --socket=/tmp/mysqltest.socket --user=mysqltest --password=mysqltest If your program also uses the MySQL Native API then the same process should work for it as well. Try this and let me know if you're still having trouble. SQL Relay has some complex features and it's not unusual to have a little trouble getting all of it to work together. I'm available for consulting services if you need them: http://sqlrelay.sourceforge.net/support.html I can usually get even the most complex configurations up and running in a few hours. Dave dav...@fi... |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X