Bug #62079 ReplicationDriver does not allow different user credentials per server

• Submitted: 3 Aug 2011 22:55
• Modified: 4 Aug 2011 13:32
• Reporter: Tero Paananen
• Status: Not a Bug
• Category: Connector / J Documentation
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[3 Aug 2011 22:55] Tero Paananen

Description:
I've got a master/slave replication setup where the master server is locked down more securely than the slaves. I'd prefer not to share credentials between the servers.

However, since I can't specify different pairs of usernames and passwords per server in the JDBC connection configuration, I can't do that.

I'd like to have the ability to set different usernames and passwords on each server with something like this:

url: jdbc:mysql:replication:/master,slave1,slave2/testdb
user: topsikrit,read,read
password: wishyouknew,12345,12345

Not necessarily literally like that, of course, but you should get the idea.

Currently, given my setup, I just can't set up a single connection and have it work with MySQL Connector/J. I have to set up a read-only connection (and create a read-only acct on the master matching the credentials on the slaves), and a write connection with different credentials.

How to repeat:
Create a master/slave replication setup.

Create the same user acct on the slaves. Do not create this user acct on the master.

Create another user acct on the master. Do not create this user acct on the slaves.

Set up a jdbc:mysql:replication: connection to those servers in an application that does writes to master and reads from slaves. It's not doable.

[3 Aug 2011 23:11] Mark Matthews

This was fixed in 5.1.14, reference the changelog note:

Added support for hosts specified in the URL of the form: 
     address=(key=value), supported keys are:
       
       (protocol=tcp or pipe (for named pipes on Windows)
       (path=[] for named pipes)
       (host=[]) for TCP connections 
       (port=[]) for TCP connections 
       
       An example would be:
       
       jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=3306)(user=test)/db
       
      Any other parameters are treated as host-specific properties that follow 
      the conventions of the JDBC URL properties. This now allows per-host 
      overrides of any configuration property for multi-host connections 
      (failover, loadbalance, replication). We do recommend that the overrides 
      are limited to user, password, network timeouts and statement and 
      metadata cache sizes. Unexpected behavior may be observed with other 
      per-host overrides.

In your case, each host would get it's own address=...

[4 Aug 2011 13:32] Tero Paananen

Thanks Mark, that is working as intended.

Shouldn't it be documented somewhere in the docs (other than in changelogs), though?

Maybe here: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html