Skip to main content
OpenSRE uses MySQL diagnostics to investigate database-related alerts — checking server health, surfacing slow queries, monitoring replication status, and analyzing table statistics.

​
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
Select MySQL when prompted and provide your host, database, and credentials.

​
Option 2: Environment variables

Add to your .env: 
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
VariableDefaultDescription
MYSQL_HOST—Required. MySQL hostname or IP
MYSQL_PORT3306MySQL port
MYSQL_DATABASE—Required. Target database
MYSQL_USERNAMErootUsername
MYSQL_PASSWORD(empty)Password
MYSQL_SSL_MODEpreferredSSL mode: preferred, required, or disabled

​
Option 3: Persistent store

Integrations are automatically persisted to ~/.tracer/integrations.json: 
{
  "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"
      }
    }
  ]
}

​
Creating a read-only user

CREATE USER 'opensre_readonly'@'%' IDENTIFIED BY 'secure-password';

-- Required for server status and process list
GRANT PROCESS ON *.* TO 'opensre_readonly'@'%';

-- Required for table statistics
GRANT SELECT ON information_schema.* TO 'opensre_readonly'@'%';

-- Required for slow query analysis
GRANT SELECT ON performance_schema.* TO 'opensre_readonly'@'%';

FLUSH PRIVILEGES;
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.

​
Investigation tools

When OpenSRE investigates a MySQL-related alert, five diagnostic tools are available:

​
Server status

Retrieves version, uptime, connection counts (current, running, max used), query rates, and InnoDB buffer pool hit ratio and deadlock counts. Useful for spotting connection saturation, high deadlock rates, or poor buffer pool efficiency.

​
Current processes

Lists active queries running longer than a configurable threshold (default 1 s), excluding sleeping connections. Surfaces long-running queries and lock contention that may be blocking other operations.

​
Replication status

Reports replica IO and SQL thread health, seconds behind source, and last replication error. Uses SHOW REPLICA STATUS (MySQL 8.0.22+) with automatic fallback to SHOW SLAVE STATUS for older versions. Returns a note if the server is not configured as a replica.

​
Slow queries

Reads performance_schema.events_statements_summary_by_digest to surface queries with the highest average execution time. Results include call count, average/total/min/max execution time (in ms), rows examined, rows sent, and full-table scan indicators.
Slow query data requires performance_schema to be enabled (performance_schema=ON in my.cnf). This is the default in MySQL 5.7+.

​
Table statistics

Returns row count estimates, data size, and index size for all base tables in the target database, sourced from information_schema.TABLES. Results are ordered by total size descending.

​
Verify

opensre integrations verify --service mysql
Expected output: 
Service: mysql
Status: passed
Detail: Connected to MySQL 8.0.32; target database: application_db

​
Troubleshooting

SymptomFix
Connection refusedVerify host, port, and firewall rules. Confirm MySQL is running and accepting remote connections (bind-address in my.cnf).
Authentication failedCheck username and password. Ensure the user exists for the connecting host ('user'@'%' or specific IP).
SSL errorSet MYSQL_SSL_MODE=disabled to test without SSL, or required to enforce it.
Access denied on performance_schemaGrant SELECT ON performance_schema.* to the OpenSRE user.
Slow query data emptyConfirm performance_schema=ON in my.cnf and restart MySQL.
Replication shows emptyExpected for primary servers — replication tools return a note, other tools still work.

​
Security best practices

  • Use a dedicated read-only user — avoid root credentials for monitoring.
  • Enable SSL (MYSQL_SSL_MODE=required) in production environments.
  • Restrict the user to specific hosts rather than '%' where possible.
  • Store credentials in .env, never in source code.
  • Rotate credentials periodically.