failing to connect set the connect R with MySQL server on my laptop from remote VM running in Putty.

[Balsher Singh]

Couldn't join the Tuesday work session but hope to get some guidance here from this forum.

I have a remote Xsede Linux VM that I access via putty and run R sessions on, its my tmux-vim-R( or Python) workstation but only allows 10 GB of storage, if I can connect to database server on my laptop it'd be perfect.

On my laptop I have a MySQL server installed that I have successfully connected to from R studio, and R Studio on another laptop over my home network(with Wireless LAN adapter Wi-Fi: IPv4/IPv6 address).  I have also successfully been able to connect to it from R on Linux subsystem on Windows 10( Ethernet adapter VirtualBox Host-Only Network: IPv4/IPv6 address).

I am unable to connect the remote session (running via Putty) with the MySQL server on my laptop after trying similar approaches to above; using every different IP address given in windows (under 'ipconfig /all' command from windows command prompt).

I don't know where to focus my troubleshooting effort.  I would have benefited from making it out a work session but was not possible.  I'd like to start with one clarification: If I am in an R session on Linux VM via Putty, shouldn't I be able to use one of the IP addresses under 'ipconfig' output in windows command prompt to link with MySQL server on my laptop just as I can from another computer over my home network using 'Wireless LAN adapter Wi-Fi: IPv4/IPv6 address' or from Windows Linux subsystem using 'Ethernet adapter VirtualBox Host-Only Network: IPv4/IPv6 address'.  I mention both IPv4 and IPv6 because there is configuration in MySQL that allows the use of one or another.

Am I missing something regarding what the 'host' parameter should equal?

I have a mysql server install on the Xsede VM aw well and I am able to access databases in it from R session as show below( accessing the 'world' db):

con = dbConnect(dbDriver("MySQL"), user = "blsingh", password = "root", dbname = "world", host = "127.0.0.1")

I tried to set up a tunnel with Putty session as such: in putty, under the tunnel tab, enter '3307' for source port, and 'localhost:3306' as the destination ( also tried 127.0.0.1:3306 or 0.0.0.0:3306 as the destination), then with RMySQL library from R in remote session (in Putty) tried the dbConnect( ) to establish a connection over the tunnel. (Note: I have another tunnel that I successfully use to display graphics in web browser on windows session with 'rmote' package.  Below is an attempt in Putty remote session where I was hoping to use the tunnel to connect to mysql running on port 3306 in windows, connecting to it with tunnel connection as explained above). 

con = dbConnect(dbDriver("MySQL"), user = "root", password = "xxxxx", dbname ="lahman2016", host = "localhost", port =3307)

The idea is that MySQL server is listening on port 3306 on windows OS and I use port 3307 in VM to connect to MySQL installed on windows( just as I use port 4324 as source and localhost:4324 with 'rmote' package to display graphics in windows web browser using '127.0.0.1:4324' url in web browser instead of using X11/Xming).

In putty event log is can see the following logs:

2017-09-12 16:06:35 Local port 3307 forwarding to localhost:3306

2017-09-12 16:06:35 Local port 4324 forwarding to localhost:4324

While I can access the output of plot and help(?) command in windows browser after 'rmote::start_rmote(port=4324)' command in R, I cannot connect to MySQL server on windows.

These are the errors:

> con2 = dbConnect(dbDriver("MySQL"), user = "root", password = "lqsymM7&", dbname = "lahman2016", host = "127.0.0.1", port = 3307)

Error in .local(drv, ...) :

  Failed to connect to database: Error: Can't connect to MySQL server on '127.0.0.1' (107)

, then if I chage host to 'localhost':

> con2 = dbConnect(dbDriver("MySQL"), user = "root", password = "lqsymM7&", dbname = "lahman2016", host = "localhost", port = 3307)

Error in .local(drv, ...) :

  Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (13)

, thereupon I found the in from Bash prompt 

blsingh@js-169-21:~$ mysql_config --socket

/var/run/mysqld/mysqld.sock

, so next I tried 

> con2 = dbConnect(dbDriver("MySQL"), user = "root", password = "lqsymM7&", dbname = "lahman2016", host = "localhost", port = 3307, unix.socket = "/var/run/mysqld/mysqld.sock")

Error in .local(drv, ...) :

  Failed to connect to database: Error: Access denied for user 'root'@'localhost' (using password: YES)

, so next I find the my.ini in windows for MySQL server at 'c:/ProgramData/MySQL/MySQL Server 5.7/my.ini' and add the following line and restart mysql service from Services window and at mysql> flush privileges;

skip-grant-tables

All to no avail.  All the while I can access mysql server from windows linux subsystem with 'Link-Local IPv6 Address'

Any help in resolving my issue will be greatly appreciated. Thanks 

And should I be learning some networking skills and if so where should I start if I wish to troubleshoot such issues.

Thanks in advance,

Sher

  

[Nick Santos]

Hi Sher,

With connecting to the host from a VM, it's dependent on how you've configured the VM's networking settings in VirtualBox - you'll need to set it to Bridge mode to access the host from the guest OS in the VM - then you can often access your host via its hostname (localhost will refer to the VM still), or you can figure out the IP address of the virtualbox adapter, I believe, and use that. See this answer on StackOverflow for a bit more.

-Nick

-Nick

