Remote MySQL database with SSH Tunnel

[Phill Clarke]

I'm struggling to connect to remote MySQL database, via an SSH tunnel and was hoping someone on here may have an idea to assist.

The scenario is that my shinyapps.io app needs to retrieve data from a MySQL database, located on a remote server. This part is simple enough using the RMySQL library, something like this:

library("RMySQL")

con <- dbConnect(MySQL(), host="hostname", user="username", pwd="password", dbname="databasename", port=3306)

sql1 <- paste("SELECT * FROM databasename.tablename", sep="")

results <- dbGetQuery(con, sql1)

dbDisconnect(con)

The problem is that the database does not accept remote connections.

If I was to tackle this problem "manually", I'd ssh into the machine and execute the SQL query on localhost. Obviously that won't work in this case.

I've been trying to setup an auto-closing ssh tunnel with something like this:

system(paste("sshpass -p 'passwordhere' ssh -f -L 22:127.0.0.1:3306 username@hostname sleep 10"))

I'm basically trying to bind the local port 22 to the remote port 3306. If there's no activity, the ssh tunnel closes in 10 seconds.

I know I could use public/private keys rather than a password, and will probably do so for the production code, but I was just testing with an actual password to establish the connection.

I've been using ps aux | grep sshd to check the tunnel is created and then after 10 seconds of non-activity closes. This bit seems to work fine.

However, I'm still somewhat stuck when trying to execute my local MySQL query. The error is:

Error in .local(drv, ...) :
  Failed to connect to database: Error: Can't connect to MySQL server on 'hostname' (111)

Which is the same error I see without the ssh tunnel, so clearly the query is not using the tunnel and this is where I am stuck. I'm guessing the local port binding might be wrong, but not really sure how to fix it.

If someone can provide a solution, obviously I'd like it to work with shinyapps.io, so the local port binding would need to be one that is available on the service.

I guess there are other convoluted options, like running the SQL query on the same machine as the database, saving results as a CSV and then making that CSV available to my shinyapps.io app. This is not ideal, because I'd like to write data back to the database too, and this ust seems like an intermediate step which is not really optimum.

[Chad Barraford]

Hello,
Can you configure your mysql server to have port 3306 open to the following IP addresses? These are the addresses your app may be running from. That would allow you to connect from shinyapps.io servers to your remote MySQL server.
54.204.37.78

54.204.29.251

54.204.36.75

54.204.34.9

If you can’t do that, port forwarding could work. That will allow you to connect to your mysql server as if its running locally (of course it actually wouldn’t be). Something like
ssh -nNTL 3306:localhost:3306 username@hostname
That command won’t exit (unless the connection is broken), so you’ll prob need to run it as a background process (which you can do in R or add an ampersand (&) to the end of the ssh command.

Hope that helps

​

--
You received this message because you are subscribed to the Google Groups "ShinyApps Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shinyapps-use...@googlegroups.com.
To post to this group, send email to shinyap...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/shinyapps-users/1225990c-139c-4ed6-b4c9-368573ea25dc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Phill Clarke]

Apologies it's taken me a while to reply to this, but I have it figured out.

My solution looks like this:

# Load db connector library

library("RMySQL")

# SSH

system(paste("sshpass -p 'remotesyspassword' ssh -f -L 3306:localhost:3306 username@remotsystemhostname sleep 20"))

# Open MySQL connection

con <- dbConnect(MySQL(), host='127.0.0.1', user='mysqlusername', password='mysqlpassword', dbname='databasename')

# MySQL Query

sql1 <- paste("SELECT * FROM databasename.databasetable", sep="")

fbRacecard <- dbGetQuery(con, sql1)

# Close MySQL connection

dbDisconnect(con)

[THIAM HUAT Tan]

I need the same solution as what you encountered...

but when I type what you suggested, it has error as below:

> system(paste("sshpass -p 'remotesyspassword' ssh -f -L 3306:localhost:3306 username@remotsystemhostname sleep 20"))

Warning message:

running command 'sshpass -p 'remotesyspassword' ssh -f -L 3306:localhost:3306 username@remotsystemhostname sleep 20' had status 127 

