XLSMART for BUSINESSis Now Bringing TrueWatch to Indonesia

TrueWatch Workshop: Monitor MySQL with TrueWatch: End-to-End Setup (Step-by-Step)

Aug 27, 2025By Admin

Reference: Full MySQL integration details and field definitions are available in the TrueWatch MySQL Integration Docs.

What You'll Achieve

By following this step-by-step guide, you will:

  • Install and configure TrueWatch DataKit to connect to MySQL
  • Create a secure, least-privilege monitoring user
  • Optionally enable and collect MySQL general/slow query logs
  • Build a practical MySQL monitoring dashboard in TrueWatch

Who is this for: backend engineers, SREs, and DBAs who want enterprise-grade MySQL monitoring fast.

Prerequisites

  • A reachable MySQL 5.7+ / 8.0+ instance (cloud RDS works too)
  • Recommended: MySQL 8.0 for full performance_schema coverage
  • Privileges to create a user and grant permissions
  • Ability to deploy TrueWatch DataKit (Docker or host installation)

Architecture at a Glance

MySQL  ← (monitoring user, performance_schema)
  ↑
DataKit (container or host) → ships Metrics / Logs / Slow Queries to TrueWatch

Need eBPF/host-level telemetry? Prefer host installation. For DB-only monitoring, a container is fine.

Step 1: Verify and Enable performance_schema

Many database metrics are sourced from MySQL performance_schema. Check if it’s enabled:

SHOW VARIABLES LIKE 'performance_schema';

If you see Value = OFF, enable it according to your environment:

  • In my.cnf:
[mysqld]
performance_schema=ON

Restart MySQL and verify it is ON.

Managed services (e.g., Aliyun RDS) may restrict some measurements (e.g., mysql_user_status) if performance_schema is disabled or limited. See the official FAQ and remedies in the TrueWatch MySQL Integration Docs.

Step 2: Create a Least-Privilege Monitoring User

For security and observability, create a dedicated read-only user (customize user/password):

CREATE USER 'datakit'@'%' IDENTIFIED WITH caching_sha2_password BY 'datakit_password';
GRANT PROCESS ON *.* TO 'datakit'@'%';
GRANT SELECT ON *.* TO 'datakit'@'%';
GRANT SELECT ON performance_schema.* TO 'datakit'@'%';
GRANT SELECT ON mysql.user TO 'datakit'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'datakit'@'%';
FLUSH PRIVILEGES;

Why these privileges matter:

  • PROCESS: inspect running queries and connections
  • SELECT (incl. performance_schema): read performance and statistics data
  • REPLICATION CLIENT: read replication status (if applicable)

Step 3: Install and Start DataKit (choose one)

Install DataKit as a container or on the host (commands simplified from the official guide):

3A. Docker (quick start)

datakit-install-docker.png

#1) In TrueWatch Console → DataKit → Docker, select your DataWay and copy the generated command/token. #2) On the target host, run:

docker run -d --name datakit \
  --hostname "$(hostname)" \
  -p 9529:9529 \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v $(pwd)/conf.d:/usr/local/datakit/conf.d \
  -e ENV_DATAWAY="https://<your-openway-host>?token=<your_token>" \
  -e ENV_HTTP_LISTEN="0.0.0.0:9529" \
  pubrepo.truewatch.com/truewatch/datakit:1.80.1

#3) Put the mysql.conf from the next step into the mounted conf.d/ directory (prefer conf.d/db/). DataKit will load it automatically.

See the official doc for more environment variables and advanced options (proxy, resource limits, logging, etc.): DataKit Installation Docs.

3B. Host installation (recommended for eBPF/host metrics)

datakit-install-host.png

#1) In TrueWatch Console → DataKit → Linux, select DataWay and copy the tokenized command.

#2) On the target host, run (installs and starts DataKit):

DK_DATAWAY="https://<your-openway-host>?token=<your_token>" bash -c "$(curl -L https://static.truewatch.com/datakit/install.sh)"

#3) Place mysql.conf under DataKit’s conf.d/db/ then restart the service:

sudo systemctl restart datakit

For additional options like DK_INSTALL_ONLY, proxies, and upgrades, consult the official docs: DataKit Installation Docs.

Step 4: Configure DataKit MySQL input (inputs.mysql)

Create or edit mysql.conf in DataKit’s conf.d/db directory:

[[inputs.mysql]]
  host = "db"              # or your MySQL address (FQDN/IP)
  user = "datakit"         # monitoring user
  pass = "datakit_password"
  port = 3306
  interval = "10s"
  innodb = true             # collect key InnoDB metrics
  dbm = true                # enable Database Monitoring (query analysis)
  election = true           # single leader collects when multiple DataKits exist

Field meanings and tuning tips: see the TrueWatch MySQL Integration Docs.

Restart (or hot-reload) DataKit; metrics will begin streaming.

Step 5 (optional): Collect MySQL general/slow query logs

TrueWatch can ingest MySQL general and slow query logs to pinpoint high-latency SQL.

5A. Enable slow query logging (MySQL)

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- optional: flag queries without indexes
-- Recommended tuning (persist via my.cnf):
SET GLOBAL long_query_time = 1;                   -- seconds threshold for slow query

To persist, add to my.cnf (example):

[mysqld]
slow_query_log=ON
long_query_time=1
log_queries_not_using_indexes=ON
log_output=FILE
slow_query_log_file=/var/log/mysql/slow.log

