Configuring Secure Connection Between MySQL and Metric Insights Application

Beginning v7.0.1, Metric Insights services  web, dataprocessor, data-analyzer, console support three MySQL connection modes:

  • Non-encrypted (require_secure_transport=0),
  • Secure connection using MySQL server certificate (require_secure_transport=1),
  • Secure connection using custom MySQL client certificates and keys, requiring MySQL server CA for validation.

This article outlines how to configure each mode to ensure secure MySQL communication.

Table of contents:

Simple Installation

Orchestrated Environments

Simple Installation

Optional Secure Connection

By default, MySQL is configured with require_secure_transport=0, allowing both non-encrypted and encrypted connections. The choice of encryption mode is determined by the application. For this configuration, no additional settings are required in the Metric Insights Installer.

Require Secure Connection

When MySQL is configured with require_secure_transport=1, it enforces secure connections, rejecting any non-secure clients. To configure Metric Insights for this mode, use the command:

./installer.py --db-require-secure-transport --db-hostname remote.database.com ...

For local MySQL, require_secure_transport will be applied automatically. When connecting to a remote database with --db-require-secure-transport, Metric Insights will also be configured to use a secure connection exclusively.

Using MySQL Client Certificate with Private Key

To use custom mysql client certificate with private key to validate DB connection between application and MySQL use the following options:

Parameter Description
--db-require-secure-transport
 Force require secure transport for all components. If local MySQL is used, it will be switched to secure mode. Otherwise the application will be switched to use MySQL secure connection.
--db-server-ca DB_SERVER_CA
 Set database server CA filename in /opt/mi/ssl. For example: server-ca.pem. It is not applied for local mysql. Options --db-client-cert and --db-client-key are required.
--db-client-cert DB_CLIENT_CERT
 Set database client certificate filename in /opt/mi/ssl. For example: client-cert.pem. It is not applied for local mysql. Options --db-server-ca and --db-client-key are required.
--db-client-key DB_CLIENT_KEY
 Set database client certificate filename in /opt/mi/ssl. For example: client-key.pem. It is not applied for local mysql. Options --db-server-ca and --db-client-cert are required.

For example, running the following command:

./installer.py \     
--db-require-secure-transport \     
--db-server-ca mysql/server-ca.pem \     
--db-client-cert mysql/client-cert.pem \     
--db-client-key mysql/client-key.pem \     
...

configures all components to use a MySQL client certificate with a private key for secure connection validation between the application and MySQL.

The specified file paths should be relative to /opt/mi/ssl inside the container. For instance, value mysql/client-cert.pem means, that the certificate is located at /opt/mi/ssl/mysql/client-cert.pem within the container.

Orchestrated Environments

Kubernetes

By default, a non-secure connection is used.

Require Secure Connection

To enable secure connection, add the following environment variables to web master and slave deployment manifests.

containers:
  - name: web
    ...
    env:
      - name: DB_SSL_CA
        value: "None"
      - name: DB_SSL_VERIFY_SERVER_CERT
        value: "false"

NOTE: To enable MySQL server certificate verification, ensure that a valid certificate is provided on the MySQL server. All clients must validate this certificate; otherwise, the database connection will fail and the application will not function. These environment variables can be passed to the application via the web secret.

Using MySQL Client Certificate with Private Key

Add variables to web, dataprocessor, data-analyzer, and console service deployment manifests:

web:

      containers:
      - name: web
      ...
        env:
          - name: DB_SSL_CA
            value: "mysql/server-ca.pem"
          - name: DB_SSL_VERIFY_SERVER_CERT
            value: "false"
          - name: DB_SSL_CERT
            value: "mysql/client-cert.pem"
          - name: DB_SSL_KEY
            value: "mysql/client-key.pem"

dataprocessor:

      containers:
      - name: dataprocessor
      ...
        env:
          - name: MYSQL_CLIENT_SSL_CA
            value: "mysql/server-ca.pem"
          - name: MYSQL_CLIENT_SSL_CERT
            value: "mysql/client-cert.pem"
          - name: MYSQL_CLIENT_SSL_KEY
            value: "mysql/client-key.pem"

data-analyzer:

      containers:
      - name: data-analyzer
      ...
        env:
          - name: MYSQL_CLIENT_SSL_CA
            value: "mysql/server-ca.pem"
          - name: MYSQL_CLIENT_SSL_CERT
            value: "mysql/client-cert.pem"
          - name: MYSQL_CLIENT_SSL_KEY
            value: "mysql/client-key.pem"

console:

      containers:
      - name: console
      ...
        env:
          - name: MYSQL_CLIENT_SSL_CA
            value: "mysql/server-ca.pem"
          - name: MYSQL_CLIENT_SSL_CERT
            value: "mysql/client-cert.pem"
          - name: MYSQL_CLIENT_SSL_KEY
            value: "mysql/client-key.pem"

