Resin MySQL and RDS setup with Amazon RDS

This is a continuance of our earlier tutorial on Resin cloud deploy on Amazon.

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