Database Monitoring for Azure Database for MySQL

Atatus Database Monitoring offers comprehensive monitoring for your Azure Database for MySQL instances, providing valuable insights into query performance, query samples, execution plans, connection details, system statistics, and InnoDB storage engine telemetry.

The Atatus Infra Agent runs on a separate host (typically an Azure VM in the same virtual network) and connects to your Azure Database for MySQL endpoint over TLS.

Configure MySQL Performance Schema Settings
Setup Database Access for the Agent
Configure Atatus Infrastructure Agent

Before You Begin

Component	Supported Versions / Requirements
MySQL	5.7 or 8.0+
Azure Deployments	Single Server, Flexible Server, MySQL on Azure VMs
Atatus Infra Agent	3.4.0 or higher

Query Activity and Wait Event collection are unavailable on Azure Database for MySQL Flexible Server.

Configure MySQL Performance Schema Settings

In the Azure Portal, open your MySQL instance and edit its Server parameters:

Parameter	Value	Purpose
`performance_schema`	`ON`	Required for the Performance Schema functionality.

Azure Database for MySQL enables performance schema consumers (events_statements_*) by default, so no further consumer configuration is needed. Restart the instance after enabling performance_schema.

Setup Database Access for the Agent

Create the atatus user and grant basic permissions:

copy

  CREATE USER atatus@'%' IDENTIFIED BY '<UNIQUEPASSWORD>';
  ALTER USER atatus@'%' WITH MAX_USER_CONNECTIONS 8;
  GRANT REPLICATION CLIENT ON *.* TO atatus@'%';
  GRANT PROCESS ON *.* TO atatus@'%';
  GRANT SELECT ON performance_schema.* TO atatus@'%';

Create the following schema:

copy

  CREATE SCHEMA IF NOT EXISTS atatus;
  GRANT EXECUTE ON atatus.* TO atatus@'%';

Procedure to Enable Events Statements Consumers:

copy

  DELIMITER $$
  CREATE PROCEDURE atatus.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 atatus.enable_events_statements_consumers TO atatus@'%';

Create the EXPLAIN_STATEMENT procedure to enable the agent to collect explain plans:

copy

DELIMITER $$
CREATE PROCEDURE atatus.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 ;

Additionally, create this procedure in every schema from which you want to collect explain plans. Replace YOUR_SCHEMA:

copy

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 atatus@'%';

Grant access for InnoDB index metrics:

copy

GRANT SELECT ON mysql.innodb_index_stats TO atatus@'%';

[Optional] To collect database configuration and schema details:

copy

GRANT SELECT, SHOW DATABASES, SHOW VIEW, INDEX, REFERENCES ON *.* TO 'atatus'@'%';
FLUSH PRIVILEGES;

Configure Atatus Infrastructure Agent

Install the Atatus Infrastructure agent on a host (typically an Azure VM) that can reach your MySQL endpoint.
Copy the MySQL example configuration file:

copy
```
cd /etc/atatus-infra-agent/conf.d/mysql.d/
sudo cp mysql.yml.template mysql.yml
```
Update the MySQL configuration file at /etc/atatus-infra-agent/conf.d/mysql.d/mysql.yml:

copy
```
metrics:
  - hosts: ["tcp(<AZURE_INSTANCE_ENDPOINT>:3306)/"]
    username: atatus
    password: <UNIQUEPASSWORD>
    dbm: true
```
Replace <AZURE_INSTANCE_ENDPOINT> with your Azure MySQL FQDN, e.g. example-primary.mysql.database.azure.com.

Single Server note: Azure Single Server typically requires the username in the form atatus@<servername> in the connection string. Adjust the username: value accordingly if your instance enforces that format.
Restart the Atatus Infra Agent:

copy
```
sudo service atatus-infra-agent restart
```

Monitoring Multiple Instances (Optional)

To monitor a primary and replica:

copy

metrics:
  - hosts: ["tcp(example-primary.mysql.database.azure.com:3306)/"]
    username: atatus
    password: <UNIQUEPASSWORD>
    dbm: true
    tags:
      - 'env:prod'
      - 'role:primary'

  - hosts: ["tcp(example-replica.mysql.database.azure.com:3306)/"]
    username: atatus
    password: <UNIQUEPASSWORD>
    dbm: true
    tags:
      - 'env:prod'
      - 'role:replica'

Collecting Schemas (Optional)

copy

metrics:
    - hosts:
        - "tcp(<AZURE_INSTANCE_ENDPOINT>:3306)/"
      username: <DB_USERNAME>
      password: <DB_PASSWORD>
      dbm: true
      dbm_mysql_options:
        collect_database_info:
          enabled: true
        collect_settings:
          enabled: true

Collecting Custom Metrics (Optional)

copy

metrics:
    - hosts:
        - "tcp(<AZURE_INSTANCE_ENDPOINT>:3306)/"
      username: <DB_USERNAME>
      password: <DB_PASSWORD>
      dbm: true
      db_name: <DB_NAME>
      dbm_mysql_options:
        additional_metrics_options:
          custom_queries:
            enabled: true
            custom_query:
              - query: SELECT age, salary, hours_worked, name FROM hr.employees;
            columns:
              - name: custom.employee_age
                type: gauge
              - name: custom.employee_salary
                type: gauge
              - name: custom.employee_hours
                type: count
              - name: name
                type: tag
            tags:
              - 'table:employees'
        max_custom_queries: 20