Skip to content

MYSQL_SETUP.md

Latest commit

 

History

History
303 lines (228 loc) · 7.48 KB

MYSQL_SETUP.md

File metadata and controls

303 lines (228 loc) · 7.48 KB

MySQL Database Backend

Cortex supports both SQLite (default) and MySQL as database backends. This guide covers setting up and configuring MySQL for Cortex.

Quick Start with Docker Compose

The easiest way to run Cortex with MySQL is using Docker Compose:

# Start Cortex with MySQL backend
docker compose -f docker-compose.yml -f docker-compose.mysql.yml up -d

# Check health
curl http://localhost:8090/api/health

The health endpoint now reports the active database backend:

{
  "status": "ok",
  "version": "0.1.0",
  "database": {
    "backend": "mysql",
    "version": "8.0.36",
    "pool_size": 5,
    "pool_idle": 4,
    "is_healthy": true
  }
}

Configuration

Environment Variables

Configure MySQL via environment variables:

Variable Description Default
CORTEX_DB_DRIVER Database driver (sqlite or mysql) sqlite
CORTEX_DB_HOST MySQL host localhost
CORTEX_DB_PORT MySQL port 3306
CORTEX_DB_USER MySQL username (required)
CORTEX_DB_PASSWORD MySQL password (required)
CORTEX_DB_DATABASE MySQL database name (required)
MYSQL_SSL_MODE SSL mode (disabled, preferred, required, verify_ca, verify_identity) disabled
MYSQL_SSL_CA Path to CA certificate PEM file —
MYSQL_SSL_CERT Path to client certificate PEM file (mutual TLS) —
MYSQL_SSL_KEY Path to client private key PEM file (mutual TLS) —

YAML Configuration

Alternatively, configure in cortex.yaml:

database:
  driver: mysql
  host: db.example.com
  port: 3306
  user: cortex
  password: your_password
  database: cortex_prod
  max_open_conns: 25  # Optional, default: 10

Requirements

  • MySQL 8.0+ (recommended)
  • MariaDB 10.5+ (compatible)

Cortex configures MySQL connections with:

  • UTF-8 encoding (utf8mb4)
  • UTC timezone
  • Foreign key enforcement
  • Strict SQL mode (STRICT_TRANS_TABLES)

Docker Compose Configuration

Files

  • docker-compose.yml - Base configuration (SQLite by default)
  • docker-compose.mysql.yml - MySQL override configuration

MySQL Service

The MySQL service is configured with:

  • Persistent data volume (mysql-data)
  • Health checks for startup readiness
  • UTF-8 character set
  • Strict SQL mode

Environment Variables for Docker

These are used by the MySQL Docker container:

Variable Description Default
MYSQL_ROOT_PASSWORD Root password cortex_root_password
MYSQL_DATABASE Database name cortex
MYSQL_USER Application user cortex
MYSQL_PASSWORD Application password cortex_password
MYSQL_PORT Host port mapping 3306

Development Setup

Running Tests Against MySQL

Use the provided test script:

# Run tests with MySQL (starts container, runs tests, cleans up)
./scripts/test-mysql.sh

# Run tests and keep MySQL container running for debugging
./scripts/test-mysql.sh --keep

Manual MySQL Container

# Start MySQL container
docker run -d \
  --name cortex-mysql \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -e MYSQL_DATABASE=cortex \
  -e MYSQL_USER=cortex \
  -e MYSQL_PASSWORD=cortexpass \
  mysql:8.0

# Run Cortex with MySQL
CORTEX_DB_DRIVER=mysql \
CORTEX_DB_HOST=127.0.0.1 \
CORTEX_DB_USER=cortex \
CORTEX_DB_PASSWORD=cortexpass \
CORTEX_DB_DATABASE=cortex \
cargo run

Connection Pool Settings

Cortex manages a connection pool for optimal performance:

Setting Default Description
max_connections 10 Maximum pool size
min_connections 1 Minimum idle connections
connect_timeout 30s Connection timeout
idle_timeout 10min Idle connection timeout
max_lifetime 30min Maximum connection lifetime