[Phill Clarke]

Yes, unfortunately, my solution worked locally, but not when deployed to shinyapps.io. On reflection, I am assuming this is because one can't launch an SSH session from a shinyapps.io hosted account. Next step is to try adding the IP addresses as suggested by Chad.

[Tareef Kawaf]

Phill,

I think there is a way to make it work. 

I will send you a private note and maybe we can iterate on it together and if it works we will document it for everyone. 

--
You received this message because you are subscribed to the Google Groups "ShinyApps Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shinyapps-use...@googlegroups.com.
To post to this group, send email to shinyap...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/shinyapps-users/48c40ff5-d77a-4f3f-a4fe-bfee83b7b956%40googlegroups.com.

[mts...@gmail.com]

Can I get in on this? I want to do something similar. I have gotten part way...

I was able to put my shiny app on an amazon EC2 server and set up MySQL on that server. It works great. I can change the data on the data base from the app using RMySQL based on user interaction on the app.

What I would like to do, is set up a RDS instance on amazon and then connect to that from the hosting server through SSH.I was unable to get this to work.

I'm actually pretty much a novice and didn't know anything about amazon cloud services or what Ubuntu was but I was able to set it up after a couple days of internet research.

The tunnel is my ultimate goal. Right now my problem is setting up the access privileges on the RDS, I was reading amazon has different procedures for setting up the user and password on MySQL when it is on an RDS vs. EC2 (???)

Was anyone able to get the SSH tunnel to work?

Best of luck to everyone,

Mike

To unsubscribe from this group and stop receiving emails from it, send an email to shinyapps-users+unsubscribe@googlegroups.com.
To post to this group, send email to shinyapps-users@googlegroups.com.

[Phill Clarke]

Hi Mike,

I did get this to work with shinyapps.io, with help from Tareef and Chad. They were going to write up the process. However, in short:

1. Create a set of SSH keys on your local machine

2. Add the relevant key to your database server. I used ssh-copy-id for this. There are many tutorials online. I use Digital Ocean and this is a good start: https://www.digitalocean.com/community/tutorials/how-to-configure-ssh-key-based-authentication-on-a-linux-server

3. Upload both public and private SSH key into a directory accessible from your Shiny app. I initially had some reservations about uploading the private key, but after thinking about it the alternative is an SSH tunnel initiated through username/password which is just as insecure, maybe more. Effectively your Shiny app server needs the private key because it's really taking the place of your local machine when making the SSH tunnel.

4. Then I have the following code (change credentials as appropriate):

# Load db connector library

library("RMySQL")

# Change permissions on SSH keys

system(paste("chmod 600 /path/to/ssh/keys/id_rsa.pub"))
system(paste("chmod 600 /path/to/ssh/keys/id_rsa"))

# Open SSH connection

system(paste("ssh -v -f -N -o StrictHostKeyChecking=no -i /path/to/ssh/keys/id_rsa -L 3306:localhost:3306 user@hostname sleep 20"))

# Database query

con <- dbConnect(MySQL(), host='127.0.0.1', user='youDBUser', password='yourDBPassword', dbname='yourDBName')

sql1 <- paste("SELECT * FROM database.tablename", sep="")

queryResults <- dbGetQuery(con, sql1)

dbDisconnect(con)

Key things to remember here are the path to the SSH key, make sure the directory where the SSH keys are located is writable and your user has rights to change the permissions, make sure port 3306 is open on both machines.

I think that's it. It took me a few attempts, with some rookie errors, but with Tareef and Chad's help we found success.

Good luck.

Cheers

Phill

[Mike Seman]

Thanks Phil!

I need to digest this some. I have shinyapps.i.o, but was also experimenting with Amazon.

So, I think I may have Amazon issues. I set the database up there and a separate server. Right now I can do everything I want on just the server. But everyone is talking about these SHH tunnels... My understanding is the classic architecture is to get the web app to talk to the database, user access through the web app. 

Is using MySQL on the same server that the app is running the same thing, or do I need to set up a separate database (as kinda a best practice?) 

