Using RMySQL Package to Connect MySQL on Ubuntu Server

Last week, I bought a Ubuntu Serve to deply my blog and some shiny applications. This blog demonstrades how to connect MySQL database with R by ‘RMySQL’ package.

Here is the basic connection method, before executing it, you may need to create a database named shiny in MySQL:

-- Access MySQL
mysql -u root -p12345678
-- Create a database named shiny:
create database if not exists shiny;

1 2	library(RMySQL) con = dbConnect(RMySQL::MySQL(), dbname = 'shiny', username = 'root', password = '12345678', host = 'localhost', port = 3306)

When I try to run above codes, I encounter following errors:

Error 1

1 2	Error in .local(drv, ...) : Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

To fix it, you can modify your connection code by specifying where mysqld.sock is placed.

1	con = dbConnect(RMySQL::MySQL(), dbname = 'shiny', username = 'root', password = '12345678', host = 'localhost', port = 3306, unix.sock = '/var/run/mysqld/mysqld.sock')

You can find the location of mysql.sock file by running following code in your terminal:

$ cat /etc/mysql/debian.cnf
#> # Automatically generated for Debian scripts. DO NOT TOUCH!
#> [client]
#> host     = localhost
#> user     = debian-sys-maint
#> password = v09HN7XGGx2CleqV
#> socket   = /var/run/mysqld/mysqld.sock
#> [mysql_upgrade]
#> host     = localhost
#> user     = debian-sys-maint
#> password = v09HN7XGGx2CleqV
#> socket   = /var/run/mysqld/mysqld.sock

Error 2:

1 2	Error in .local(drv, ...) : Failed to connect to database: Error: Access denied for user 'xxx'@'xxxx' (using password: YES)

Although I can log in MySQL at the terminal using root account, the RMySQL connection fails. At this time, you can first log in your MySQL at the terminal using root account, then reset the password (even if the reset password is the same as the original one, it also needs to be operated):

1 2	$ mysql -u root -p12345678; alter user 'root'@'localhost' identified with mysql_native_password by '12345678';

Then I make a successful connection!

Following are some basic usage of RMySQL package:

dbRemoveTable(con, "emaillist")
#> [1] FALSE

# if error happends, excute `set persist local_infile = 1` in MySQL.

dbWriteTable(con, "emaillist", df, append = TRUE, row.names = FALSE)
#> [1] TRUE

dbReadTable(con, "emaillist")
#>   name           email
#> 1    a  [email protected]
#> 2    b [email protected]
#> 3    c  [email protected]

# Close connection
dbDisconnect(con)

Shiny’s official website provides some more sophisticated methods for persistent data storage in Shiny apps. For MySQL is:

library(RMySQL)
options(mysql = list(
  "host" = "127.0.0.1",
  "port" = 3306,
  "user" = "myuser",
  "password" = "mypassword"
))
databaseName <- "myshinydatabase"
table <- "responses"

saveData <- function(data) {
  # Connect to the database
  db <- dbConnect(MySQL(), dbname = databaseName, host = options()$mysql$host, 
      port = options()$mysql$port, user = options()$mysql$user, 
      password = options()$mysql$password)
  # Construct the update query by looping over the data fields
  query <- sprintf(
    "INSERT INTO %s (%s) VALUES ('%s')",
    table, 
    paste(names(data), collapse = ", "),
    paste(data, collapse = "', '")
  )
  # Submit the update query and disconnect
  dbGetQuery(db, query)
  dbDisconnect(db)
}

loadData <- function() {
  # Connect to the database
  db <- dbConnect(MySQL(), dbname = databaseName, host = options()$mysql$host, 
      port = options()$mysql$port, user = options()$mysql$user, 
      password = options()$mysql$password)
  # Construct the fetching query
  query <- sprintf("SELECT * FROM %s", table)
  # Submit the fetch query and disconnect
  data <- dbGetQuery(db, query)
  dbDisconnect(db)
  data
}