MySQL - OpenSRE Documentation

Overview

OpenSRE uses MySQL diagnostics to investigate database-related alerts — checking server health, surfacing slow queries, monitoring replication status, and analyzing table statistics. Queries are wrapped as read-only.

Prerequisites

MySQL 5.7+ (8.0+ recommended for full performance_schema support)
Network access from the OpenSRE environment to your MySQL instance
A read-only user with access to information_schema and performance_schema

Setup

Option 1: Interactive CLI

opensre integrations setup mysql

Option 2: Environment variables

MYSQL_HOST=your-mysql-host
MYSQL_PORT=3306
MYSQL_DATABASE=your-database
MYSQL_USERNAME=opensre_readonly
MYSQL_PASSWORD=your-password
MYSQL_SSL_MODE=preferred   # preferred, required, or disabled

Variable	Default	Description
`MYSQL_HOST`	—	Required. MySQL hostname or IP
`MYSQL_PORT`	`3306`	MySQL port
`MYSQL_DATABASE`	—	Required. Target database
`MYSQL_USERNAME`	`root`	Username
`MYSQL_PASSWORD`	(empty)	Password
`MYSQL_SSL_MODE`	`preferred`	`preferred` (no cert verify), `required`, or `disabled`

Option 3: Persistent store

{
  "version": 1,
  "integrations": [
    {
      "id": "mysql-prod",
      "service": "mysql",
      "status": "active",
      "credentials": {
        "host": "prod-primary.mysql.example.com",
        "port": 3306,
        "database": "application_db",
        "username": "opensre_readonly",
        "password": "your-password",
        "ssl_mode": "preferred"
      }
    }
  ]
}

Credentials

Creating a read-only user

CREATE USER 'opensre_readonly'@'%' IDENTIFIED BY 'secure-password';
GRANT PROCESS ON *.* TO 'opensre_readonly'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'opensre_readonly'@'%';
GRANT SELECT ON performance_schema.* TO 'opensre_readonly'@'%';
GRANT SELECT ON your_database.* TO 'opensre_readonly'@'%';
FLUSH PRIVILEGES;

Three notes on these grants, each confirmed against a real server:

information_schema needs no explicit grant — MySQL exposes it automatically based on a user’s other privileges. GRANT SELECT ON information_schema.* is rejected outright (ERROR 1044) and aborts the script before later grants run.
The grant on the target database (MYSQL_DATABASE) is required — the connector selects it as the default schema on connect, and without it every tool call fails with Access denied ... to database.
REPLICATION CLIENT is required for get_mysql_replication_status (SHOW REPLICA STATUS / SHOW SLAVE STATUS) — without it, that one tool returns an access-denied error even though integrations verify mysql and the other four tools pass.

performance_schema is enabled by default in MySQL 5.7+. Slow query data will not be available if it has been explicitly disabled in my.cnf.

Quick local test with Docker

docker run -d --name mysql-dev -p 127.0.0.1:3307:3306 \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -e MYSQL_DATABASE=app_db \
  mysql:8.0

Wait for it to become healthy, then create the read-only user and a table to inspect:

i=0
until docker logs mysql-dev 2>&1 | grep -q "ready for connections.*port: 3306"; do
  i=$((i + 1))
  [ "$i" -ge 30 ] && { echo "ERROR: MySQL never became ready" >&2; exit 1; }
  sleep 2
done

docker exec mysql-dev mysql -uroot -prootpass -e "
CREATE USER 'opensre_readonly'@'%' IDENTIFIED BY 'secure-password';
GRANT PROCESS ON *.* TO 'opensre_readonly'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'opensre_readonly'@'%';
GRANT SELECT ON performance_schema.* TO 'opensre_readonly'@'%';
GRANT SELECT ON app_db.* TO 'opensre_readonly'@'%';
FLUSH PRIVILEGES;
CREATE TABLE app_db.orders (id INT PRIMARY KEY, amount DECIMAL(10,2));
INSERT INTO app_db.orders VALUES (1, 42.50), (2, 17.00);
"

export MYSQL_HOST=127.0.0.1
export MYSQL_PORT=3307
export MYSQL_DATABASE=app_db
export MYSQL_USERNAME=opensre_readonly
export MYSQL_PASSWORD=secure-password
export MYSQL_SSL_MODE=disabled

Verify:

opensre integrations verify mysql

SERVICE │ SOURCE    │ STATUS   │ DETAIL
mysql   │ local env │ ✓ passed │ Connected to MySQL 8.0.46
        │           │          │ target database: app_db.

