Grafana Learn
Sign in Sign up
Path

Configure Performance Schema and query capture settings, create a monitoring user, and grant the privileges Database Observability needs.

Estimated time: 3 min

Prepare your MySQL database

In this milestone, you’ll prepare your MySQL database for monitoring. This involves configuring Performance Schema and query capture settings, creating a dedicated monitoring user, and granting the privileges Alloy needs to collect complete query telemetry.

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

  1. Add the Performance Schema and query capture settings to your MySQL configuration file, for example my.cnf:

    ini 
    [mysqld]
performance-schema=ON
max-digest-length=4096
performance-schema-max-digest-length=4096
performance-schema-max-sql-text-length=4096

    These are startup variables. If you change any of them, restart MySQL before continuing.

  2. Verify that the settings are applied:

    SQL 
    SHOW VARIABLES LIKE 'performance_schema';
SHOW VARIABLES LIKE 'max_digest_length';
SHOW VARIABLES LIKE 'performance_schema_max_digest_length';
SHOW VARIABLES LIKE 'performance_schema_max_sql_text_length';

    Expected result: performance_schema is ON and the three length settings are 4096.

  3. Create the db-o11y monitoring user and grant its base privileges. Replace YOUR_DB_O11Y_PASSWORD with a strong password:

    SQL 
    CREATE USER 'db-o11y'@'%' IDENTIFIED BY 'YOUR_DB_O11Y_PASSWORD';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'db-o11y'@'%';
GRANT SELECT ON performance_schema.* TO 'db-o11y'@'%';

    Alloy connects as this user. If you choose a different user name, use it in every later step.

  4. Grant read access to the schemas you want to monitor, so Alloy can collect table and index details and run EXPLAIN on sampled queries:

    SQL 
    GRANT SELECT, SHOW VIEW ON YOUR_SCHEMA_NAME.* TO 'db-o11y'@'%';

    Repeat for each schema, or grant read access to all schemas if you’re unsure which ones you need:

    SQL 
    GRANT SELECT, SHOW VIEW ON *.* TO 'db-o11y'@'%';

  5. Allow Alloy to manage Performance Schema consumers and actor tracking. Database Observability needs the events_statements_cpu, events_waits_current, and events_waits_history consumers enabled, and it needs the monitoring user’s own queries excluded from tracking. Both settings reset when MySQL restarts, so the recommended approach is to let Alloy maintain them:

    SQL 
    GRANT UPDATE ON performance_schema.setup_consumers TO 'db-o11y'@'%';
GRANT INSERT, UPDATE ON performance_schema.setup_actors TO 'db-o11y'@'%';

    You’ll turn on the matching Alloy settings in the next milestone. If you’d rather not let Alloy modify Performance Schema, enable the consumers and disable tracking manually instead and re-apply them after each restart — refer to Set up Database Observability for MySQL.

  6. Verify the monitoring user’s privileges:

    SQL 
    SHOW GRANTS FOR 'db-o11y'@'%';

    Confirm the output includes grants equivalent to:

    • PROCESS, REPLICATION CLIENT ON *.*
    • SELECT ON performance_schema.*
    • SELECT, SHOW VIEW on the schemas you granted
    • UPDATE ON performance_schema.setup_consumers
    • INSERT, UPDATE ON performance_schema.setup_actors

You’ve prepared your MySQL database for Database Observability monitoring. Performance Schema captures full query text, and the db-o11y user can read the data Alloy needs.

In the next milestone, you’ll generate the Grafana Alloy configuration for this database and deploy it.