-2

I would like couple things clarified.

(I have mysql client installed on my remote VM running on Ubuntu 16.04, goal is to access the database from within R but I have been trying so far with mysql as such: mysql -u root -p -h fe80::883f:XXXX:XXX:XXXXX -P 3306 where for "XXX" I tried every ip address I get in windows command line when the command ipconfig is issued.

To narrow down my troubleshooting I want couple pointers:

  1. Do I need to give the IP address for my remote(cloud) instance in the mysql config file (thus find my.cnf or its equivalent in windows in dir where mysql server is installed and bind the address for the remote/cloud VM I am trying to access the db on my laptop from).

2 SSL, is this something that needs to be enabled?

Additionally, from Bash on Ubuntu on Windows (WSL) I am able to log into mysql server with `mysql -u root -p'xxx' -h ' i.e. the ip address from windows command line ipconfig output called "Link-local IPv6 Address". After going over several posts and using my imagination(which gets me in trouble with troubleshooting), I feel like in some config file in MySQL server dir is have to "bind the address" for the remote VM (which I access via putty from my laptop), am thinking along the right path?

As for the privileges for the root user in mysql server I believe I have given root every privilege (show below): enter image description here

Shér
  • 9
  • 5
  • 2
    You can do it easily from R using JDBC: https://www.rforge.net/RJDBC/ – duffymo Aug 27 '17 at 00:07
  • Thanks for the share let me try this in a minute and hopefully I succeed. – Shér Aug 27 '17 at 02:02
  • @duffymo I am getting to learn about OBDC connectivity and presume, JDBC is similar tool. I hope my question makes sense but basically my first failure was that I configured a DSN connection on my windows machine and was looking for a way to connect to it from remote linux machine. Then it dawned on my me that I -probably- need a obdc dsn connection setup on my remote machine that connects to the database on my windows machine. I am trying to set it up this way right now but would appreciate if you help me clear things further - sincerely – Shér Sep 02 '17 at 10:56

2 Answers2

2

If you're going to use the RJDBC package, you have to stick with JDBC. ODBC might be "one letter off", but it's not the same thing.

You need a few things to be in place:

  1. The database on your Windows machine has to be running, visible from the Linux machine, and have a JDBC driver JAR available that will let you connect to it. If you tell me your database is Microsoft Access, you're done. There are no free JDBC drivers for Access.
  2. You have to have the JDBC driver JAR for your database on the Linux machine, in the CLASSPATH that R will use to find it.
  3. You need a JDK installed on the Linux machine. I recommend that you install nothing older than version 8.

Once this is in place it should be easy. I've done it.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • @duffmo When you say my Db on windows should be visible from Linux machine that means that I should start with Googling "Connect to remote MySQL database using JDBC", correct? – Shér Sep 02 '17 at 16:40
  • It means you should get the MySQL JDBC JAR, put it where R can find it, and then go into R with the URL, username, and password and see if R can connect. You're making this too hard. Read the RJDBC docs and try it. – duffymo Sep 02 '17 at 18:54
0

I solved my issue and can access MySQL sever on my laptop from remote ression(that I access with putty as follows: in putty first used 3307 as source, checked the remote radio buttons, set destination to localhost:3306 and at Bash prompt in VM: mysql -u root -p -h '127.0.0.1' -P 3307

Or from Linux (I use WSL) Bash prompt:

# access the remote VM from Windows Linux subsystem(WSL) ssh -R 3307:localhost:3306 blsingh@149.165.169.21

## access MySQL server on my laptop from Bash prompt on in WSL mysql -u root -p -h'127.0.0.1' -P 3307

  • From here its not difficult to figure out how to access it in R; we have a remote port forward and we use the same parameters in R.

☼

Shér
  • 9
  • 5