Message-ID: <200712191634.26224.dusa.adrian@gmail.com>
Date: 2007-12-19T14:34:26Z
From: Adrian Dusa
Subject: connecting [logging] RMySQL to an external server - SOLVED
In-Reply-To: <971536df0712150854n55d5c15eoaf300527cd8ce844@mail.gmail.com>
On Saturday 15 December 2007, Gabor Grothendieck wrote:
> Use ssh forwarding to forward local port 3307 to remote port 3306
> specifying the remote account and password. Then if you use local port
> 3306 you can access your local version of MySQL and if you
> use port 3307 you can access the remote version. There is some
> info on the MySQL site. First test it out by running the mysql command
> line program accessing the remote data base via port 3307 and once
> that works you know its ok and you can try RMySQL or RODBC packages.
For those interested in using R with a remote MySQL database, here's the most
simple and straightforward solution (thanks to Gabor):
On the console, forward local port 3307 to remote port 3306
ssh -L 3307:xxx.xxx.xxx.xxx:3306 myuser at xxx.xxx.xxx.xxx
(where xxx.xxx.xxx.xxx is the IP of the remote server and myuser is the login
name). After entering the password, keep that console open.
In R:
library(RMySQL)
drv <- dbDriver("MySQL")
dbConnect(drv, user="mysqluser", password="mysqlpassword",
dbname="anydatabase", host="127.0.0.1", port="3307")
(where mysqluser and mysqlpassword are the remote machine's MySQL username and
password).
Another possible solution is suggested by Prof. Ripley, using stunnel to
create a secure tunnel between the local and the remote machines, but I
haven't explored that in detail.
Best wishes,
Adrian
--
Adrian Dusa
Romanian Social Data Archive
1, Schitu Magureanu Bd
050025 Bucharest sector 5
Romania
Tel./Fax: +40 21 3126618 \
+40 21 3120210 / int.101