These environment variables can be passed to the application via the web secret.

AWS ECS

By default, a non-secure connection is used.

Require Secure Connection

To enable secure connection, add the following environment variables to web master and web slave deployment manifests:

$ nano web.json.tpl
{
  ...
  "environment": [
    { "name": "DB_SSL_CA", "value": "None" },
    { "name": "DB_SSL_VERIFY_SERVER_CERT", "value": "false" },
    { "name": "DB_SSL_CERT", "value": "mysql/client-cert.pem" },
    { "name": "DB_SSL_KEY", "value": "mysql/client-key.pem" }
  ]
}

Using MySQL Client Certificate with Private Key

Add variables to web, dataprocessor, data-analyzer, and console service deployment manifests:

web:

$ nano web.json.tpl
{
  ...
  "environment": [
    { "name": "DB_SSL_CA", "value": "mysql/server-ca.pem" },
    { "name": "DB_SSL_VERIFY_SERVER_CERT", "value": "false" },
    { "name": "DB_SSL_CERT", "value": "mysql/client-cert.pem" },
    { "name": "DB_SSL_KEY", "value": "mysql/client-key.pem" }
  ]
}

dataprocessor:

$ nano dataprocessor.json.tpl
{
  ...
  "environment": [
    { "name": "MYSQL_CLIENT_SSL_CA", "value": "mysql/server-ca.pem" },
    { "name": "MYSQL_CLIENT_SSL_CERT", "value": "mysql/client-cert.pem" },
    { "name": "MYSQL_CLIENT_SSL_KEY", "value": "mysql/client-key.pem" }
  ]
}

data-analyzer:

$ nano data-analyzer.json.tpl
{
  ...
  "environment": [
    { "name": "MYSQL_CLIENT_SSL_CA", "value": "mysql/server-ca.pem" },
    { "name": "MYSQL_CLIENT_SSL_CERT", "value": "mysql/client-cert.pem" },
    { "name": "MYSQL_CLIENT_SSL_KEY", "value": "mysql/client-key.pem" }
  ]
}

console:

$ nano console.json.tpl
{
  ...
  "environment": [
    { "name": "MYSQL_CLIENT_SSL_CA", "value": "mysql/server-ca.pem" },
    { "name": "MYSQL_CLIENT_SSL_CERT", "value": "mysql/client-cert.pem" },
    { "name": "MYSQL_CLIENT_SSL_KEY", "value": "mysql/client-key.pem" }
  ]
}

The same configuration must be applied to the CloudFormation template.

Docker Swarm

By default, a non-secure connection is used.

Require Secure Connection

To enable a secure connection, add the following environment variables to web master and web slave deployment manifests:

$ nano docker-compose.yml
...
services:
  web-master:
    ...
    environment:
      - DB_SSL_CA=None
      - DB_SSL_VERIFY_SERVER_CERT=false
    ...
  web-slave:
    ...
    environment:
      - DB_SSL_CA=None
      - DB_SSL_VERIFY_SERVER_CERT=false
...

Using MySQL Client Certificate with Private Key

Add variables to web, dataprocessor, data-analyzer, and console services:

services:
  web-master:
    ...
    environment:
      - DB_SSL_CA=mysql/server-ca.pem
      - DB_SSL_CERT=mysql/client-cert.pem
      - DB_SSL_KEY=mysql/client-key.pem
      - DB_SSL_VERIFY_SERVER_CERT=false
    ...
  web-slave:
    ...
    environment:
      - DB_SSL_CA=mysql/server-ca.pem
      - DB_SSL_CERT=mysql/client-cert.pem
      - DB_SSL_KEY=mysql/client-key.pem
      - DB_SSL_VERIFY_SERVER_CERT=false
    ...
  dataprocessor:
    ...
    environment:
      - MYSQL_CLIENT_SSL_CA=mysql/server-ca.pem
      - MYSQL_CLIENT_SSL_CERT=mysql/client-cert.pem
      - MYSQL_CLIENT_SSL_KEY=mysql/client-key.pem
    ...
  data-analyzer:
    ...
    environment:
      - MYSQL_CLIENT_SSL_CA=mysql/server-ca.pem
      - MYSQL_CLIENT_SSL_CERT=mysql/client-cert.pem
      - MYSQL_CLIENT_SSL_KEY=mysql/client-key.pem
    ...
  console:
    ...
    environment:
      - MYSQL_CLIENT_SSL_CA=mysql/server-ca.pem
      - MYSQL_CLIENT_SSL_CERT=mysql/client-cert.pem
      - MYSQL_CLIENT_SSL_KEY=mysql/client-key.pem
    ...