I was able to connect to MySQL on Amazon server, but key was to create a user and grant privileges. I used the key to set that up, but from my pc's command line(Putty). Once it was set up, in my app I can just use RMySQL without specifying any keys. I could never do the same on Amazon RDS instance - creating a user with the same privileges there. I think if I could I could just use that address, user, and password in my RMySQL call.

The link is extremely helpful and I was unaware of the system() function in R. Thanks so much for the reply, gives me options and paths to follow.

Cheers

Mike

 

Sent from my iPad

[Scott Shepard]

Hi Chad

I am trying to setup a mysql connection to my shinyapp using Amazon's RDS servers. Everything works locally as I have whitelisted my office IP address to connect to the database, however it does not work when deployed. I tried whitelisting the IP addresses you listed in hopes that my app would connect once live, but alas no luck. 

I guess my question is, where did you find the IP addresses for the shinyapps.io servers? I don't know if they have changed or expanded since you posted back in February. How would I go about finding that information?

Thank you for your help

Scott Shepard

[Chad Barraford]

Hi Scott,

Theres a slight better way of doing this. In your security group for your RDS instance, allow traffic from the following. This should give our servers access to your RDS server, even as we provision/retire nodes.

AWS Account ID: 954555569365

EC2 Security Group Name: production-agents

To view this discussion on the web visit https://groups.google.com/d/msgid/shinyapps-users/65f5d52a-f50b-4dc4-8467-c7bf3eafa30d%40googlegroups.com.

[Scott Shepard]

Thank you that worked wonderfully.

[Paolo Cavatore]

A Step-by-Step guide would be appreciated if possible...hopefully using Amazon RDS

[Scott Shepard]

Of course. 

I was not responsible for setting up the mysql server I connected to, but amazon has a great resource here 

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.MySQL.html

To ensure that my shiny app could always connect to our servers, I added RStudio's EC2 Security Group to the database I was connecting to.

1. Log into your Amazon AWS console 

2. Click on the RDS Dashboard under the Database section

3. Navigate to the Security Groups tab

4. Select the database you want to allow access to

5. At the very bottom there is a dropdown for Connection Type, select EC2 Security Group

6. Select Another Account

7. Enter the account ID and Security Group name for shinyapps.io: 

      AWS Account ID: 954555569365

      EC2 Security Group Name: production-agents

8. Authorize! 

And you're done. The shinyapps servers have access to your MySQL database without making system calls or setting up an ssh tunnel. 

[Tim Beveridge]

Thanks Phil. That's a great guide. I'm new to the Amazon services and have set up a free RDS MySQL server. It exists inside a VPC and hence you can only use VPC security groups to control access.

I'm wondering if you have any tips on setting the security in that environment so shinyapps.io can access it :-)

Cheers,

Tim

[ja...@prismfp.com]

Tim,

I may be wrong, but I have tried to do this with a VPC, and I have concluded that the only way is to allow access from the individual IP addresses mentioned in Chad Barraford's post above from 2 Feb.

If you find out how to get it to work using ShinyApps.io's security group, then I would be pleased to hear about it. However it seems that in any case you would have to be in the same AWS geographic zone as the ShinyApps servers for this to work. IP addresses work anywhere.

Jack

[Tim Beveridge]

Ok. Thanks Jack.

[Tareef Kawaf]

I am not 100% sure if this helps, but I know some people use security groups for this purpose as well.  From our FAQ.

You’ll need to add RStudio’s EC2 Security Group to the database:

1. Log into your Amazon AWS console
2. Click on the RDS Dashboard under the Database section
3. Navigate to the Security Groups tab
4. Select the database you want to allow access to
5. At the very bottom there is a dropdown for Connection Type, select EC2 Security Group
6. Select Another Account
7. Enter the account ID and Security Group name for shinyapps.io:
AWS Account ID: 954555569365
EC2 Security Group Name: production-agents
8. Authorize!

To view this discussion on the web visit https://groups.google.com/d/msgid/shinyapps-users/ea9eabce-c089-4a93-944a-32cf1109c56b%40googlegroups.com.

