You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Michael Shea <mi...@nitido.com> on 2009/02/10 00:04:48 UTC
Cayenne + master/slave mysql database replication
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.
Re: Cayenne + master/slave mysql database replication
Posted by Michael Shea <mi...@nitido.com>.
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.
>>>
>>
>>
>
Re: Cayenne + master/slave mysql database replication
Posted by Andrus Adamchik <an...@objectstyle.org>.
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.
>>
>
>
Re: Cayenne + master/slave mysql database replication
Posted by Andrus Adamchik <an...@objectstyle.org>.
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.
>