MySQL

info

MySQL New uses SmartAgent + Integration Agent to collect MySQL metrics and DBM data for Query performance, execution-plan, active-session, and service-correlation analysis.

1. Support Scope

Supported database versions:

MySQL 5.6 / 5.7 / 8.0 / 8.4 / 9.7

Note: Other database versions are theoretically supported but must be verified by the user.

Supported system architectures:

Linux x86_64 (amd64), requires glibc ≥ 2.17. Verified versions: CentOS 7, CentOS 8, CentOS 8.5, Ubuntu 21.10

Note: Other system versions are theoretically supported but must be verified by the user.

Supported agent versions:

SmartAgent 10.3.0+

2. Deployment

Install the SmartAgent probe and enable integration-agent.

3. MySQL-side Configuration: User and Privileges

3.1 Create the bonree user

bonree: the user created for the plugin probe to access the database; <UNIQUEPASSWORD>: the database access password you set. If you want to restrict which host ranges may connect using the bonree user, you can configure it like this: bonree@'10.0.0.%' or bonree@'localhost'.

CREATE USER 'bonree'@'%' IDENTIFIED BY '<UNIQUEPASSWORD>';

Check that access works:

mysql -u bonree --password='<UNIQUEPASSWORD>' -e "SHOW STATUS" | grep Uptime
# Seeing the Uptime_since_flush_status line means it works

3.2 Basic privileges

-- MySQL 8.0+
GRANT REPLICATION CLIENT ON *.* TO 'bonree'@'%';
ALTER USER 'bonree'@'%' WITH MAX_USER_CONNECTIONS 5;

-- MySQL 5.6 / 5.7
-- GRANT REPLICATION CLIENT ON *.* TO 'bonree'@'%' WITH MAX_USER_CONNECTIONS 5;

GRANT PROCESS ON *.* TO 'bonree'@'%';
GRANT SELECT ON performance_schema.* TO 'bonree'@'%';

-- Required for index metrics (QUERY_INDEX_SIZE):
GRANT SELECT ON mysql.innodb_index_stats TO 'bonree'@'%';

Privilege	Purpose	Check logic that triggers it
`REPLICATION CLIENT`	Allows the account to query replication-related status (primary/replica), reading `SHOW REPLICA STATUS / SHOW BINARY LOGS`	`_collect_replication_metrics`, `_get_binary_log_stats`
`PROCESS`	Allows viewing all running threads/sessions in the database — monitoring slow queries, active connections, blocking transactions, long-running SQL, and viewing other connections' processlist	`MySQLActivity`, `_get_replicas_connected_count`
`SELECT on performance_schema.*`	The core library of performance metrics, storing wait events, statement latency, locks, I/O, and connection performance data; collects statement metrics / samples / activity / metadata	All DBM Jobs
`SELECT on mysql.innodb_index_stats`	The InnoDB index statistics table, recording row counts, page counts, and sampled statistics for each index; used to compute index cardinality and optimize SQL execution plans, e.g. obtaining InnoDB index size	`MySqlIndexMetrics.QUERY_INDEX_SIZE`

3.3 DBM Execution-Plan Collection Configuration: EXPLAIN Stored Procedure

To create the explain_statement stored procedure so that execution plans can be collected, you need to:

Create one global procedure in the shared monitoring schema (bonree).
Create another procedure of the same name in each business schema for which you want to collect execution plans.

Note: Not all queries support execution plans. Only SELECT / INSERT / UPDATE / DELETE / REPLACE statements support EXPLAIN; statements such as BEGIN / COMMIT / SHOW / USE / ALTER cannot produce a valid execution plan.

3.3.1 Create the shared monitoring schema and the global procedure

A single global monitoring schema bonree is shared. When a query has no explicit schema context, the global procedure under this schema is used.

CREATE SCHEMA IF NOT EXISTS bonree;

GRANT EXECUTE ON bonree.* TO 'bonree'@'%';

DELIMITER $$
CREATE PROCEDURE bonree.explain_statement (IN query TEXT)
    SQL SECURITY DEFINER
BEGIN
    SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
    PREPARE stmt FROM @explain;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

SQL SECURITY DEFINER: the definer is typically root@'localhost', and the monitoring user only needs the EXECUTE privilege to obtain the table read permissions that originally belong to the DEFINER (provided the DEFINER itself has read access to the business tables). This avoids granting the bonree user direct SELECT on the business schemas.

Note: If you did not use the default agent-schema name bonree.explain_statement for the global explain procedure, change the mysql.d/conf.yaml configuration to point to the procedure under your custom agent schema. For example, if the schema created above is agent (using agent in place of bonree), then agent.explain_statement should be configured as follows:

