Connecting to DB via R


#1

Hi,
I’m trying to connect to my database via R (a statistical programming language). I’m running the script from my computer (not my server), and I’ve added my ip to accepted hosts. When I run the code

m<-dbDriver("MySQL")
con<-dbConnect(m,user="username", password="pw", host="hostname", dbname="db")

I get the following error:

Error in .local(drv, ...) : 
  Failed to connect to database: Error: Can't connect to MySQL server on 'mysql.ahubers.com' (0)

I have ran code similar to this before without issues on separate databases (not hosted by dreamhost), so I’m wondering if someone else has accessed their db like this or has an idea for what to try? It feels like my changes to accepted hosts isn’t going through (I’ve tried adding wild cards to both ends of my ip).

thanks


#2

Just messaging that I tried this with a separate IP and it worked. False alarm everybody.


#3

If your data has any value, you should create an SSH tunnel and connect through that. Also you should keep your credentials in .my.cnf so that you don’t accidentally commit them to a repository or share them with others. Check the example in ?dbConnect, but basically you can just use con <- dbConnect(dbDriver(‘MySQL’));


#4

I tried this to no avail… Although I agree, in the future I will change to that. I have taken my computer from my house to separate internet (my college’s) and it worked. So it’s something to do with my internet. I know it’s not my IP because I would receive a different error if my IP were blocked…

thanks!


#5

You’ll need to make sure port 3306 is not blocked. I’d bet most home routers have it blocked by default (as it should be) which is another reason to set up a tunnel. I set up a tunnel with ssh -L 3307:database.domain.tld:3306 user@domain.tld and then connect to the database as localhost:3307

also, if your data is valuable, you should change your database subdomain to something other than mysql, otherwise someone could launch a dictionary attack at ahubers.com/dh_phpmyadmin/mysql.ahubers.com/ By changing it to something like s5g6o2t3hcdr4b7p, you at least get a little security through obscurity.