Amazon RDS Master Slave configuration for JBoss Java EE 6 App

1
votes

I have a Java EE 6 app that connects to a standalone MySQL instance. Currently it connects to a localhost MySQL using datasource.xml (deployed on app server) and persistence.xml (deployed in application .war)

I wish to configure it to talk to an Amazon RDS Mysql instance running in master-slave mode. This means I need to configure my app to write only to the master and read only from the slaves.

How can I achieve this? I'm guessing this is a datasource.xml or persistence.xml edit?

2
mysqlamazon-ec2java-ee-6amazon-rds

2 Answers

0
votes

I don't think there's a way to do this automatically. You could setup two separate connection pools, and then you'd have to code your application to use the different data sources as required.

Instructions for JBoss 6 datasources here:

https://access.redhat.com/documentation/en-US/JBoss_Enterprise_BRMS_Platform/5/html/BRMS_Administrator_Guide/Configuring_a_Datasource_for_JBoss_Enterprise_Application_Platform_6.html

You can setup RDS as Multi-AZ for high availability, and you can then create one or more read-replicas for the read-only queries.

0
votes

You will need to use JDBC URL with driver 

com.mysql.jdbc.ReplicationDriver

this is picked up by URL like

jdbc:mysql:replication//master_ip:3306,slave_1_ip:3306

RDS will work with JDBC_CONNECTION_STRINGsystem variable.

----additional update------- Sample datasource.xml from JBOSS

<datasource jndi-name="java:jboss/datasources/MysqlDS"
    enabled="true" use-java-context="true" pool-name="MysqlDS" use-ccm="true">
    <connection-url>jdbc:mysql://${env.AMAZON_RDS_MYSQL_DB_HOST}:${env.AMAZON_RDS_MYSQL_DB_PORT}/${env.AMAZON_RDS_MYSQL_DB_NAME}?autoReconnect=true&amp;verifyServerCertificate=false&amp;useSSL=true&amp;requireSSL=true
    </connection-url>
    <driver>mysql</driver>
    <security>
        <user-name>${env.AMAZON_RDS_MYSQL_DB_USERNAME}</user-name>
        <password>${env.AMAZON_RDS_MYSQL_DB_PASSWORD}</password>
    </security>
    <validation>
        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
        <background-validation>true</background-validation>
    </validation>
    <pool>
        <flush-strategy>IdleConnections</flush-strategy>
        <allow-multiple-users />
    </pool>
</datasource>