MySQL

There are two options for connecting to a MySQL database:

Using the odbc package

The odbc package, in combination with a driver, provides DBI support and an ODBC connection.

Driver options

  • MySQL - The official MySQL website provides a downloadable ODBC driver for MySQL: MySQL Connector

  • MariaDB - MariaDB is an open source relational database built by the original developers of MySQL. MariaDB provides an ODBC connector that can be used as a drop-in replacement for a MySQL ODBC connector: MariaDB Connector

  • Posit Professional Drivers - Workbench, RStudio Desktop Pro, Connect, or Shiny Server Pro users can download and use Posit Professional Drivers at no additional charge. These drivers include an ODBC connector for MySQL databases. Posit delivers standards-based, supported, professional ODBC drivers. Use Posit Professional Drivers when you run R or Shiny with your production systems. See the Posit Professional Drivers for more information.

Connection Settings

There are 5 settings needed to make a connection:

  • Driver - See the Drivers section for setup information
  • Server - A network path to the database server
  • UID - User name used to access MySQL server
  • PWD - The password corresponding to the provided UID
  • Port - Should be set to 3306
con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = "[your driver's name]",
                      Server   = "[your server's path]",
                      UID      = rstudioapi::askForPassword("Database user"),
                      PWD      = rstudioapi::askForPassword("Database password"),
                      Port     = 3306)

MySQL Native Authentication

There are multiple means for authenticating to MySQL. If you receive an error regarding the mysql_native_password authentication plugin such as “The driver does not support native authentication any more. Please check your server version or upgrade to secure authentication mechanism for the current user” then you must take action to re-establish connection.

Support for MySQL native authentication was removed from the Posit Professional Drivers in version 2024.03.0, because as of MySQL 8.0.34, the mysql_native_password authentication plugin is deprecated and subject to removal in a future version of MySQL.

In MySQL 8.0, caching_sha2_password was made the default authentication plugin. Please see the MySQL Upgrade Guide for detailed instructions to set this authentication plugin as the default for earlier versions of MySQL.

You should speak with your MySQL Database Administrator to determine if the default authentication plugin is set correctly to caching_sha2_password, if it is not, you will need to ask them to run the follow command for your user and password:

ALTER USER '<ReplaceWithYourUsername>'@'%' IDENTIFIED WITH caching_sha2_password by '<ReplaceWithYourPassword>' REPLACE '<ReplaceWithYourPassword>';

If the default authentication plugin is already set to caching_sha2_password, then you will need to ask them to reset your password with this command:

ALTER USER '<ReplaceWithYourUsername>'@'%' IDENTIFIED by '<ReplaceWithYourPassword>' REPLACE '<ReplaceWithYourPassword>';

If you are currently using Posit Professional Drivers prior to version 2024.03.0 to connect to an AWS RDS for MySQL instance, you will need to update the password authentication plugin for your Master Username prior to upgrading Posit Professional Drivers. AWS RDS for MySQL does not allow you to change the default authentication plugin, so you must manually modify the password using this command:

ALTER USER 'MasterUsername'@'%' IDENTIFIED WITH caching_sha2_password by 'MasterPassword' REPLACE 'MasterPassword';

Please note: Future resets of the Master Password in the AWS console will revert to using mysql_native_password plugin and will lock your user out of the database via the Posit Professional Drivers. You will need to connect to the AWS RDS for MySQL database via alternate means in order to rerun the statement above.

Using the RMariaDB package

RMariaDB is a database interface and MariaDB driver for R. This version is aimed at full compliance with the DBI specification, as a replacement for the old RMySQL package. For more information, visit RMariaDB’s official site: rmariadb.r-dbi.org

To install from CRAN:

install.packages("RMariaDB")
The development version from github:

To install the development version:

# install.packages("remotes")
remotes::install_github("r-dbi/DBI")
remotes::install_github("r-dbi/RMariaDB")

To connect:

library(DBI)
# Connect to my-db as defined in ~/.my.cnf
con <- dbConnect(RMariaDB::MariaDB(), group = "my-db")
Back to top