Database Monitoring for MySQL

Atatus Database Monitoring offers comprehensive insights into your MySQL databases, revealing key metrics such as query performance, query samples, execution plans, connection details, system statistics, and InnoDB storage engine telemetry.

To enable Database Monitoring, the Agent gathers telemetry by accessing the database as a read-only user. Follow these steps to configure Database Monitoring for your MySQL database:

Enable Performance Schema and it's configuration
Setup Database Access for the Agent
Install Atatus Infrastructure Agent

Enable Performance Schema and it's configuration

To gather query metrics, samples, and execution plans, you need to activate the MySQL Performance Schema and set up the required Performance Schema Options. This can be done through the command line or by adjusting the configuration files (e.g., mysql.conf):

Parameter	Value	Description
performance_schema (Required)	ON	Activates the Performance Schema.
max_digest_length (Required)	4096	Essential for capturing larger queries: Set this parameter to collect queries longer than 1024 characters. By default, queries exceeding this length will not be recorded.
performance-schema-consumer-events-statements-current (Required)	ON	Essential for tracking the status of active queries
performance-schema-consumer-events-waits-current (Required)	ON	Necessary for gathering wait event data.
performance_schema_max_digest_length	4096	Must correspond to the max_digest_length value.
performance_schema_max_sql_text_length	4096	Must correspond to the max_digest_length value.
performance-schema-consumer-events-statements-history-long	ON	Suggested. Allows monitoring of a greater volume of recent queries across all threads. Enabling this feature enhances the chances of recording execution details for less frequent queries.
performance-schema-consumer-events-statements-history	ON	Optional. Allows tracking of recent query history for each thread, improving the chances of capturing details from less frequent queries.

Setup Database Access for the Agent

Create the atatus user and grant basic permissions:

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

  (OR)

  # Only For MySQL 5.6, follow below
  CREATE USER atatus@'%' IDENTIFIED BY '<UNIQUEPASSWORD>';
  GRANT REPLICATION CLIENT ON *.* TO atatus@'%' WITH MAX_USER_CONNECTIONS 8;
  GRANT PROCESS ON *.* TO atatus@'%';
  GRANT SELECT ON performance_schema.* TO atatus@'%';

Create the following schema:

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

Procedure to Enable Events Statements Consumers:

  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.

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 with the appropriate schema name.

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

To collect database information about MySQL, atatus user needs the below necessary privileges. The following SQL query grants the required permissions:

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

Configure Atatus Infrastructure Agent

To enable Database Monitoring for your databases, You should install the Atatus Infrastructure agent in your host machine.
Copy the mysql example configuration file.

copy
```
cd /etc/atatus-infra-agent/conf.d/mysql.d/
sudo cp mysql.yml.template mysql.yml
```

3. Update mysql Configuration File:

Add the following configuration to /etc/atatus-infra-agent/conf.d/mysql.d/mysql.yml:

  metrics:
   - hosts: ["tcp(127.0.0.1:3306)/"]
     username: atatus
     password: <UNIQUEPASSWORD>
     dbm: true

  logs:
   - type: error
   - type: slowlog

4. Restart the atatus infrastructure agent and MySQL service.

copy

  sudo service mysql restart
  sudo service atatus-infra-agent restart