-- --------------------------------------------------------------------
--
-- 모든 쿼리가 mysql.general_log에 저장되도록 글로벌 변수 셋팅
--
-- --------------------------------------------------------------------
SET GLOBAL slow_query_log='ON';
SET GLOBAL log_output='TABLE';
SET GLOBAL general_log='ON';
SHOW VARIABLES LIKE '%general_log%';
-- --------------------------------------------------------------------
--
-- 모든 쿼리가 C:\mysql_query_monitor.sql 파일에 저장되도록 셋팅
--
-- --------------------------------------------------------------------
SET GLOBAL general_log_file='C:\\mysql_query_monitor.sql';
SET GLOBAL general_log_file='/var/lib/mysql/mysql_query_monitor.sql';
SET GLOBAL log_output='FILE';
SET GLOBAL slow_query_log='ON';
SET GLOBAL general_log='ON';
SHOW VARIABLES LIKE '%general_log%';
SHOW VARIABLES LIKE '%log_output%';
-- --------------------------------------------------------------------
--
-- 모니터링 중지
--
-- --------------------------------------------------------------------
SET GLOBAL slow_query_log='OFF';
SET GLOBAL general_log='OFF';
TRUNCATE TABLE mysql.general_log;
COMMIT;
-- --------------------------------------------------------------------
--
-- 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;