instances:
  - host: 127.0.0.1     
    port: 3306         
    username: bonree
    password: '<PASSWORD>' 
    dbm: true 
    query_samples:
      # The global procedure used by FQ_PROCEDURE
      fully_qualified_explain_procedure: agent.explain_statement  # Default: bonree.explain_statement

3.3.2 Create the same-named procedure in each business schema

Replace <YOUR_SCHEMA>with the actual business schema name, and run the SQL below on each schema for which you want to collect plans, to create the explain_statement stored procedure:

DELIMITER $$
CREATE PROCEDURE <YOUR_SCHEMA>.explain_statement(IN query TEXT)
    SQL SECURITY DEFINER
BEGIN
    SET @explain := CONCAT('EXPLAIN FORMAT=json ', query);
    PREPARE stmt FROM @explain;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE <YOUR_SCHEMA>.explain_statement TO 'bonree'@'%';

3.3.3 Special notes

The procedure under the actual business schema is normally tried first; if it fails, the procedure under the global bonree schema is used. If both fail, you may need to also grant bonree SELECT on the business schema (equivalent to a bare EXPLAIN, low security, not recommended):

GRANT SELECT ON <APP_SCHEMA>.* TO 'bonree'@'%';

3.4 Enabling `performance_schema`

The performance schema is enabled for DBM data collection. It can be enabled via the configuration file or at runtime.

3.4.1 Enable via configuration

In the [mysqld] section of my.cnf:

[mysqld]
performance_schema = ON
performance-schema-consumer-events-statements-current = ON
performance-schema-consumer-events-statements-history = ON
performance-schema-consumer-events-statements-history-long = ON
performance-schema-consumer-events-waits-current = ON

If you need to configure the length of the SQL statements to be collected, add the following configuration; otherwise the default is 1024 characters:

max_digest_length = 4096
performance_schema_max_digest_length = 4096
performance_schema_max_sql_text_length = 4096

Note: A database restart is required after changes for them to take effect.

3.4.2 Enable at runtime:

DELIMITER $$
CREATE PROCEDURE bonree.enable_events_statements_consumers()
    SQL SECURITY DEFINER
BEGIN
    UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name LIKE 'events_statements_%';
    UPDATE performance_schema.setup_consumers SET enabled='YES' WHERE name = 'events_waits_current';
END $$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE bonree.enable_events_statements_consumers TO bonree@'%';

Note: Dynamically enabling at runtime only affects consumers. Parameters such as max_digest_length still need to be configured in my.cnf and require a restart to take effect.

Parameter descriptions: For MySQL >= 5.7:

Parameter	Value	Description
`performance_schema`	`ON`	Required. Enables the Performance Schema.
`max_digest_length`	`4096`	Set as needed. Used to collect longer SQL statements. If left at the default (1024), queries longer than 1024 characters cannot be collected.
`performance_schema_max_digest_length`	`4096`	Must match `max_digest_length`.
`performance_schema_max_sql_text_length`	`4096`	Must match `max_digest_length`. Available in MySQL > 5.6.
`performance-schema-consumer-events-statements-current`	`ON`	Required. Enables monitoring of currently executing queries.
`performance-schema-consumer-events-waits-current`	`ON`	Required. Enables wait-event collection.
`performance-schema-consumer-events-statements-history-long`	`ON`	Recommended. Tracks more recent queries across all threads, helping capture execution details of low-frequency queries.
`performance-schema-consumer-events-statements-history`	`ON`	Optional. Tracks recent query history per thread, helping capture execution details of low-frequency queries.

Note: If the procedure created above does not use the default name bonree.enable_events_statements_consumers, change the mysql.d/conf.yaml configuration to your custom procedure name. For example, if agent is used in place of bonree above, then agent.enable_events_statements_consumers should be configured as follows:

instances:
  - host: 127.0.0.1     
    port: 3306         
    username: bonree   
    password: '<PASSWORD>' 
    dbm: true 
    query_samples:
      # If the consumer-enabling procedure was created under agent
      events_statements_enable_procedure: agent.enable_events_statements_consumers  # Default: bonree.enable_events_statements_consumers

3.5 Self-check script

After flushing, view the granted privileges:

FLUSH PRIVILEGES;
SHOW GRANTS FOR 'bonree'@'%';

mysql -u bonree --password='<UNIQUEPASSWORD>' <<'SQL'
SELECT VERSION();
SELECT @@performance_schema;

SELECT NAME, ENABLED
  FROM performance_schema.setup_consumers
 WHERE NAME LIKE 'events_statements_%' OR NAME = 'events_waits_current';

SELECT COUNT(*) AS timed_statement_instruments
  FROM performance_schema.setup_instruments
 WHERE NAME LIKE 'statement/%' AND ENABLED = 'YES' AND TIMED = 'YES';

