10分でDatadogのDatabase Monitoringを試す（ローカルMySQL環境）

$ brew install mysql
$ mysql --version
mysql Ver 8.0.30 for macos11.6 on x86_64 (Homebrew)
$ mysql.server start

$ brew install mysql
$ mysql --version
mysql Ver 8.0.30 for macos11.6 on x86_64 (Homebrew)
$ mysql.server start

mysql> SOURCE /Users/hogehoge/Downloads/world-db/world.sql
mysql> show databases;
+------------------------+
| Database |
+------------------------+
| world |
+------------------------+

mysql> use world
Database changed

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.01 sec)

設定ファイルの場所が分からない場合
~ ❯❯❯ mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 
↑ 左から優先して読み込まれる

以下のファイルを編集。
/usr/local/etc/my.cnf

[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
performance_schema=on
max_digest_length=4096
performance_schema_max_digest_length=4096
performance_schema_max_sql_text_length=4096
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-waits-current=ON
performance-schema-consumer-events-statements-history-long=ON
performance-schema-consumer-events-statements-history=ON

~/D/learn ❯❯❯ mysql.server restart
Shutting down MySQL ... SUCCESS!
Starting MySQL .... SUCCESS!
mysql> SHOW VARIABLES LIKE 'perf%';
| performance_schema | ON | を確認

CREATE USER datadog@'%' IDENTIFIED WITH mysql_native_password by 'testhogehoge';
# 「testhogehoge」はパスワードのなので適宜設定してください
ALTER USER datadog@'%' WITH MAX_USER_CONNECTIONS 5;
GRANT REPLICATION CLIENT ON *.* TO datadog@'%';
GRANT PROCESS ON *.* TO datadog@'%';
GRANT SELECT ON performance_schema.* TO datadog@'%';

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

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

# データベース名は適宜設定してください
DELIMITER $$
CREATE PROCEDURE world.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 world.explain_statement TO datadog@'%';

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

筆者が実行したInstallコマンドを参考までに貼っておきます
DD_AGENT_MAJOR_VERSION=7 DD_API_KEY=APIキーを入れる DD_SITE="us5.datadoghq.com" bash -c "$(curl -L https://s3.amazonaws.com/dd-agent/scripts/install_mac_os.sh)"

サンプルの設定ファイルをコピーしてきて使います。
ll /opt/datadog-agent/etc/conf.d/mysql.d/conf.yaml.example
cp /opt/datadog-agent/etc/conf.d/mysql.d/conf.yaml.example /opt/datadog-agent/etc/conf.d/mysql.d/conf.yaml

init_config:

instances:
  - dbm: true
    host: 127.0.0.1
    port: 3306
    username: datadog
    password: 'testhogehoge' # from the CREATE USER step earlier

mysql -u root

mysql> use world;
mysql> select * from country;
mysql> select * from city;

mysql> SELECT id, name from city WHERE name = "Nablus";
+------+--------+
| id   | name   |
+------+--------+
| 4078 | Nablus |
+------+--------+
1 row in set (0.00 sec)

mysql> SELECT id, name from city WHERE id = "4078";
+------+--------+
| id   | name   |
+------+--------+
| 4078 | Nablus |
+------+--------+
1 row in set (0.00 sec)