Mithilesh Kumar Mithilesh Kumar - 4 months ago 30
MySQL Question

Error in connecting R to MySQL running on Amazon ec2 Ubuntu machine

I have recently configured MySQL on my Amazon EC2 Ubuntu machine. I am trying to connect MySQL db to RStudio running on the same machine. I am using the R package

RMySQL
for the same.

My R-code:

library(dbConnect)
library(RMySQL)
con = dbConnect(MySQL(), user='user1', password='pass1', dbname='db1', host='http://ec2-*********.us-west-2.compute.amazonaws.com/')


The error message:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.22/angular.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/knockout/3.1.0/knockout-min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.4.9/d3.min.js"></script>
Error in mysqlNewConnection(drv, ...) :
RS-DBI driver: (Failed to connect to database: Error: Unknown MySQL server host 'http://ec2-52-24-185-205.us-west-2.compute.amazonaws.com/' (2)

<!-- end snippet -->

Answer

You should be able to connect on an Ubuntu machine to a MySQL database using the following:

library("RMySQL")

mySqlCreds <- list(dbhostname = "YourHostIP",
               dbname   = "YourDB",
               username = "YourUserName",
               pass = "YourPassword",
               port = 1111
                 )

drv <- dbDriver("MySQL")
dbConnect(drv, host=mySqlCreds$dbhostname, dbname=mySqlCreds$dbname, 
        user=mySqlCreds$username, password=mySqlCreds$pass, port = mySqlCreds$port)

Where of course the port is whichever port you or your DBA has specified...

Comments