Re: How to modify the application to implement the separation of write/read

On Mar 11, 2010, at 7:30 PM, mqboss wrote:

> Hi all, 
> 
> I want to test the mode of Master/Slave for mysql.
> Also I want to separate the write and read of sql requests, that is, I want to make Slave server of mysql to handle read requests and make Master server to handle write requests.
> 
> I want to know how to modify the code of my application. Some people suggest me to use mysql-proxy to access mysql server.
> I checked the version of mysql-proxy, I found the latest version is just 0.8.0.
> Does someone have met this problem? How to implement the separation of read and write? 
> Please give me some advice.
> 
> I use java with the Connector/L jdbc driver, I checked the webpage of http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html, it suggests to use "ReplicationDriver" to replace common driver.
> I use Hibernate to access Mysql, the connection poll is c3p0-0.9.0, can I just need to modify the configuration of JDBC URL, like "jdbc:mysql:replication://master,slave1,slave2,slave3/test" to implement the separation of write/read? 
> After configuring JDBC URL like this, can I make the query SQL requests to access the Slave server, the update/insert/delete SQL requests to access the Master server, and if the Master server is shotdown, the driver will failover to the Slave server?
> I am not sure whether do I need to modify something else? Like my application code.

Peter,

If you use "jdbc:mysql:replication", you still have to change your application to let the driver know whether it can use a slave or not. You do this by calling Connection.setReadOnly(true) to direct queries to the slave(s), and Connection.setReadOnly(false) to direct queries to the master.

If the master is unavailable, with "jdbc:mysql:replication" you do not get failover if you are trying to write to the master, as it's a little bit more complicated than the driver can handle to promote one of the existing slaves to a master.

	-Mark
-- 
Mark Matthews
Principal Software Developer - Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html






-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]