How can access a database in MySQL server on my laptop from a cloud instance (preferably from within R)?

Question

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

Answer 1

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 [email protected]

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

☼

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