SHOW VARIABLES LIKE '%general%';
SET GLOBAL general_log = 'ON';
-- 可選:SET GLOBAL general_log_file = '/var/log/mysql/general.log';
#!/usr/bin/env python3
import re, sys, os
if len(sys.argv) != 2 or not os.path.exists(sys.argv[1]):
print(f"Usage: {sys.argv[0]} "); sys.exit(1)
log_path = sys.argv[1]
query_pattern = re.compile(r'^\s*\d+\s+.*\bQuery\s+(.*)$', re.I)
cleanup_patterns = [
(r'values\s*\(.*?\)', 'values (x)', re.I),
(r"([=<>]=?)\s*('|\").*?\2", r"\1 'x'"),
(r'([=<>]=?)\s*\d+', r'\1 x'),
(r"like\s+('|\").*?\1", "like 'x'", re.I),
(r'in\s+\(.*?\)', 'in (x)', re.I),
(r'limit.*', 'limit', re.I),
(r'\s+', ' '),
]
with open(log_path) as f:
prev_line = ''
for line in f:
line = line.rstrip('\n')
m = query_pattern.match(line)
if m:
if prev_line:
sql = prev_line
for pat, repl, *flags in cleanup_patterns:
sql = re.sub(pat, repl, sql, flags=flags[0] if flags else 0)
print(sql.strip())
prev_line = m.group(1)
else:
prev_line += ' ' + line.strip()
# 最後一行
if prev_line:
sql = prev_line
for pat, repl, *flags in cleanup_patterns:
sql = re.sub(pat, repl, sql, flags=flags[0] if flags else 0)
print(sql.strip())
python3 analysis-general-log.py /var/log/mysql/general.log \
| sort | uniq -c | sort -nr | head -20
1032 SELECT * FROM wp_comments WHERE ( comment_approved = 'x' OR comment_approved = 'x' ) AND comment_post_ID = x ORDER BY comment_date_gmt DESC
653 SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id in (x) ORDER BY meta_id ASC
527 SELECT FOUND_ROWS()
...
前幾名即為最需優化的查詢(加索引、改寫、快取)。
USE mysql;
CREATE TABLE gn2 LIKE general_log;
RENAME TABLE general_log TO oldLogs, gn2 TO general_log;
-- 稍後可 DROP TABLE oldLogs;