logo

한국어

MySQL 쿼리 모니터링

관리자 2019.03.21 13:23 조회 수 : 9

-- --------------------------------------------------------------------
--
-- 모든 쿼리가 mysql.general_log에 저장되도록 글로벌 변수 셋팅
--
-- --------------------------------------------------------------------
SET GLOBAL general_log='ON';
SET GLOBAL slow_query_log='ON';
SET GLOBAL log_output='TABLE';

-- --------------------------------------------------------------------
--
-- root 계정에 대해 mysql Database 접근권한 주기
--
-- --------------------------------------------------------------------
GRANT ALL PRIVILEGES ON mysql.* TO 'root'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

-- --------------------------------------------------------------------
--
-- INSERT 쿼리만 보기
--
-- --------------------------------------------------------------------
SELECT EVENT_TIME,
       ARGUMENT
FROM mysql.general_log
WHERE COMMAND_TYPE="Query"
  AND ARGUMENT LIKE "INSERT%"
ORDER BY EVENT_TIME DESC
LIMIT 100;

-- --------------------------------------------------------------------
--
-- UPDATE 쿼리만 보기
--
-- --------------------------------------------------------------------
SELECT EVENT_TIME,
       ARGUMENT
FROM mysql.general_log
WHERE COMMAND_TYPE="Query"
  AND ARGUMENT LIKE "UPDATE%"
ORDER BY EVENT_TIME DESC
LIMIT 100;

-- --------------------------------------------------------------------
--
-- UPDATE+INSERT 쿼리만 보기
--
-- --------------------------------------------------------------------
SELECT EVENT_TIME,
       ARGUMENT
FROM mysql.general_log
WHERE COMMAND_TYPE="Query"
  AND (ARGUMENT LIKE "UPDATE%"
       OR ARGUMENT LIKE "INSERT%")
ORDER BY EVENT_TIME DESC
LIMIT 100;

-- --------------------------------------------------------------------
--
-- 모든 쿼리 보기
--
-- --------------------------------------------------------------------
SELECT EVENT_TIME,
       ARGUMENT
FROM mysql.general_log
WHERE COMMAND_TYPE="Query"
ORDER BY EVENT_TIME DESC
LIMIT 100;

-- --------------------------------------------------------------------
--
-- general_log 비우기
--
-- --------------------------------------------------------------------
SET GLOBAL general_log='OFF';
TRUNCATE TABLE mysql.general_log;
SET GLOBAL general_log='ON';