Prepare your MySQL database

In this milestone, you’ll prepare your MySQL database for monitoring. This involves enabling Performance Schema, creating a dedicated monitoring user, and adjusting digest length settings for complete query capture.

1. To prepare your MySQL database instance for Database Observability, complete the following steps:

2. Verify that Performance Schema is enabled. It is on by default in MySQL 8.0 and later:

   SQL Copy

   SHOW VARIABLES LIKE 'performance_schema';

   Expected result: Value is ON.

3. Create a dedicated monitoring user and replace the password with a strong value:

   SQL Copy

   CREATE USER 'db-o11y'@'%' IDENTIFIED BY '<STRONG_PASSWORD>';

4. Grant the required privileges to the monitoring user:

   SQL Copy

   GRANT PROCESS ON *.* TO 'db-o11y'@'%';
   GRANT REPLICATION CLIENT ON *.* TO 'db-o11y'@'%';
   GRANT SELECT ON performance_schema.* TO 'db-o11y'@'%';

5. Exclude the monitoring user’s own queries from Performance Schema data:

   SQL Copy

   UPDATE performance_schema.setup_actors
   SET ENABLED = 'NO', HISTORY = 'NO'
   WHERE HOST = '%' AND USER = 'db-o11y';

6. Grant object-level privileges for schema details and explain plans:

   SQL Copy

   GRANT SELECT ON *.* TO 'db-o11y'@'%';

   This lets Alloy collect table schemas, indexes, and run EXPLAIN on sampled queries.

7. Increase the digest length to capture complete query text:

   SQL Copy

   SET GLOBAL max_digest_length = 4096;
   SET GLOBAL performance_schema_max_digest_length = 4096;

8. Verify both settings:

   SQL Copy

   SHOW VARIABLES LIKE 'max_digest_length';
   SHOW VARIABLES LIKE 'performance_schema_max_digest_length';

   Expected result: Both values are 4096.

   Note: Add these settings to your MySQL configuration file (my.cnf) to persist across restarts.

9. Optional: Enable Performance Schema consumers for CPU and wait event data:

   SQL Copy

   UPDATE performance_schema.setup_consumers
   SET ENABLED = 'YES'
   WHERE NAME IN ('events_statements_cpu', 'events_waits_current', 'events_waits_history');

   These consumers reset when MySQL restarts. You can grant Alloy permission to auto-enable them instead — refer to the Database Observability MySQL setup documentation for details.

10. You’ve prepared your MySQL database for Database Observability monitoring.

In the next milestone, you’ll configure Grafana Alloy with the Database Observability components.