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.
All I've found that's similar is this SO article. It didn't really apply, as I never used localhost.
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. (Unless it ships with High Sierra; I have no idea. In any case, if there's a local MySQL instance, it's not running.)
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 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)
The only thing I can think of is that SequelPro & Workbench both had a place to enter the location of my private key (~/.ssh).
Can you explain what I'm doing wrong?
I added the host to my ./ssh/config file as follows:
Host XXX.XXX.130.0
LocalForward 3306 localhost:3306
That caused my other connections to fail, so I removed it. But Sequel Pro gave me the following info when I tried to connect with ~/.ssh/config still in place:
Used command: /usr/bin/ssh -v -N -S none -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -i /Users/steves2018air/.ssh/id_rsa -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 ubuntu@XXX.XXX.130.0 -L 49700:127.0.0.1:3306
OpenSSH_7.6p1, LibreSSL 2.6.2
debug1: Reading configuration data /Users/steves2018air/.ssh/config
debug1: /Users/steves2018air/.ssh/config line 1: Applying options for XXX.XXX.130.0
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 48: Applying options for *
debug1: Control socket " none" does not exist
debug1: Connecting to XXX.XXX.130.0 [XXX.XXX.130.0] port 22.
debug1: fd 3 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /Users/steves2018air/.ssh/id_rsa type 0
debug1: key_load_public: No such file or directory
debug1: identity file /Users/steves2018air/.ssh/id_rsa-cert type -1
debug1: Local version string SSH-2.0-OpenSSH_7.6
debug1: Remote protocol version 2.0, remote software version OpenSSH_7.2p2 Ubuntu-4ubuntu2.4
debug1: match: OpenSSH_7.2p2 Ubuntu-4ubuntu2.4 pat OpenSSH* compat 0x04000000
debug1: Authenticating to XXX.XXX.130.0:22 as 'ubuntu'
On a whim, I tried changing host from '127.0.0.1' to 'localhost', just to see what would happen, and if that might shed any light. Slightly different error:
Error in connection_create(host, username, password, dbname, as.integer(port), :
Failed to connect: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Still no solution or explanation — can you help?
It took me forever, but here's how I solved this:
I added this to /etc/mysql/.my.cnf:
[destination]
user=`username`
port=3308
proto=TCP
Then I connect to the cloud server using the following:
ssh ubuntu@xxx.xxx.xxx.x -L 3308:127.0.0.1:3306
I can run that in a Terminal window outside of R, or I can run it in the Terminal window in RStudio — either way, the following works:
con <- dbConnect(RMariaDB::MariaDB(),
user = '`username`',
password = decrypt_kc_pw("`pw_profile_name`"),
groups = "destination",
default.file = "/etc/mysql/.my.cnf",
host = '127.0.0.1',
port = 3308,
dbname='`db_name`')