Connecting R to a MySQL database in a remote machine

Today I devoted a significant amount of time to solve a problem which seemed simple to me. I wanted to access a MySQL database from R. The database is in a remote machine, web and I wanted to use an ssh tunnel to connect to it. The database is configured so that its local alloweduser user can connect directly to it (no password). To make things more interesting, buy more about I cannot connect directly to the target machine, physiotherapy I have to use an intermediate one to establish the tunnel.

So, again, what I wanted to do was:

  • Open an ssh  tunnel to targethost machine, using intermediatehost (which already has a tunnel to targethost).
  • Use that tunnel to connect to a certain MySQL database in targethost, using the alloweduser account, without using a password.
  • Do all of the above from R.

[Yes, I know many people would object having a connection to a database specifying only a user, with no password. But first, the database only allows connections from localhost, and all local users are trusted. Second, it is a long story… 😉 ]

Establishing the tunnel was easy. After some tries, the following works like a charm:

$ autossh -M 5477 -N -L 3308:localhost:3306 intermediateuser@intermediatehost -p 28942

28942 is the port in intermediatehost where the tunnel to targethost is waiting for me. 3308 is the local port (in my machine) which will be used to connect to this new tunnel. 3306 is the port in targethost to which the tunnel will be connected. That is, the tunnel will be established from localhost:3308 in my machine to localhost:3308 in targethost. .ssh/authorized_users is properly configured with my public key in intermediatehost, so that I don’t need to write a password.

[Collateral hint: MySQL default port is 3306. So, connecting to 3308 in my machine, in fact I will be connecting to MySQL, through the localhost interface, in targetmachine.]

I could test it using MySQL Workbench, where I just specified a new connection, with localhost:3308 as destination port, allowed user as MySQL user, nothing else.

So good, so far.

The problem started when I tried to do the same form mysql in my machine. My idea was that the next line should work:

$ mysql --user=alloweduser --port=3308 --host=localhost

Clear, isn’t it?

No. It doesn’t work:

$ mysql --user=alloweduser --port=3308 --host=localhost
ERROR 1045 (28000): Access denied for user 'allowedusert'@'localhost' (using password: NO)

???? What was happening? These were exactly the same parameters I was using from MySQL Workbench. Time to have a detailed look at manuals, web search engines, and the usual suspects. After many tries, almost by chance, I thought: “what if mysql is not using a TCP connection, but who knows what, to connect?”. So, I specified I wanted a TCP connection (which is something I was doing in MySQL Workbench, because it is there by default). And it worked like a charm:

$ mysql --user=alloweduser --port=3308 --protocol=TCP --host=localhost
Welcome to the MySQL monitor.  Commands end with ; or g.
[...]
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>

Great! now let’s try from R. Unfortunately, when I try “the simple way”, it doesn’t work either:

> library(RMySQL)
> con <- dbConnect(MySQL(), user="alloweduser", port=3308)
Error in mysqlNewConnection(drv, ...) :
RS-DBI driver: (Failed to connect to database: Error: Access denied for user 'alloweduser'@'localhost' (using password: NO)

So, I suspected of the same problem: I needed to specify that I wanted a TCP connection. Back to the manuals and web searches… But no luck:I couldn’t find a way to specify, from R, that I wanted a TCP connection. Time to despair…

Fortunately, I saw a way of specifying the connection data which seemed interesting. With MySQL, you can specify a ~/.my.cnf file, in which you can detail options for your connections to MySQL databases. So, I tried writing one for my connection:

[destination]
user=alloweduser
port=3308
proto=TCP

To use it, I just use group destination from R:

> con <- dbConnect(MySQL(), group="destination")

And it worked!

It took some time, but now I can connect from my RStudio to the remote database. I’m happy (for a while).

Is this behavior of mysql and R (needing to be specified “TCP” to connect using TCP) a bug or a feature? Difficult to say. But given that I’m specifying a port, it seems assuming a TCP connection seems about the right thing to do…

Add a Comment Trackback

Add a Comment

Please leave these two fields as-is:

Protected by Invisible Defender. Showed 403 to 205,265 bad guys.