SHOW GRANTS FOR CURRENT_USER();
SQL

Expected output:

@@performance_schema = 1.
events_statements_current / history / history_long are all YES.
events_waits_current = YES.
The number of timed statement instruments is ≥ 1.
GRANTS include at least PROCESS, REPLICATION CLIENT, and SELECT ON performance_schema.*.

4. Agent-side Configuration: `mysql.d/conf.yaml`

4.1 Minimal configuration

File path:

OS	Path
Linux	`${APM_HOME}/integration/conf/integration.d/mysql.d/conf.yaml`

instances:
  - host: 127.0.0.1         # IP or domain of the database host to connect to; defaults to the local machine: localhost or 127.0.0.1
    port: 3306              # Port to connect to the database; MySQL default is 3306
    username: bonree        # Username the probe uses to access the database; must match the database-side configuration
    password: '<PASSWORD>'  # Password the user uses to access the database, e.g. 'Bonree@123'; must match the database-side configuration
    dbm: false              # true: enable database performance monitoring; false: disable; default: false
    # tags:
    #   - cluster:cluster_name        # Custom cluster-name tag to attach when reporting data
  # Multiple database monitoring instances can be configured, e.g.:
  # - host: 10.1.1.1
  #   port: 3307              # Assuming MySQL is mapped to access port 3307
  #   username: bonree
  #   password: '<PASSWORD>'
  #   dbm: true
  #   #tags:
  #     #- cluster:prod-mariadb-cluster

4.2 Enabling schema collection

instances:
  - host: 127.0.0.1
    port: 3306
    username: bonree
    password: '<PASSWORD>'
    dbm: true                         # Enable DBM collection
    # tags:
    #   - cluster:cluster_name        # Custom cluster-name tag to attach when reporting data

    collect_schemas:
      enabled: true                   # Use with caution on large databases; when enabled it queries information_schema to list databases/tables/columns/indexes. Default false (disabled).

4.3 Applying the configuration

The configuration takes effect hot. If a change does not take effect, restart SmartAgent (run the bash command: systemctl restart bonree-agent).

5. Service and Trace Correlation

When an application service (e.g. a Java service) accesses a monitored database, you can enable different propagation modes on the Bonree ONE platform, turning SQL-comment injection on/off to associate or disassociate services or traces.

Note: Correlation currently supports only Java services monitored by the Java probe; services monitored by other probes will be supported later.

5.1 Configuration steps

Go to the Bonree ONE platform home page -> Deployment Configuration -> Rule Configuration -> Data Collection -> Database -> Create (create an end-to-end correlation rule) -> Select service scope -> Select database type -> Select propagation mode (i.e. correlation mode; default is Off; options are Off, Full, and Service).

5.2 Propagation mode descriptions

Off mode: No comment is injected; database activity cannot be traced back to upstream services or traces.
Service mode: Only service-level information is injected into the SQL comment, providing basic correlation analysis; trace correlation is not supported.
Full mode: Complete service and Trace information is injected; database activity can be traced back to upstream services, traces, and related information.

5.3 Notes

Correlation prerequisite: Not all statements can be correlated. Correlation information can only be collected once the SQL comment has been successfully written into the database performance tables (e.g. long-running or blocking statements).
Performance impact: The SQL comment itself usually adds little overhead to network transmission, parsing, and CPU; however, Full mode may affect the execution-plan cache (e.g. cache misses, hard parses), so enable it with caution.
Prepared Statement injection: Applies to PostgreSQL only. When enabled, Trace information is carried via the connection-level ApplicationName before each PreparedStatement execution. This may cause extra session-attribute updates and network round-trips, increasing execution latency in high-frequency SQL scenarios; evaluate performance before enabling.
Comment position: For special compatibility in complex environments; by default the comment is injected before the SQL.

1. Support Scope​

Supported database versions:​

Supported system architectures:​

Supported agent versions:​

2. Deployment​

3. MySQL-side Configuration: User and Privileges​

3.1 Create the bonree user​

3.2 Basic privileges​

3.3 DBM Execution-Plan Collection Configuration: EXPLAIN Stored Procedure​

3.3.1 Create the shared monitoring schema and the global procedure​

3.3.2 Create the same-named procedure in each business schema​

3.3.3 Special notes​

3.4 Enabling performance_schema​

3.4.1 Enable via configuration​

3.4.2 Enable at runtime:​

3.5 Self-check script​

4. Agent-side Configuration: mysql.d/conf.yaml​

4.1 Minimal configuration​

4.2 Enabling schema collection​

4.3 Applying the configuration​

5. Service and Trace Correlation​

5.1 Configuration steps​

5.2 Propagation mode descriptions​

5.3 Notes​