How to make shiny app talk to cloud relational database (in MySQL)?

Question

This might sounds quite easy to many of experts, but after spending hours I have not came up with a right solution yet, I might have overlooked something which is easy to configure.

My question is how to make this shiny app to talk to Cloud Relational Database, for instance Google MySQL services, after deploying onto shinyapps.io

I have successfully launched this shiny app locally on my Windows 7 64-bit machine, cause I have specified User DSN as google_sql with correct Driver MySQL ODBC 5.3 ANSI Driver, ip, password, etc, so in code line odbcConnect I can simply provide dsn, username and password to open a connection. However when I deploy it to shinyapps.io, it failed with my expectation. My guess is my DSN google_sql is not recognized by shinyapps.io, so in order to make it working, what should I do? Should I change some code? Or configure on shinyapps.io

PS: It's not about how to install RMySQL, someone is posting ans to similar question here (unless they think RMySQL can do something which RODBC can not do) connecting shiny app to mysql database on server

server.R

 library(shiny)
#   library(RODBC)
    library(RMySQL)
#   ch <- odbcConnect(dsn = "google_sql", uid = "abc", pwd = "def")
    ch <- dbConnect(MySQL(),user='abc',password='def',
                    host = 'cloud_rdb_ip_address', dbname = 'my_db')
    shinyServer(function(input, output) {

      statement <- reactive({
        if(input$attribute == 'All'){
          sprintf("SELECT * FROM test_db WHERE country = '%s' AND item = '%s' AND year = '%s' AND data_source = '%s'",
                  input$country,input$item,input$year,input$data_source)
        }else{
          sprintf("SELECT * FROM test_db WHERE country = '%s' AND item = '%s' AND attribute = '%s' AND year = '%s' AND data_source = '%s'",
                  input$country,input$item,input$attribute,input$year,input$data_source)      
        }
      })

       output$result <- renderTable(dbFetch(dbSendQuery(ch, statement=statement()),n=1000))
    })

ui.R

library(shiny)
shinyUI(fluidPage(

  # Application title
  headerPanel("Sales Database User Interface"),

  fluidRow(
    column(4,
           selectInput('country','Country',c('United States','European Union','China'),selected = NULL),
           selectInput('item','Item',c('Shoes','Hat','Pants','T-Shirt'),selected = NULL),
           selectInput('attribute','Attribute',c('All','Sales','Procurement'),selected = NULL)           
    ),
    column(4,
           selectInput('year','Calendar Year',c('2014/2015','2015/2016'),selected = NULL),
           selectInput('data_source','Data Source',c('Automation','Manual'),selected = NULL)
    )
  ),

  submitButton(text = "Submit", icon = NULL),

  # Sidebar with a slider input for the number of bins

  # Show a plot of the generated distribution
  mainPanel(
    tableOutput("result")
  )

))

I think it worth posting my shiny showLogs() error log for expert to enlighten me pls,

2015-05-04T06:32:16.143534+00:00 shinyapps[40315]: R version: 3.1.2
2015-05-04T06:32:16.392183+00:00 shinyapps[40315]: 
2015-05-04T06:32:16.143596+00:00 shinyapps[40315]: shiny version: 0.11.1
2015-05-04T06:32:16.392185+00:00 shinyapps[40315]: Listening on http://0.0.0.0:51336
2015-05-04T06:32:16.143598+00:00 shinyapps[40315]: rmarkdown version: NA
2015-05-04T06:32:16.143607+00:00 shinyapps[40315]: knitr version: NA
2015-05-04T06:32:16.143608+00:00 shinyapps[40315]: jsonlite version: NA
2015-05-04T06:32:16.143616+00:00 shinyapps[40315]: RJSONIO version: 1.3.0
2015-05-04T06:32:16.143660+00:00 shinyapps[40315]: htmltools version: 0.2.6
2015-05-04T06:32:16.386758+00:00 shinyapps[40315]: Using RJSONIO for JSON processing
2015-05-04T06:32:16.386763+00:00 shinyapps[40315]: Starting R with process ID: '27'
2015-05-04T06:32:19.572072+00:00 shinyapps[40315]: Loading required package: DBI
2015-05-04T06:32:19.831544+00:00 shinyapps[40315]: Error in .local(drv, ...) : 
2015-05-04T06:32:19.831547+00:00 shinyapps[40315]:   Failed to connect to database: Error: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
2015-05-04T06:32:19.831549+00:00 shinyapps[40315]: 

PS: I think I need to white-list shinyapps.io ip address to my Google Could, to enable deployment on shinyapps.io.

Answer 1

The list given in pidig89's answer is the right IP list, but rather than trusting some random list found on a SO answer you can find the most up-to-date list on their support site: https://support.rstudio.com/hc/en-us/articles/217592507-How-do-I-give-my-application-on-shinyapps-io-access-to-my-remote-database-

(They "formally" announced this as the recommended way of IP filtering on their mailing list on a post in July 28, 2016)

Answer 2

I actually managed to come up with the answer to this question ourselves but wanted to share the answer since it might be relevant to others as well.

Here are the IP addresses you need to whitelist.

54.204.29.251

54.204.34.9

54.204.36.75

54.204.37.78