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

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

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...