Hi,
recently I was reviewing the code a project which was having some database issues, while the review I found that the project environment was setup with the MySQL with one master node and two slave node, and I was surprised that in the code they have used the default JDBC driver for database connectivity which actually uses on single node for all read/write operations.
When asked to developer about the reason of using the default driver for database connectivity, he actually don’t knows that the default JDBC connector does not support replication aware connection. So I’ve realised that many of the developers actually don’t know about the replication aware JDBC connection driver, and I’ve decided to write this post.
There are very little difference between default JDBC connector which support single node mysql connection and ReplicationAware JDBC Connector which is used to create Replication aware JDBC connection using multi node mysql setup.
1) The DriverClass
As you all know that for different type of connection we need to load different driver class. The default JDBC Connector class uses com.mysql.jdbc.Driver driver class and ReplicationAware driver uses com.mysql.jdbc.ReplicationDriver driver class for connection.
2) The connection string
As in default JDBC connection we have only single node and in replication environment we can have n number of nodes, so our connection string will also be different for both connections.
while using default jdbc connector we use connection string as
jdbc:mysql://[database host][:port]/[database_name][?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]
and for replication aware jdbc connector we have to provide all server’s ip including master and slaves
jdbc:mysql:replication://[master host][:port],[slave host 1][:port][,[slave host 2][:port]]...[/[database_name]][?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]
thats it 🙂
you have done it. its the only difference between default JDBC connector and ReplicationAware JDBC connector.
See MySQL documentation here for list of configuration properties that can be appended to the JDBC URL.
One more thing that how to ensure that your operations redirecting to master and slave correctly:
1) Standalone Application:
if you are using standalone application then just call connection.setReadOnly(false) before any transaction and your all read/write operations will execute on master node and set readOnly flag to true if you want to execute queries from slave. ReplicationDriver will take care of your transactions and pick a slave from the list to execute query on a slave node. (Read configuring load balancing with connector/J)
2) Spring transactions:
If you are working with Spring, just add @Transactional annotation to the method with readOnly flag.
For read/write operations on master node, use @Transactional(readOnly = false) and all the database operations will go to the master only.
For read only operations with slave node, use @Transactional(readOnly = true) and all the database operations will go to the slave.
References:
MySQL Replication with Connector/J
MySQL Configuration Properties for Connector/J