Connection to MySQL Server using RMySQL Library through Bastion in R

Question

On my local machine, I have ssh into the bastion where I can then connect to the remote MySQL server. I know that this is working because in terminal, it says that I have successfully connected and when I use an app like SQLPro and attempt to connect to the MySQL server with the correct permissions, I am able to successfully log in. Also, the command line

mysql -u username -p

works after I ssh.

Now, I am trying to use the library RMySQL to connect to the server and using

con<-dbConnect(MySQL(), user = "username", password = "pw", host = "127.0.0.1")

I get the return

Error in .local(drv, ...) : Failed to connect to database: Error: Can't connect to MySQL server on '127.0.0.1' (61)

It seems that R cannot determine that I have connected to the bastion. I say this because I have used the line above before on the remote server and it worked just fine.

Answer 1

I'm running R on linux.

After a few hours of searching, the following documentation for AWS finally gave me the command I needed to connect to an RDS instance via an AWS bastion host:

https://aws.amazon.com/premiumsupport/knowledge-center/rds-connect-using-bastion-host-linux/

The "syntax 2" at the above link worked for me to set up the tunnel:

ssh -i "Private_key.pem" -f -N -L 3306:RDS_Instance_Endpoint:3306 ec2-user@EC2-Instance_Endpoint -v

This successfully forwarded my local port 127.0.0.1:3306 to the RDS port 3306.

I then connected to the RDS instance from within R with just:

cn = dbConnect(RMariaDB::MariaDB(), user = "myDataBaseUserName", password = "myPassword", host = "127.0.0.1", dbname = "mySchemaName")

Answer 2

con<-dbConnect(MySQL(), user = "username", password = "pw", host = "localhost")

If you have a workbench then go to server-> client connection and check the Host name. Your host name might be incorrect