SSL/TLS Configuration

SSL/TLS is supported for all MySQL connections. Configure it via environment variables (preferred for secrets) or in cortex.yaml.

Environment Variables

Variable Description Default
MYSQL_SSL_MODE SSL mode (see below) disabled
MYSQL_SSL_CA Path to CA certificate file (PEM) —
MYSQL_SSL_CERT Path to client certificate file (PEM) —
MYSQL_SSL_KEY Path to client private key file (PEM) —

SSL mode values:

Value Behaviour
disabled No SSL (default, backward-compatible)
preferred Use SSL if the server supports it, otherwise plain TCP
required Require SSL but skip certificate verification
verify_ca Require SSL and verify the server certificate against MYSQL_SSL_CA
verify_identity Require SSL, verify the CA, and check the server hostname

Mode names are case-insensitive; hyphens and underscores are interchangeable (e.g. verify-ca and verify_ca are equivalent).

Example: Require SSL with CA Verification

CORTEX_DB_DRIVER=mysql \
CORTEX_DB_HOST=db.example.com \
CORTEX_DB_USER=cortex \
CORTEX_DB_PASSWORD=secret \
CORTEX_DB_DATABASE=cortex_prod \
MYSQL_SSL_MODE=verify_ca \
MYSQL_SSL_CA=/etc/ssl/mysql/ca.pem \
cargo run

Example: Mutual TLS (mTLS)

MYSQL_SSL_MODE=verify_identity \
MYSQL_SSL_CA=/etc/ssl/mysql/ca.pem \
MYSQL_SSL_CERT=/etc/ssl/mysql/client-cert.pem \
MYSQL_SSL_KEY=/etc/ssl/mysql/client-key.pem \
cargo run

YAML Configuration

database:
  driver: mysql
  host: db.example.com
  user: cortex
  password: ${MYSQL_PASSWORD}
  database: cortex_prod
  ssl_mode: verify_ca          # disabled | preferred | required | verify_ca | verify_identity
  ssl_ca: /etc/ssl/mysql/ca.pem
  # ssl_cert: /etc/ssl/mysql/client-cert.pem   # optional – for mutual TLS
  # ssl_key:  /etc/ssl/mysql/client-key.pem    # optional – for mutual TLS

Note: Environment variables (MYSQL_SSL_*) take precedence over the YAML file when both are set.

Kubernetes / Docker

When running in a container, mount the certificate files as a volume and set the env vars to point at the mounted paths:

env:
  - name: MYSQL_SSL_MODE
    value: "verify_ca"
  - name: MYSQL_SSL_CA
    value: "/etc/ssl/mysql/ca.pem"
volumeMounts:
  - name: mysql-certs
    mountPath: /etc/ssl/mysql
    readOnly: true

Migrations

Cortex automatically runs database migrations on startup for both SQLite and MySQL backends. The same migration scripts work for both backends.

Troubleshooting

Connection Refused

Error: Connection refused (os error 111)

Ensure MySQL is running and accessible:

# Check if MySQL is running
docker ps | grep mysql

# Test connection
mysql -h 127.0.0.1 -u cortex -p cortex

Authentication Failed

Error: Access denied for user 'cortex'@'...'

Verify credentials match your MySQL configuration:

# Check environment variables
echo $CORTEX_DB_USER
echo $CORTEX_DB_PASSWORD

Database Does Not Exist

Error: Unknown database 'cortex'

Create the database:

CREATE DATABASE cortex CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Switching Between Backends

SQLite → MySQL

  1. Export data from SQLite (if needed)
  2. Set up MySQL database
  3. Update configuration to use MySQL
  4. Start Cortex (migrations run automatically)
  5. Re-import data via API

MySQL → SQLite

  1. Export data from MySQL (if needed)
  2. Update configuration to use SQLite
  3. Start Cortex (migrations run automatically)
  4. Re-import data via API

Note: Direct database migration tools are planned for a future release.