Can't Connect in R to MySQL Server through SSH

General
1

Background

I'm trying to pull data from a cloud MySQL Server. I just moved the database from a shared server to a dedicated cloud server. Everything works fine on the old location. The new location requires an SSH tunnel.

I can successfully connect using the same credentials in both Sequel Pro and MySQL Workbench, but I can't get the R script to play ball.

I can connect to mysql in the command line.

I do not have mysql installed locally.

Code:
Here's what's failing:

con <- dbConnect(RMariaDB::MariaDB(),
                 user = 'soundings_app',
                 password = keyringr::decrypt_kc_pw("Soundings_app"),
                 host = '127.0.0.1',
                 port = 3306,
                 dbname='UFO')

I also tried using the actual password as a string, and got the same error.

Error message

Error in connection_create(host, username, password, dbname, as.integer(port),  : 
  Failed to connect: Can't connect to MySQL server on '127.0.0.1' (57)

My Only Guess
The only thing I can think of is that SequelPro & Workbench both had a place to enter the location of my private key (~/.ssh).

UPDATE
I added the host to my ./ssh/config file as follows:

Host XXX.XXX.130.0
  LocalForward 3306 localhost:3306

Thanks for any guidance!

1 Like
2

Interesting topic i'm following. I think you'll need to do some sort of ssh tunneling before you attempt to connect to the database. I would love to see something like this added to the pool package that does the tunneling in the background. e.g

missing

ssh_host = "db.my_host.at"
ssh_user = "tunnelrat"
ssh_password= "pass123"
ssh_port="1234"
)

1 Like
3

I have the same question. Perhaps someone can get a little Stack love for answering:

I also walk through what I have gotten working from the console.