0

I have installed an instance of a learning management system (Moodle) on AWS. The LMS uses MySQL. I want to gather data from the tables using R and use the following code:

library(DBI)

con <-  dbConnect(RMySQL::MySQL(), 
                  username="public1",
                  password="test",
                  host="127.0.0.1",
                  port=3306,
                  dbname="bitnami_moodle"
                  )

dbReadTable(conn = con, name = "mdl_user")

dbDisconnect(con)

This works properly as long as I am using a tunnel via PuTTY. However later on I want to use Shiny on a separate instance and there I will need to make a direct connection.

I looked through some discussions, but they are not detailed enough for my level of understanding. Can anyone explain to me how to do it?

user3819568
  • 170
  • 1
  • 8

2 Answers2

1

Once you are sure that you are willing to take on the security risks involved, you would do the following.

  1. Open up your Security Group to allow traffic in from port 3306. If you click on the instance in your AWS dashboard, you'll see a security group associated with it. You can click on that group to see what ports are currently open (likely port 22 from anywhere). You'll want to edit that to add port 3306 to your "approved ports" list. You can either do it from just your current IP (if this is the only machine that will need to get in) or from all IPs if you need wider access and are confident that your MySQL usernames/passwords are strong enough to have MySQL open to the public (this is dangerous if the data is sensitive...).
  2. Open your VM's firewall on port 3306 (if one exists -- many AWS images don't have firewalls enabled).
  3. Some recent distributions of MySQL restrict access to not accept connections from external hosts (only locally). If you get an error about not being able to connect from a remote host, you'll need to lookup how to open MySQL for external connections on your distribution.

Another secure approach would be to leave an SSH tunnel from your R machine to your remote MySQL box. Lookup "SSH tunneling" if you want to do this. It would be the most secure, but also cumbersome to manage.

Last option I can think of would be to run something like RStudio Server on the remote machine so you don't need to open up MySQL access.

Jeff Allen
  • 17,277
  • 8
  • 49
  • 70
0

As always when someone shows it to you it's pretty easy. I found the key information here:

https://www.youtube.com/watch?v=O2-9sKSCk4w

Thank you Wilson18!

I can now connect to the remote MySQL server from any location without bothering with SSH or tunnels.

user3819568
  • 170
  • 1
  • 8