2

I’m newbie to R with RStudio using Mac(OS X).

I successfully use sequel Pro to see DB with like this.

enter image description here

I use dbConnect with RMySQL and DBI(below code) with RStudio.

library(DBI)
library(RMySQL)
con <-  dbConnect(RMySQL::MySQL(), 
              username = "username",
              password = "password",
              host = "hostname-xxx.ap-northeast-1.rds.amazonaws.com",
              port = 3306, 
              dbname = "dbname"
)

but I've got below error.

Error in .local(drv, ...)
  Failed to connect to database: Error: Access denied for user 'username'@'yyyyyyyyyyymarunouchi.tokyo.ocn.ne.jp' (using password: YES)

So ssh tunnel like this using terminal.

ssh -f sshuser@xx.xxx.xx.xx -i ~/.ssh/ssh_key -L 3306:hostname-xxx.ap-northeast-1.rds.amazonaws.com:3306 -N

and successfully logined.

after that, in order to confirm, executed below command(in terminal) but failed after entering correct password.

mysql -h 127.0.0.1 -p -u username dbname

with error code 

ERROR 1044 (42000): Access denied for user 'username'@'localhost' to database 'dbname'

(other machine with same username successfully login. I didn't know why...)

and dbConnect(RMySQL with RStudio) is showing same above error.

Anyone same situation? Please tell me what to do.

Thank you.

Tsuyoshi Endo
  • 727
  • 1
  • 7
  • 17
  • This is just a matter of database credentials. You need to set up a user in your DBMS who has permission to access the DB that you login to and then use that account + password to login. This may not be the same as the account that you use to login to the server. https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql – Hack-R Jul 08 '16 at 03:43

1 Answers1

0

This is just a matter of database credentials. You need to set up a user in your DBMS who has permission to access the DB that you login to and then use that account + password to login. This may not be the same as the account that you use to login to the server.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

If this gives you any trouble then instead of granting ALL PRIVILEGES grant each permission one at a time. This is rare but it happens, especially with other DB's like Hive.

Hack-R
  • 22,422
  • 14
  • 75
  • 131
  • If it is database credential matter, the "username" account settings for DB should be wrong, so wouldn't the ’mysql -h 127.0...’ command execute successfully for all of the terminals, or fail on all of them? ```Other machine with same username successfully login. I didn't know why..``` – Tsuyoshi Endo Jul 08 '16 at 05:19
  • ` sql -h 127.0.0.1 -p password -u username dbname ` disappear above error and mysql command execute successfully in terminal. But dbConnect in RStudio occurs error:` Error in .local(drv, ...): Failed to connect to database: Error: Can't connect to MySQL server on 'hostname-xxx.ap-northeast-1.rds.amazonaws.com' (60) ` – Tsuyoshi Endo Jul 19 '16 at 09:15