[R] connecting RMySQL to and external server

[Prof Brian Ripley]

It is trivial with RODBC (I know that is not what you asked, but it is the 
solution we found first).


For RMySQL, note first that the MySQL configuration file is consulted, so 
the default host is specified in the client section, which is like

[client]

port=3306

[mysql]

default-character-set=latin1

Add host=foo under [client] to change the default host.

And ?dbConnect mentions a 'host' argument under '...'.  That seems to work 
for me (provided of course the server allows connections from other 
machines): on my home network from Windows laptop to Linux server

> library(RMySQL)
Loading required package: DBI
> drv <- dbDriver("MySQL")
> con <- dbConnect(drv, user="ripley", host="auk", dbname="ripley")

This mixture of using the *local* configuration file overridden by 
arguments is a bit dangerous: RMySQL seems not really designed for 
client-server operation and there are some things that definitely do not 
work.  (As I recall, that included dbWrite as that imports a file which is 
on the local machine.)

On Fri, 14 Dec 2007, Adrian Dusa wrote:

> Dear list,
>
> I learned how to connect R to a local MySQL server, using:
> drv <- dbDriver("MySQL")
> con <- dbConnect(drv, user="root", password="mypass", dbname="mydb")
>
> Is it possible to connect R in this way to an external server (on a different
> machine, with a different IP)?
>
> I read the documentation on ?dbConnect (and everything I could find on the
> internet), but I failed to find some other relevant arguments. For example,
> one needs to first connect to the external machine and only after that to the
> MySQL server on that machine. Is this possible from within R?
>
> Thank you in advance,
> Adrian
>
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595