[Paolo Cavatore]

I would add the great article by Dean Attali as a general reference for persistenet storage for shiny apps

http://shiny.rstudio.com/articles/persistent-data-storage.html

http://deanattali.com/blog/shiny-persistent-data-storage/

[Tareef Kawaf]

Yes, thanks Paolo. That is now the latest and greatest version of what we would recommend for folks who are interested in more persistent storage options.

To view this discussion on the web visit https://groups.google.com/d/msgid/shinyapps-users/2cea9a9f-a93f-4feb-b376-5be996d2fc82%40googlegroups.com.

[lalas]

Hi Mike

Thanks for your helpful answer. One question though: why do you need: 

StrictHostKeyChecking=no

when you create the ssh tunnel? Is this not a security risk?

Cheers 

[Naveen Srikanth]

Dear Phill,

Your below given steps are very impressive. I need your help desperately to finish my task. I would be very thankful to you in my life if this is succeeded .

Problem Statement :

Problem is very simple to you and what you have already done it. The problem is "I am using windows system where R and R Studio is installed I will be creating apps and publishing apps to shinyapps.io. Now, I have a database in my local system . I have developed a app where it retrieves id from MySQL database and displays the results in app.

Now what I wanted to do is I wanted to publish the app in shinyapps.io where

1)My DB should be in local pc 

2)I will be updating the database manually in my local pc

3)After uploading the app in shinyapps.io the updated changes that is being done in my local system should be reflected in shinyapps.io.

I have attached the code ui.R and server.R files where it retrieves the id from MySQL database and displays the results, this is done locally where database is maintained.

The steps that you have explained below ssh tunneling is for linux os . But how can I generate private and public key for windows . Please elaborate the process that you have done to achieve the goal.

I am eagerly looking for your reply. I am completely dependent for your help to finish this . Please do help me.

Thanks and Regards,

Naveen Srikanth D,

9008482114/9791081769.

[Tareef Kawaf]

Hi Naveen,

I just responded to the shinyapps-users list, my instinct is that you don't want to open up your personal laptop/desktop to the outside world.  Would it be possible for you to consider putting your data in something like Amazon's RDS? or other hosted databases?

-Tareef

To unsubscribe from this group and stop receiving emails from it, send an email to shinyapps-use...@googlegroups.com.
To post to this group, send email to shinyap...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/shinyapps-users/40df2c7c-9a03-4f81-8f67-6961ca82f31f%40googlegroups.com.

[Naveen Srikanth]

Dear Tareef,

I have created a blog which tells about the connection of Amazon RDS and R shiny which will in turn expose to shinyapp.io, below is the link : I am very much thankful to you for the valuable suggestion as you have told be created via RDS after many R&D I was able to succeed.

https://rawsanalyticscom.wordpress.com

Thanks and Regards,

Naveen Srikanth D,

9791081769/9008482114.

[mich...@stat-vision.com]

Hello Everyone,

I'm trying to replicate what you have accomplished here.

According to the debug log, I succeed in authenticating the connection, but then this happens:

debug1: Local connections to LOCALHOST:3306 forwarded to remote address localhost:3306
debug1: Local forwarding listening on ::1 port 3306.
bind: Address already in use
debug1: Local forwarding listening on 127.0.0.1 port 3306.
bind: Address already in use
channel_setup_fwd_listener: cannot listen to port: 3306
Could not request local forwarding.
debug1: Requesting no-more-sessions@openssh.com
debug1: forking to background
debug1: Entering interactive session.
Error in value[[3L]](cond) :
  Failed to connect to database: Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Calls: local ... tryCatch -> tryCatchList -> tryCatchOne -> <Anonymous>
Execution halted

So, I'm thinking either the port 3306 is not open or maybe the port is taken.  I tried a few other ports  (33060, 33061,33062,33099,62635) but, obtained the same error (with a different port listed).  But, maybe I'm doing something wrong.  Assuming my sql server port is 3306 and I want to forward it to the shiyapp server port 33060, I'm not really sure which of these is correct:

