Connecting to MySQL from R

Question

I am trying to connect to MySQL from R. I have installed "8.0.11 MySQL Community Server - GPL" on my machine. In R studio, I have installed RMySQL Library.

When I give the command:

con = dbConnect(RMySQL::MySQL(),user="root", password = "password", dbname="test")

I keep getting the error:

Error in .local(drv, ...) : Failed to connect to database: Error: Unknown database 'test'

I am not sure why it keep giving this error. Any suggestions?

Answer 1

Here is a code I use for access to MySQL from R

# 1. Library
library(RMySQL)

# 2. Settings
db_user <- 'your_name'
db_password <- 'your_password'
db_name <- 'database_name'
db_table <- 'your_data_table'
db_host <- '127.0.0.1' # for local access
db_port <- 3306

# 3. Read data from db
mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
                 dbname = db_name, host = db_host, port = db_port)
s <- paste0("select * from ", db_table)
rs <- dbSendQuery(mydb, s)
df <-  fetch(rs, n = -1)
on.exit(dbDisconnect(mydb))

Please, check how it works on your side.

PS. Looks like you miss 'db_table' parameter.

Answer 2

RMariaDB seems to be the way to go these days. Tested with MySQL.

sudo apt-get update
sudo apt-get install libmariadbclient-dev

R code:

# install.packages("RMariaDB")
library(DBI)

# Connect to the MySQL database
con <- dbConnect(RMariaDB::MariaDB(), 
             dbname = "test", 
             host = "localhost", 
             port = 3306,
             user = "root",
             password = "password")

# Get table names
tables <- dbListTables(con)

# Display structure of tables
str(tables)

# Always cleanup by disconnecting the database
dbDisconnect(con)

You might run into authentication issues:

Error: Failed to connect: Plugin caching_sha2_password could not be loaded:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'TopSecret##1234';