Search code examples
mysqlrrmysql

R - connecting to remote mysql server

I am trying to connect in R to remote mysql server, but without success.

That is my code - 

library(RMySQL)
drv = dbDriver("MySQL")
mydb = dbConnect(drv,host="*.*.*.*",dbname="dbname",user="user",pass="pass")

but I get the following error -

Failed to connect to database: Error: Access denied for user 'user'@'ec2----.eu-west-1.compute.amazonaws.com'

It is trying to connect to local host, and not the host I specified in the code.

What is wrong?

Solution

  • Looks to me like you haven't granted proper access for this user where queries are coming from ec2----.eu-west-1.compute.amazonaws.com.

    Try something like:

    CREATE USER 'user'@'ec2----.eu-west-1.compute.amazonaws.com' IDENTIFIED BY 'password';

    GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'ec2----.eu-west-1.compute.amazonaws.com';