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.

Research/Info:

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.)

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).

Can you explain what I'm doing wrong?

UPDATE

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'

UPDATE II

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?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Steve
  • 575
  • 4
  • 18

2 Answers2

1

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`')
Steve
  • 575
  • 4
  • 18
1

Steve, thanks for this - it worked perfectly form me - after some minor changes.

Mine was saved in the /etc/mysql/mariadb.cnf file.

con <- dbConnect(RMariaDB::MariaDB(),
user = "yourusername",
password = "yourpassword",
groups = "destination",
default.file = "/etc/mysql/mariadb.cnf",
host = '127.0.0.1',
port = 3308,
dbname="yourdb_name")
Brian
  • 11
  • 1