tensaidba 님의 블로그

• 목적 : DB 모니터링 디테일 하게 ( 현재는 Grafana만)
• 순서 :
  1. VM 발급
  2. Agent 설정
     • KEY 필요
  3. 타겟 DB 설정

-- datadog 계정 생성
CREATE USER datadog@'%' IDENTIFIED BY 'password';
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@'%';
 
-- db생성
CREATE SCHEMA IF NOT EXISTS datadog;
GRANT EXECUTE ON datadog.* TO datadog@'%';
GRANT CREATE TEMPORARY TABLES ON datadog.* 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@'%';
 
 
-- 쿼리 실행계획 수집 프로시저 생성
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 ;
 
-- 쿼리 실행계획 수집 프로시저 생성 (추적 DB)
DELIMITER $$
CREATE PROCEDURE xxx.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 books_cp.explain_statement TO datadog@'%';
 
FLUSH PRIVILEGES;

4. 연동 설정

init_config:
 
instances:
  - dbm: true
    host: IP
    port: 3306
    username: datadog
    password: 'passsword'
    tags :
      - 'service:xx'
      - 'evn:live'
      - 'team:DB'
      - 'type:ec2-mysql'
- dbm: true
    host: IP
    port: 3306
    username: datadog
    password: 'passsword'
    tags :
      - 'service:yyy'
      - 'evn:dev'
      - 'team:DB'
      - 'type:ec2-mysql'

5. Agent 재시작 & 오류 확인

-- 설정 후 재시작
systemctl restart datadog-agent
 
 
-- 오류 확인
datadog-agent status