-L 33060:localhost:3306

-L 3306:localhost:33060

I think its the top one.

Best,

Michael

[Joshua Spiewak]

It looks like the tunnel is being established just fine, but your driver connection parameters are leaving out the port, so it falls back to trying to use the socket file /var/run/mysqld/mysql.sock, which does not exist.

Try adding port=3306 to the dbConnect call.

[mich...@stat-vision.com]

I have been specifying the port in my dbConnect call.  Here's the script generating the error.

library(RMySQL)
pubkey<-paste0(getwd(),"/data/stat-vision.pub")
pvtkey<-paste0(getwd(),"/data/stat-vision.ppk")

# Change permissions on SSH keys

system(paste0("chmod 600 ",pubkey))
system(paste0("chmod 600 ",pvtkey))

#Open Tunnel
system(paste0("ssh -v -f -N -o StrictHostKeyChecking=no -i ",pvtkey," -L 3306:localhost:3306 us...@host.com sleep 20"))

#Connect to the Database.
con<-dbConnect(RMySQL::MySQL(),
               dbname="dbname",
               user="user",
               password="pass",
               host='localhost',
               port=3306)

When I run dbConnect on my windows box, it works as intended. And, I have tried different forwarding ports successfully (using putty to connect, forwarding to a different port, and connecting via dbConnect( ..., port=NewPort).

I am worried about these highlighted lines in the error log:

debug1: Local connections to LOCALHOST:3306 forwarded to remote address localhost:3306
debug1: Local forwarding listening on ::1 port 3306.
bind: Address already in use
debug1: Local forwarding listening on 127.0.0.1 port 3306.
bind: Address already in use
channel_setup_fwd_listener: cannot listen to port: 3306
Could not request local forwarding.
debug1: Requesting no-more-sessions@openssh.com
debug1: forking to background
debug1: Entering interactive session.
Error in value[[3L]](cond) :
  Failed to connect to database: Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Calls: local ... tryCatch -> tryCatchList -> tryCatchOne -> <Anonymous>
Execution halted

Suggestions?

[mich...@stat-vision.com]

Ok, I think I have it working.  I changed:

#Connect to the Database.
con<-dbConnect(RMySQL::MySQL(),
               dbname="dbname",
               user="user",
               password="pass",
               host='localhost',
               port=3306)

#Connect to the Database.
con<-dbConnect(RMySQL::MySQL(),
               dbname="dbname",
               user="user",
               password="pass",

               host=127.0.0.1,
               port=3306)

Looks like it works now!  Thanks for pointing me towards the dbConnect call.

[Phill Clarke]

An old thread of mine, which I thought I might just make a quick update to.

A couple of things have changed for my app since this was originally posted.

My SSH command ended up being:

system("nohup ssh -v -f -o StrictHostKeyChecking=no -i ./path/to/id_rsa -N -L 3306:localhost:3306 user@hostname", wait = FALSE, ignore.stdout = TRUE, ignore.stderr = TRUE, input = character(0))

This full command was created with great assistance from Andy Kipp.

I also don't bother with the MySQL connection anymore. I just found it was too slow for transferring a lot of data into the app.

Instead, I now run a small script on the server, via cron, to extract the data from the MySQL database and save as an RDS file. This is about a 50MB file and almost 300k records. I then transfer this file into the app periodically with something like the following:

if(!isTRUE(file.exists("filename.rds"))){
  system("scp -v -i ./path/to/id_rsa -o StrictHostKeyChecking=no user@hostname:/path/to/filename.rds path/to/filename.rds")
} else {
  if((file.info("files/filename.rds")$mtime) < (Sys.time() - 86400)){
    system("scp -v -i ./path/to/id_rsa -o StrictHostKeyChecking=no user@hostname:/path/to/filename.rds path/to/filename.rds")
    }
  }

I appreciate this solution won't work for everyone, but I did have quite a few headaches with the SQL, but it eventually worked. Unfortunately, performance wasn't great when transferring large amount of data (RDS files are compressed, thus smaller file sizes to transfer).

I hope that maybe helps someone in the future.