opensre investigate only treats an integration as active once the store resolution has something to fall through to env vars with — an untouched ~/.opensre/integrations.json with an unrelated integration in it blocks env-var fallback entirely. Point OPENSRE_INTEGRATIONS_STORE_PATH at an empty, valid store instead, so your real config is never read or written and the exported MYSQL_* vars above are the only source of credentials:

export OPENSRE_INTEGRATIONS_STORE_PATH=$(mktemp /tmp/opensre-mysql-demo.XXXXXX)
echo '{"version": 2, "integrations": []}' > "$OPENSRE_INTEGRATIONS_STORE_PATH"

A literal zero-byte file does not work here — the store loader expects valid JSON and raises on an empty read, so this writes an explicitly empty (but valid) store rather than just creating the file with mktemp alone. Kick off a long-running query so there is something real to investigate, then trigger a real investigation — this is the actual supported entrypoint, not an internal import:

docker exec -d mysql-dev mysql -uroot -prootpass -e "SELECT SLEEP(30);"

opensre investigate --input-json '{
  "alert_name": "MySQLLongRunningQuery",
  "severity": "warning",
  "commonAnnotations": {
    "summary": "app_db MySQL instance has a long-running query blocking other connections"
  }
}'

Real output from a run against this exact local instance (edited for length). An empty store makes opensre investigate fall through to env-var resolution for every integration, not just MySQL — if your shell or machine already resolves another integration from its own env vars, it rides along too (harmless; it just means extra unrelated tool availability, not incorrect MySQL results). Here telegram appears because this environment happens to resolve it:

Loading integrations: telegram, mysql
↳ MySQL · get mysql current processes
↳ MySQL · get mysql server status
↳ MySQL · get mysql slow queries
↳ MySQL · get mysql table stats
↳ MySQL · get mysql replication status

root_cause: "A single SELECT SLEEP(30) was executed by root@localhost on
a freshly started MySQL 8.0.46 instance (uptime 32s), which tripped the
long-running-query alert. All other health signals (connections, locks,
buffer pool, slow queries, replication, table sizes) are clean,
indicating no real workload impact -- this is a synthetic/manual probe,
not a real incident."

All 5 registered tools were exercised in this single turn, and the agent correctly identified the exact synthetic probe query. Teardown — remove the demo store file and unset every demo variable, so nothing lingers in the shell to affect a later command:

rm -f "$OPENSRE_INTEGRATIONS_STORE_PATH"
unset OPENSRE_INTEGRATIONS_STORE_PATH
unset MYSQL_HOST MYSQL_PORT MYSQL_DATABASE MYSQL_USERNAME MYSQL_PASSWORD MYSQL_SSL_MODE
docker rm -f mysql-dev

Investigation tools

Tool	What it does
`get_mysql_server_status`	Version, uptime, connections, query rates, InnoDB buffer pool / deadlock counts
`get_mysql_current_processes`	Active queries longer than a threshold (default 1s)
`get_mysql_slow_queries`	Highest average time from `performance_schema.events_statements_summary_by_digest`
`get_mysql_table_stats`	Row estimates, data/index size from `information_schema.TABLES`
`get_mysql_replication_status`	Replica IO/SQL health, seconds behind source (`SHOW REPLICA STATUS` with `SHOW SLAVE STATUS` fallback)

Verify

opensre integrations verify mysql

Expected output:

Service: mysql
Status: passed
Detail: Connected to MySQL 8.0.32; target database: application_db

Troubleshooting

Symptom	Fix
Connection refused	Check host, port, firewall, `bind-address`
Authentication failed	Confirm user host (`'user'@'%'` or specific IP)
SSL error	Try `MYSQL_SSL_MODE=disabled` or `required`
Access denied on performance_schema	Grant `SELECT ON performance_schema.*`
Access denied to database on verify/tool calls	Grant `SELECT ON <your_database>.*` — the connector selects `MYSQL_DATABASE` as its default schema on connect
Slow query data empty	Confirm `performance_schema=ON` and restart MySQL
Replication shows empty	Expected on primary — other tools still work

Security

Use a dedicated read-only user — avoid root credentials.
Prefer MYSQL_SSL_MODE=required in production.
Restrict the user to specific hosts rather than '%' where possible.
Store credentials in .env or your secret manager — not in source control.

​Overview

​Prerequisites

​Setup

​Option 1: Interactive CLI

​Option 2: Environment variables

​Option 3: Persistent store

​Credentials

​Creating a read-only user

​Quick local test with Docker

​Investigation tools

​Verify

​Troubleshooting

​Security

Overview

Prerequisites

Setup

Option 1: Interactive CLI

Option 2: Environment variables

Option 3: Persistent store

Credentials

Creating a read-only user

Quick local test with Docker

Investigation tools

Verify

Troubleshooting

Security