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?

Error: Unknown database 'test'. – Titou Oct 08 '20 at 12:44 — Titou, Oct 08 '20 at 12:44

score 9 · Answer 1 · answered May 26 '18 at 15:42

9

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.

answered May 26 '18 at 15:42

Andrii

2,843
27
33

1

andrii I'm sorry as may be here is not the right place to ask but I have issue with connect to mysql using require_ secure_ transport=ON [here](https://community.rstudio.com/t/access-mysql-database-from-shinyapps-io-using-require-secure-transport-on/8723/4). If you have any solution may be I can post a another question in SO. Thank you very much in advance. – A. Suliman May 26 '18 at 15:52
I posted what is work for me. If it's useful please you know what to do here – Andrii May 26 '18 at 16:22
1

Sorry may be my comment wasn't clear. Did you or have you ever try to connect to mysql while defining require_secure_transport=ON in etc/mysql/my.cnf. – A. Suliman May 26 '18 at 16:33
What it the format of the host parameter in case of a remote connection to a Database? – nba2020 Mar 15 '22 at 13:18

Harley · Answer 2 · 2021-06-07T05:44:01.647

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';

A. Suliman · Answer 3 · 2018-05-26T16:14:26.280

The obvious reason may be "I hope" beacuse you didn't include the host ip. Also I prefer use pool package. Then your connection call may be

library(DBI)
library(RMySQL)
library(pool)

pool <- dbPool(
                  drv = RMySQL::MySQL(),
                  dbname = "db_name",
                  host = "127.0.0.1",
                  username = 'user_name',
                  password = 'password',
                  port = 3306
             )

 onStop(function() {
           poolClose(pool)
         })

Another thing it's better to define user with appropriate privileges on test DB and use this user in the connection call insteade of root as DB connection security best practice.

Connecting to MySQL from R

3 Answers3

Linked