Thanks for the replies, Andrus.
I think I will probably end up going with the MySQL Proxy solution,
since it looks like the proxy can also deal with replication delay, so I
won't have to worry about that in my application code:
http://jan.kneschke.de/projects/mysql/mysql-proxy/ (there's a bit about
Delay Adaptive Balancing).
Sounds like exactly what I need. =) Thanks!
Mike Shea.
> A colleague also pointed me this little tool that implements the magic
> switching:
>
> http://forge.mysql.com/wiki/MySQL_Proxy
>
> Andrus
>
>
> On Feb 10, 2009, at 9:05 AM, Andrus Adamchik wrote:
>
>> Interesting... I am using a similar setup with MySQL (a master and a
>> bunch of slaves), however we split the code into read-only and
>> read/write webapp modules and use separate URL's for each type. In
>> addition to proper load balancing, this adds a layer of security
>> (apps that are read-only are guaranteed to have no write access even
>> if there are programming errors that result in write attempts). Of
>> course this is not as generic (for instance I'd like my read-write
>> apps to still read from slaves).
>>
>> To handle it the way MySQL describes it, the simplest way is to
>> create a custom "replicating adapter" that is a subclass of
>> MySQLAdapter, and override 'getAction'. There's a few helper classes
>> involved there (MySQLActionBuilder, ...), but the goal is to override
>> MySQLSelectAction to call "connection.setReadOnly" before execution.
>>
>> If you are using SQLTemplates or ProcedureQueries, the approach is
>> similar, only you'll need to analyze the contents of the query to
>> decide whether this is a select or update.
>>
>> We may include this in Cayenne at some point, but as demonstrated
>> above, it should be easy to do it as a custom extension.
>>
>> Andrus
>>
>>
>> On Feb 10, 2009, at 1:04 AM, Michael Shea wrote:
>>> Hi guys,
>>>
>>> I am currently using Cayenne 3.0M4 in a web application, using a
>>> MySQL database.
>>>
>>> I have been asked to modify the application so that we can perform
>>> reads from one of N slave database servers, but writes will occur
>>> only on one master database server.
>>>
>>> Is it possible to do this with Cayenne? Anyone know how? =)
>>>
>>> I've looked at this briefly:
>>> http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html
>>>
>>>
>>> ... So it looks like I could configure a JNDI datasource that
>>> represents the master + all the slaves pretty easily. I just don't
>>> know how to get Cayenne to call "connection.setReadOnly(...)" as
>>> necessary.
>>>
>>>
>>> Thanks!
>>>
>>>
>>> Mike Shea.
>>>
>>
>>
>
This archive was generated by hypermail 2.0.0 : Tue Feb 10 2009 - 12:49:22 EST