Connect to MySQL tabular with Domino Data Sources

The easiest way to connect to a MySQL instance from Domino is to use a Domino Data Source.

Configuration

To create a MySQL (tabular) Data Source select MySQL as the Data Source type from the New Data Source wizard and specify the relevant parameters.

Valid values for the host are <host string>:3306 or <host string>.

mysql tab ds

Authentication

Specify the credentials that will be used to connect to MySQL (tabular).

Currently, the only authentication mechanism supported is Username and Password. The credentials will be securely stored in the Domino secret store backed by HashiCorp Vault.

Python and mysql-connector-python

Domino recommends the mysql-connector-python library for interacting with MySQL databases from Python.

Environment setup

Use the following Dockerfile instruction to install psycopg2 in your environment.

This instruction assumes you already have pip installed.

USER root
RUN pip install mysql-connector-python
USER ubuntu

Credential setup

There are several environment variables you must set up to store secure information about your MySQL connection. Set the following as Domino environment variables on your user account:

MYSQL_HOST

Hostname where your MySQL service is running. Make sure your MySQL service and network firewall are configured to accept connections from Domino.
MYSQL_USER

The MySQL user you want to authenticate as.
MYSQL_PASSWORD

The password for the user chosen above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the mysql-connector-python documentation for detailed information about how to use the package. Below is a simple example for connecting to MySQL with mysql-connector-python where:

You have set up environment variables noted above with the host, user, and password
Your user has access to a database named db1 in the target MySQL instance
The db1 database contains a table called employees

from mysql.connector import (connection)
import os

# fetch values from environment variables and set the target database
hostname = os.environ['MYSQL_HOST']
username = os.environ['MYSQL_USER']
password = os.environ['MYSQL_PASSWORD']
dbname = 'db1'

# establish connection to db1 database in your mysql service
cnx = connection.MySQLConnection(user=username,
                                 password=password,
                                 host=hostname,
                                 database=dbname)

# create cursor for passing queries to database
cursor = cnx.cursor()

# define query
query = ("SELECT * FROM employees")

# execute query
cursor.execute(query)

# print results
for row in cursor:
  print(row)

# close connection
cnx.close()

R and RMySQL

Domino recommends the RMySQL library for interacting with MySQL services from R.

Environment setup

Use the Dockerfile instructions below to add RMySQL to your environment.

USER root

RUN sudo apt-get install -y libmariadb-client-lgpl-dev
RUN R -e 'install.packages("RMySQL")'

USER ubuntu

Credential setup

There are several environment variables you must set up to store secure information about your MySQL connection. Set the following as Domino environment variables on your user account:

MYSQL_HOST

Hostname where your MySQL service is running. Make sure your MySQL service and network firewall are configured to accept connections from Domino.
MYSQL_USER

The MySQL user you want to authenticate as.
MYSQL_PASSWORD

The password for the user chosen above.

Read Environment variables for secure credential storage to learn more about Domino environment variables.

Usage

Read the RMySQL documentation for detailed information about how to use the package. Below is a simple example for connecting to MySQL with RMySQL where:

You have set up environment variables noted above with the host, user, and password
Your user has access to a database named db1 in the target MySQL instance
The database contains a table named employees

# load the library
library(RMySQL)

# fetch values from environment variables and set the target database
hostname <- Sys.getenv['MYSQL_HOST']
username <-  Sys.getenv['MYSQL_USER']
password <- Sys.getenv['MYSQL_PASSWORD']
database <- 'db1'

# set up a driver and use it to create a connection to your database
con <- dbConnect(RMySQL::MySQL(), host = hostname,
 user = username, password = password, dbname = database)

# run a query and load the response into a dataframe
df_mysql <- dbGetQuery(con, "SELECT * FROM employees")

# close your connection when finished
dbDisconnect(con)