5B. DataKit log collection (typical with host installation)

Append to conf.d/mysql.conf (or create a new section):

[inputs.mysql.log]
  files = ["/var/log/mysql/*.log"]  # adjust to your absolute paths

For detailed log field mapping (normal/slow), see the TrueWatch MySQL Integration Docs.

Step 6: Verify data ingestion

#1) Check DataKit status page (default port 9529) to confirm input health #2) In TrueWatch Explorer, search for measurements like mysql, mysql_replication_log #3) Look for fields such as connections, query_time, lock_time, rows_examined, rows_sent

If certain measurements are missing (e.g., mysql_user_status), ensure performance_schema is enabled or check RDS limitations. See the FAQ in the TrueWatch MySQL Integration Docs.

Step 7: Build a practical MySQL dashboard

A. Use the template dashboard (recommended)

#1) Go to left Scenes → top Dashboards → click Create.

create-dashboard.png

#2) Under System Views, search for mysql and choose Mysql Monitor View (or a cloud‑specific MySQL template).

mysql-template-dashboard.png

#3) The template includes common charts for connections, query latency, InnoDB, and replication. Customize as needed.

Template source and field references: TrueWatch MySQL Integration Docs.

B. Build your own widgets (flexible)

Dashboard preview

mysql-view-1.png

mysql-view-2.png

Example widget configs (QPS / TPS)

  • QPS: metric mysql with Questions, apply non_negative_derivative to get queries per second.

QPS.png

  • TPS: use A+B expression to sum Handler_commit and Handler_rollback, each with non_negative_derivative, to get transactions per second.

TPS.png

Below are battle‑tested widgets for common DB monitoring needs. Add them to a TrueWatch dashboard using Simple/Expression/DQL queries as appropriate.

Field examples reference measurements like mysql and mysql_replication_log. Validate field availability in your Explorer.

A. Overview

  • Time series (Connections)

    • Source: mysql.connections
    • Dimension: server (host:port)
    • Purpose: watch concurrency and spikes

  • Single value/trend (p95 query time)

    • Source: mysql.query_time
    • Aggregation: p95 over 5m
    • Purpose: tail latency at a glance

  • Single value/trend (lock wait time)

    • Source: mysql.lock_time
    • Aggregation: avg over 5m (or p95)
    • Purpose: detect lock contention and blocking

  • Single value (slow queries per minute)

    • Source: slow query logs (inputs.mysql.log)
    • Method: count events where db_slow_statement exists
    • Purpose: catch surges of slow queries

B. InnoDB & Throughput

  • Time series (Rows Examined / Rows Sent)

    • Source: mysql.rows_examined, mysql.rows_sent
    • Aggregation: sum or rate
    • Purpose: understand read volume vs. returned rows, infer efficiency

  • Table (Top hot tables/indexes)

    • Source: mysql.index_name, mysql.object_name, mysql.object_schema
    • Aggregation: group by query_signature or object_name, show avg query_time and count
    • Purpose: locate hot tables/indexes and slow operations

C. Top-N problem queries (logs/events)

  • Table (Top slow queries by avg duration)

    • Source: slow query logs or mysql events
    • Columns: query_signature, sql_text (or normalized message)
    • Metrics: avg(query_time), count()

  • Table (Top wait events)

    • Source: mysql.wait_event, event_name
    • Metric: sum(event_timer_wait)

D. Replication (if applicable)

  • Single value/table (Master / Executed GTID Set summary)
    • Source: mysql_replication_log.Executed_Gtid_Set, Master_Host, Master_Port
    • Purpose: confirm master relationship and GTID progress

Tip: if available, add Seconds_Behind_Master as a single value/time series for replication lag.

Best practices & security

  • Use a dedicated, least-privilege monitoring user separate from application accounts
  • Higher collection frequency (e.g., interval = "10s") is fine in staging; tune for prod cost/perf
  • Adjust long_query_time and log_queries_not_using_indexes to balance noise vs. signal
  • In containers, prefer stdout/log-driver over file mounts to avoid sharing sensitive paths
  • Store credentials in secrets/ENV vars rather than committing them to files

FAQ & troubleshooting

Q: I don’t see the mysql_user_status measurement.

A: Ensure performance_schema is ON:

SHOW VARIABLES LIKE 'performance_schema';

If OFF, enable it per your DB/RDS provider. Some RDS products restrict it. See the TrueWatch MySQL Integration Docs.

Q: DataKit is running but no data arrives.

  • Verify mysql.conf connection details (host/user/pass/port)
  • Ensure security group/firewall allows DataKit to reach MySQL
  • Inspect DataKit status page/logs for connection or permission errors

Q: Slow queries don’t show up.

  • Confirm slow_query_log is enabled
  • If collecting files from containers, ensure absolute paths are readable (stdout/sidecar is often simpler)
  • In Explorer, search the log index for markers (e.g., [MYSQL_QUERY]) or slow log fields

References

  • Official integration documentation and field definitions: TrueWatch MySQL Integration Docs
  • Our integration journey and rationale (monitoring user, DataKit inputs, sidecar): demo-project/INTEGRATION_GUIDE.md

With these steps you’ve integrated MySQL with TrueWatch and built an actionable dashboard. Next, add alerts (Monitors), scheduled reports, and cross-service correlation to achieve end‑to‑end, production‑ready database observability.

Get in touch background

Go beyond observability with TrueWatch today.

Get Demo