--
Check out our R resources at http://d-rug.github.io/
---
You received this message because you are subscribed to the Google Groups "Davis R Users' Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to davis-rug+unsubscribe@googlegroups.com.
Visit this group at https://groups.google.com/group/davis-rug.
For more options, visit https://groups.google.com/d/optout.

[Alex Mandel]

On 09/12/2017 05:46 PM, Balsher Singh wrote:
> I have a remote Xsede Linux VM that I access via putty and run R sessions
> on, its my tmux-vim-R( or Python) workstation but only allows 10 GB of
> storage, if I can connect to database server on my laptop it'd be perfect.

This is the reverse of what most people are doing so many instructions
you find will be backwards of what you want.

If you local port forward with Putty to localhost, that brings a remote
port to your local machine. You want Remote forwarding equivalent to ssh
-R so you can push your laptop to the remote server.

http://blog.batlin.com/2011/11/ssh-remote-port-forwarding-with-putty.html

Another option is to VPN, so that your local machine and remote machine
think they are on the same network and have IP in the same subnet. You
could do this by installing OpenVPN on the server. However do not do
this unless you know how to secure it.

Thanks,
Alex

[Balsher Singh]

[SOLVED] Thank you Nick and Alex after reading your responses, I first tried to understand whats is purposed, Nick's advice to set networking to Bridge mode I have not been able to get too far with (my VM's 'ifconfig' confuses me, there is a docker, ens3, and lo sections).  But What Alex suggested was a simple enough and worked like a charm, only thing is use 127.0.0.1 not localhost, i.e. 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

Thank you very much.  Best, Sher.

[Alex Mandel]

127.0.0.1 and localhost are synonyms for each other. You actually add
things to your system's hosts file for any such combination. If
localhost isn't working it's probably because it's missing from the
hosts file.

As for your ifconfig.
lo is local loopback - ignore

Do you have a Virtual Machine (VM) or do you have a Docker, they are not
the same thing?
Though on Windows Docker containers historically have run inside a VM.
Which of course leads to an extra layer of confusion.

Thanks,
Alex Mandel, PhD

Center for Spatial Sciences
http://spatial.ucdavis.edu
Geospatial and Farming Systems Research Consortium
http://gfc.ucdavis.edu
University of California, Davis

On 09/13/2017 05:36 PM, Balsher Singh wrote:
> [SOLVED] Thank you Nick and Alex after reading your responses, I first
> tried to understand whats is purposed, Nick's advice to set networking to
> Bridge mode I have not been able to get too far with (my VM's 'ifconfig'
> confuses me, there is a docker, ens3, and lo sections). But What Alex

> suggested was a simple enough and worked like a charm, only thing is *use
> 127.0.0.1 not localhost, *i.e. 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
>
> Thank you very much. Best, Sher.
>
>
> On Tuesday, September 12, 2017 at 5:46:08 PM UTC-7, Balsher Singh wrote:
>>
>> Couldn't join the Tuesday work session but hope to get some guidance here
>> from this forum.
>>
>> I have a remote Xsede Linux VM that I access via putty and run R sessions
>> on, its my tmux-vim-R( or Python) workstation but only allows 10 GB of
>> storage, if I can connect to database server on my laptop it'd be perfect.
>>
>> On my laptop I have a MySQL server installed that I have successfully
>> connected to from R studio, and R Studio on another laptop over my home
>> network(with Wireless LAN adapter Wi-Fi: IPv4/IPv6 address). I have also
>> successfully been able to connect to it from R on Linux subsystem on
>> Windows 10( Ethernet adapter VirtualBox Host-Only Network: IPv4/IPv6
>> address).
>>
>> I am unable to connect the remote session (running via Putty) with the
>> MySQL server on my laptop after trying similar approaches to above; using
>> every different IP address given in windows (under 'ipconfig /all' command
>> from windows command prompt).
>>
>> I don't know where to focus my troubleshooting effort. I would have
>> benefited from making it out a work session but was not possible. I'd like

>> to start with one clarification: *If I am in an R session on Linux VM via

>> Putty, shouldn't I be able to use one of the IP addresses under 'ipconfig'
>> output in windows command prompt to link with MySQL server on my laptop
>> just as I can from another computer over my home network using 'Wireless
>> LAN adapter Wi-Fi: IPv4/IPv6 address' or from Windows Linux subsystem using

>> 'Ethernet adapter VirtualBox Host-Only Network: IPv4/IPv6 address'. *I

>> mention both IPv4 and IPv6 because there is configuration in MySQL that
>> allows the use of one or another.
>>

>> *Am I missing something regarding what the 'host' parameter should equal?*

>>
>> I have a mysql server install on the Xsede VM aw well and I am able to
>> access databases in it from R session as show below( accessing the 'world'
>> db):
>> con = dbConnect(dbDriver("MySQL"), user = "blsingh", password = "root",
>> dbname = "world", host = "127.0.0.1")
>>
>>

>> I tried to set up a *tunnel with Putty* session as such: in putty, under

[Balsher Singh]

I am not gonna exude confidence as this is my early foray in cloud/Linux/VM environment.  I would like to says its a docker, since that is what the first subsection under 'ifconfig' states (as I mentioned earlier 2nd is named ens3).  Additional its one of the many per-configured images from JetStream and its named 'Ubuntu 16.04 Devel and Docker'.  I don't know much about the VM and Docker.  But for sure I was not able to use 'localhost' as I mentioned in my previous post, and given some time will would like to learn about 'host' files et. al.

Thanks,

Sher