connecting RMySQL to and external server

library(RMySQL)

drv <- dbDriver("MySQL")
con <- dbConnect(drv, user="ripley", host="auk", dbname="ripley")

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

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

Indeed, I noticed the "host" argument but the server demands an username and a
password for the machine first,

and only after that for the MySQL server.
Those were the arguments I was looking for.
I will study the RODBC package then, if it solves the problem.

Thank you very much,
Adrian


On Saturday 15 December 2007, Prof Brian Ripley wrote:

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

Indeed, I noticed the "host" argument but the server demands an username and a
password for the machine first, and only after that for the MySQL server.
Those were the arguments I was looking for.
I will study the RODBC package then, if it solves the problem.

Thank you very much,
Adrian



On Saturday 15 December 2007, Prof Brian Ripley wrote:

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

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

On Sat, 15 Dec 2007, Adrian Dusa wrote:

Indeed, I noticed the "host" argument but the server demands an username
and a password for the machine first,

But you said 'connect to', not 'log in to', so how were we to know that?

and only after that for the MySQL server.
Those were the arguments I was looking for.
I will study the RODBC package then, if it solves the problem.

I am afraid I don't understand your setup. MySQL works by listening on
port 3306: user accounts don't come into that.  With our bastion servers
all such ports are blocked and can only be accessed via tunnels
(implemented by stunnel).  I think you need to discuss this with your
sysadmins: if it works under mysql or for ODBC (isql) it will work with
the corresponding R packages.

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.

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.

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

[...]

Err, I am late to this but

 dbConnect(drv, user="mysqluser", password="mysqlpassword",
dbname="anydatabase", host="xxx.xxx.xxx")

works fine for me (eg on Ubuntu with a remote MySQL on some other
box).  What's the problem you were seeing?

On Wed, Dec 19, 2007 at 04:34:26PM +0200, Adrian Dusa wrote:

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

Err, I am late to this but

 dbConnect(drv, user="mysqluser", password="mysqlpassword", dbname="anydatabase", host="xxx.xxx.xxx")

works fine for me (eg on Ubuntu with a remote MySQL on some other
box).  What's the problem you were seeing?

Dirk

--
Three out of two people have difficulties with fractions.

Hi,

I'm sorry I'm also coming late to this discussion, but  like Dirk, I
fail to understand what's wrong with using dbConnect() the way
the documentation, (see ?MySQL) suggests.

RMySQL was developed in a fully distributed client/server
environment, and it uses the MySQL-provided client API.  The
options that the MySQL client library allows (user, password, host,
port or socket file, compression, etc.) should work from RMySQL
(RMySQL just passes those directly to the MySQL client library).
If you have a specific (e.g., security-related) need that the MySQL client
API does not address, then the use of ssh may be the proper way
to go.  But for simple, vanilla style of client-server communications
the underlying MySQL client should be sufficient.

Hi James,

On Wednesday 19 December 2007, David James wrote:

Hi,

I'm sorry I'm also coming late to this discussion, but  like Dirk, I
fail to understand what's wrong with using dbConnect() the way
the documentation, (see ?MySQL) suggests.

RMySQL was developed in a fully distributed client/server
environment, and it uses the MySQL-provided client API.  The
options that the MySQL client library allows (user, password, host,
port or socket file, compression, etc.) should work from RMySQL
(RMySQL just passes those directly to the MySQL client library).
If you have a specific (e.g., security-related) need that the MySQL client
API does not address, then the use of ssh may be the proper way
to go.  But for simple, vanilla style of client-server communications
the underlying MySQL client should be sufficient.

Normally yes, you are right, but this particular MySQL server does not allow 
remote connections (it does not directly listens to port 3306) but only local 
connections using a socket (this terminology is unfamiliar to me, I may talk 
stupid).
So I have to first create a secure login and only after that connect to the 
remote MySQL server.