CODEWITHSUNDEEP
mysqlrrmysql
fjori
fjori

Reputation: 119

connecting to database using RMySQL and .my.cnf file in R

I am trying to connect to my company's MySQL database from R, but am not allowed to keep the username and password in my code, and so I created a .my.cnf file. My .my.cnf file looks like this (password, dbname and hostnames adjusted):

[dbname]
username = admin-read
port = 3306
password = mypassword
host = myhost.us-west-2.rds.amazonaws.com
datasets = dbname

Then I run the following code in R to (try to) connect to the database, and receive the following error:

rmysql.settingsfile <- "~/.my.cnf"
rmysql.db <- "dbname"
drv <- dbDriver("MySQL")
con <- dbConnect(drv, default.file = rmysql.settingsfile, group = rmysql.db, user = NULL, password = NULL)

Error in .local(drv, ...) : 
Failed to connect to database: Error: Access denied for user 'myname'@'ec2-publicip.us-west-2.compute.amazonaws.com' (using password: YES)

It is a pain that this isn't working. Initially, I had written my code with the username and password passed to the dbConnect() function, as so, and connecting to the database worked fine:

my_connection <- dbConnect(
  MySQL(),
  user="admin-read",
  dbname="dbname",
  host="myhost.us-west-2.rds.amazonaws.com",
  password="mypassword"
)

... and connection in this way worked okay.

Any help with the error, and how I could fix this, would be greatly appreciated. Thanks!

Upvotes: 1

Views: 1727

Answers (2)

fe_esalq_agro
fe_esalq_agro

Reputation: 1

in my case i used "" for all the options inside the file .cnf

[wp]
user="wordpress"
port="3306"
password="xxxxxxxxxx"
host="127.0.0.1"
database="test"

Upvotes: 0

akond
akond

Reputation: 16060

Make sure your *.conf file has all the syntax allright:

[wp]
user=wordpress
port=3306
password=xxxxxxxxxx
host=127.0.0.1
database=test

Upvotes: 1

Related Questions