Resin MySQL and RDS setup with Amazon RDS
From Resin 3.0
This is a continuance of our earlier tutorial on Resin cloud deploy on Amazon.
Resin_Cloud_deployment_with_Amazon Part 1
Resin_Cloud_deployment_with_Amazon_WS_EC2_and_Euca_Tools Part 2
Install MySQL on local box
First let's install MySQL create a user called bloguser with a password. Then let's create a database called blogdb.
$ sudo apt-get mysql-server
Startup MySQL client tool to create a new user and database:
$ mysql -u root -P
Next create a user called bloguser with the pass roofoo.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'bloguser'@'%' IDENTIFIED BY 'roofoo' WITH GRANT OPTION; Output: Query OK, 0 rows affected (0.00 sec)
Next create a database called blogdb.
mysql> create database blogdb; Output: Query OK, 1 row affected (0.00 sec)
Exit mysql.
mysql> exit; Output: Bye
Use Roo to switch to MySQL
In the examples folder start up roo and switch to using MySQL.
$ roo roo> jpa setup --database MYSQL --provider HIBERNATE --databaseName blogdb Output: Updated SPRING_CONFIG_ROOT/database.properties Please update your database details in src/main/resources/META-INF/spring/database.properties. Updated SPRING_CONFIG_ROOT/applicationContext.xml Updated ROOT/src/main/resources/META-INF/persistence.xml Updated ROOT/pom.xml [added dependency mysql:mysql-connector-java:5.1.18; removed dependency com.h2database:h2:1.3.161]
Find and modify the database properties for this application. (Later we will set this up in JNDI of Resin, but for now just modify the properties file).
$ find . -name "database.properties" Output ./src/main/resources/META-INF/spring/database.properties $ emacs ./src/main/resources/META-INF/spring/database.properties
Add the user name and password that we setup earlier.
#Updated at Thu Dec 01 12:57:30 PST 2011 #Thu Dec 01 12:57:30 PST 2011 database.password=roofoo database.url=jdbc\:mysql\://localhost\:3306/blogdb database.username=bloguser database.driverClassName=com.mysql.jdbc.Driver
Deploy and test what we have done so far
Now let's test out what we have so far. Build the project and deploy it to resin.
$ mvn package Output: ... [INFO] BUILD SUCCESS $ resinctl deploy ./target/blog-0.1.0.BUILD-SNAPSHOT.war -name blogm Output Deployed production/webapp/default/blogm from ./target/blog-0.1.0.BUILD-SNAPSHOT.war to hmux://127.0.0.1:6800
Now startup the blogm app and add a blog entry.
$ firefox http://localhost:8080/blogm/
If you like, you can fire up the mysql client and see if your new entry and blog table is in MySQL.
$ mysql -u root -p Enter password: mysql> use blogdb; mysql> show tables; +------------------+ | Tables_in_blogdb | +------------------+ | blog | +------------------+ 1 row in set (0.00 sec) mysql> select * from blog; +----+---------+-------+---------+ | id | message | title | version | +----+---------+-------+---------+ | 1 | sdf | sadf | 0 | +----+---------+-------+---------+ 1 row in set (0.00 sec) mysql> exit
Now change Resin and Roo to use JNDI instead of Spring configured datasource
Resin can monitor and manage database connection better than Spring alone. In order to do this you need to JNDI. It also good to enable JNDI for production so that a select few in operations can control the user name and password of the production system databases. Having JNDI allows your dev box and your prod box to have different usernames and passwords as well without rebuilding a special war file for each.
In this tutorial our dev box is Ubuntu and our prod box is Amazon AMI in the sky.
First tell Roo to use JNDI as follows:
$ roo
____ ____ ____
/ __ \/ __ \/ __ \
/ /_/ / / / / / / /
/ _, _/ /_/ / /_/ /
/_/ |_|\____/\____/ 1.2.0.RC1 [rev dcaa483]
...
roo> jpa setup --database MYSQL --provider HIBERNATE --databaseName blogdb --jndiDataSource jdbc/blogdb
Output:
Updated SPRING_CONFIG_ROOT/applicationContext.xml
roo> exit
Notice the addition of --jndiDataSource jdbc/blogdb.
Let's see the change it made.
$ find . -name applicationContext.xml
./src/main/resources/META-INF/spring/applicationContext.xml
$ grep -A 5 -B 5 jdbc ./src/main/resources/META-INF/spring/applicationContext.xml
</context:component-scan>
<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>
<tx:annotation-driven mode="aspectj" transaction-manager="transactionManager"/>
<jee:jndi-lookup id="dataSource" jndi-name="jdbc/blogdb"/>
<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
<property name="persistenceUnitName" value="persistenceUnit"/>
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
Now we need to add a JNDI entry for our MySQL database.
Create the file /etc/resin/local.d/database.xml
$ sudo emacs /etc/resin/local.d/database.xml
And add the contents as follows:
<resin xmlns="http://caucho.com/ns/resin"
xmlns:resin="urn:java:com.caucho.resin">
<database jndi-name="jdbc/blogdb">
<driver type="com.mysql.jdbc.Driver">
<url>jdbc:mysql://localhost:3306/blogdb</url>
<user>bloguser</user>
<password>roofoo</password>
</driver>
</database>
</resin>
Find and copy the mysql jar file from the maven local repo to Resin as follows:
Find it:
$ find ~/.m2/repository/ -name "*mysql*.jar" /home/rick/.m2/repository/mysql/mysql-connector-java/5.1.18/mysql-connector-java-5.1.18.jar
Now deploy it
$ sudo cp ~/.m2/repository/mysql/mysql-connector-java/5.1.18/mysql-connector-java-5.1.18.jar \
/usr/local/share/resin/lib/
Deploy and test what we have done so far
Now let's test out what we have so far. Build the project and deploy it to resin.
$ mvn package Output: ... [INFO] BUILD SUCCESS $ resinctl deploy ./target/blog-0.1.0.BUILD-SNAPSHOT.war -name blogjndi Output Deployed production/webapp/default/blogjndi from ./target/blog-0.1.0.BUILD-SNAPSHOT.war to hmux://127.0.0.1:6800
Now startup the blogm app and add a blog entry.
$ firefox http://localhost:8080/blogjndi/
Poke around with mysql client tool and see if your new blog entry is there.
Setting up variables
I don't like needing to modify XML all of the time. You can setup EL variables in database.xml that allow you to set key name value pairs in the resin.properties file as follows:
/etc/resin/resin.properties Add three properties to resin.properties
blogdb.url : localhost blogdb.user : bloguser blogdb.password : roofoo
/etc/resin/local.d/database.xml Change database.xml to use new properties.
<resin xmlns="http://caucho.com/ns/resin"
xmlns:resin="urn:java:com.caucho.resin">
<database jndi-name="jdbc/blogdb">
<driver type="com.mysql.jdbc.Driver">
<url>jdbc:mysql://${blogdb.url}:3306/blogdb</url>
<user>${blogdb.user}</user>
<password>${blogdb.password}</password>
</driver>
</database>
</resin>
This will be nice because later we can set the user-data for the VM and point to the Amazon RDS database.
Setup RDS database
Setup an RDS database using MySQL 5.1.7 (or as close to your MySQL as possible).
I use the same username password to make it simple. The only part that is going to change for sure is the URL.
Using the AWS web console modify the user-data of the resinserver we deployed earlier and add three properies.
user-data on resin server
blogdb.url : blogdb.cvolnlau763z.us-east-1.rds.amazonaws.com blogdb.user : bloguser blogdb.password : roofoo
Note that the URL should be the url of the Amazon RDS database that you just setup. Also you need to setup a database security group that allows the group of your vm instance access the database.
Start up the VM instance that we installed Resin on earlier and create a database.xml file in the /etc/resin/local.d/ as follows:
/etc/resin/local.d/database.xml Create database.properties on remote Amazon instance.
<resin xmlns="http://caucho.com/ns/resin"
xmlns:resin="urn:java:com.caucho.resin">
<database jndi-name="jdbc/blogdb">
<driver type="com.mysql.jdbc.Driver">
<url>jdbc:mysql://${blogdb.url}:3306/blogdb</url>
<user>${blogdb.user}</user>
<password>${blogdb.password}</password>
</driver>
</database>
</resin>
Next install the mysql driver on the Amazon instance.
$ sudo yum install mysql-connector-java $ sudo cp /usr/share/java/mysql-connector-java.jar /usr/local/share/resin/lib/
Now deploy the war file to the Amazon AMI
$ resinctl deploy target/blog-0.1.0.BUILD-SNAPSHOT.war -name blogdb -address 107.22.13.146 \
-port 8080 -user admin -password roofoo
Now test it out: http://ec2-107-22-13-146.compute-1.amazonaws.com:8080/blogdb/blogs/1
It worked for me. :)
