Connecting to a remote database using RMySQL - access denied

Question

I am trying to connect to a remote mysql database by the following steps

1. SSH into their machine and keep the tunnel open then,

2. Using a SQL client (Sequel Pro) or RMySQL through RStudio Using the SQL client I can connect to their database and perform queries. However, using RMySQL when I try to connect via,

   mydb = dbConnect(dbDriver("MySQL"), user='uname', password='pwd', dbname='dbname', host='localhost',group='destination')
   

I am getting the following error,

Error in .local(drv, ...) : Failed to connect to database: Error: Access denied for user 'uname'@'localhost' (using password: YES)

Help would be appreciated.

Answer 1

Updating the query forcing to be TCP by specifying port resolved the issue.

mydb = dbConnect(dbDriver("MySQL"), user='uname', password='pwd', dbname='dbname', host='127.0.0.1',port = port)

Answer 2

Default port for mysql is 3306

install.packages("RMySQL")
library(RMySQL)
driver = dbDriver("MySQL");
connection = dbConnect(driver, user='user', password='password', dbname='db',host='host', port='3306');
query = dbGetQuery(connection, statement="SELECT * FROM your_table");