I ran into a few configuration problems when I tried to push a Grails webapp into production and make it talk to a MySQL database instead of the standard HSQL database. These notes might be useful to someone who is pulling their hair out trying to get Grails talking to MySQL database.
My production machine is a CentOS 5 box running MySQL 5, Sun JDK 5, and Tomcat 5.5 (incidentally, my webapp's version happened to be 0.5 which rounds out the '5' theme nicely).
I started by pulling down the
MySQL JDBC driver and dropping it into my
lib directory. I then updated the production section in my
DataSource.groovy file:
...
production {
dataSource {
pooling = true
driverClassName = "com.mysql.jdbc.Driver"
dbCreate = "create"
url = "jdbc:mysql://localhost:3306/grails_webapp"
username = "grailsuser"
password = "XXXX"
}
}
...
I then
grails wared things up on my local machine and copied it over to the production server.
Over on the production server, I connected to MySQL with the
mysql command line utility to create my database and user:
create database grails_webapp;
grant all privileges on grails_webapp.* to 'grailsuser'@'localhost' identified by 'XXXX';
flush privileges;
I verified things worked by connecting to the new database as the newly created user and creating a table.
I dropped the war into my
tomcat/webapps directory and watched the logs. The webapp tries to start but quickly dies, leaving behind a log full of
Communications link failure and
Connection refused messages. I could see that the MySQL JDBC driver was found, but for whatever reason it couldn't connect to the database server.
After double checking my
DataSource.groovy file to make sure my URL, username, and password were all spelled correctly and connecting again locally from the command line to ensure that the database is indeed running, I started Googling.
Google revealed that one common problem is to forget to enable networking in MySQL. I double checked my
my.cnf and confirmed that I had enabled networking by specifying a port and an address to bind to. This is where I noticed that the bound address wasn't
localhost or
127.0.0.1, but the actual IP address of the machine.
Normally you would expect MySQL to only need to listen locally. In my case, however, the production MySQL server replicates to a slave server for backup, so it needs to listen on the external interface. The firewall locked down to prevent all connections to the MySQL port except from the slave server.
On a hunch, I went back and changed my database URL to
jdbc:mysql://the.ip.addr.ess:3306/grails_webapp and tried re-deploying. Things still failed, but with
access denied errors instead of
connection refused errors. Progress.
The final piece of the puzzle was to go and grant my
grailsuser access on all addresses instead of just localhost:
grant all privileges on grails_webapp.* to 'grailsuser'@'%' identified by 'XXXX';
flush privileges;
Once this was done, I re-deployed again and was greeted with an error free log. Huzzah!
