Search code examples
mysqlrmysql

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

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

Solution

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

    ☼