Amazon Opsworks RDS, EC2, Rails 3, Phusion - Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) (Mysql2::Error)

Question

I'm really close to having a simple Rails app deployed on Amazon Opsworks - using an EC2 instance running Phusion Passenger, and an RDS instance for the database server (it runs mySQL). The app code is grabbed from my Git Repo.

Here's the issue.

The EC2 instance will serve up my static ROR home page - the public/index.html file, so I know the app is in there, and being served.

The trouble happens when I try to do an HTTP GET to fetch representations of resources from the database.

Here is the exact error code:

"Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) (Mysql2::Error)"

I'm guessing it's trying to connect to a local MySQL server on my laptop through a socket, instead of trying to connect to the Amazon RDS instance (the MySQL database server) that I specified in database.yml.

Here is my database.yml:

development:
adapter: mysql2
encoding: utf8
database: *********_development
pool: 5
username: ****
password: ****
socket: /tmp/mysql.sock
host: localhost


test:
 adapter: mysql2
 encoding: utf8
 database: ********_test
 pool: 5
 username: ********
 password: ****
 socket: /tmp/mysql.sock
 host: localhost


production:
 adapter: mysql2 
 encoding: utf8
 database: *****
 username: ******
 password: *****
 host: ***dbinstance.*********.us-west-2.rds.amazonaws.com
 port: 3306 

How can I get the rails app to use the production database I specify in database.yml, instead of always trying to connect to a local database?

Answer 1

Opsworks doesn't use the database.yml you are using in your application. To get this working you need to provide the Opsworks scripts with the correct credentials. You can add custom JSON to your stack. Please see here to get an idea how it works:

https://forums.aws.amazon.com/thread.jspa?threadID=119823

I added a custom JSON to my stack settings like this:

{
  "deploy": {
    "yourappname": {
      "database": {
        "adapter": "mysql2",
        "encoding": "unicode",
        "host": "yourdbonrds.rds.amazonaws.com",
        "port": "3306",
        "database": "yourdb",
        "pool": "5",
        "username": "yourusername",
        "password": "yourpassword"
      }
    }
  }
}

All the "your"-fields need to be exchanged through your custom values.

One additional hint. In the RDS console you need to add the security group from Opswork Rails server to your RDS instance, so that the instance is allowed to connect to RDS. Please see here how to do:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithSecurityGroups.AuthorizingEC2.html

You can tryout if you can connect to the RDS instance from the Opsworks intance be ssh to the instance and then telnet to your RDS instance, e.g. telnet Your-RDS-Instance 3306