-- 查看通用日志是否開啟
SHOW VARIABLES LIKE 'general_log%';

-- 如果開啟，查看日志文件位置
SHOW VARIABLES LIKE 'general_log_file';

-- 臨時開啟通用日志（生產(chǎn)環(huán)境慎用）
SET GLOBAL general_log = 'ON';

# Linux下查看包含特定表的SQL
grep -i '表名' /var/lib/mysql/localhost.log

# 或使用mysql命令行
mysql> SELECT * FROM mysql.general_log 
WHERE argument LIKE '%表名%' 
AND event_time > '2024-01-01';

-- 開啟相關(guān)監(jiān)控
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%statement%';

-- 查看歷史語句（包含完整SQL）
SELECT * FROM performance_schema.events_statements_history
WHERE SQL_TEXT LIKE '%表名%'
ORDER BY EVENT_TIME DESC
LIMIT 100;

-- 查看更詳細的歷史
SELECT * FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT LIKE '%表名%';

-- 查看當前binlog文件
SHOW MASTER STATUS;

-- 查看所有binlog文件
SHOW BINARY LOGS;

-- 使用mysqlbinlog工具解析
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 | grep -A5 -B5 '表名'

-- 如使用MariaDB審計插件
INSTALL PLUGIN server_audit SONAME 'server_audit.so';
SET GLOBAL server_audit_events = 'QUERY';

// Spring Boot示例：使用AOP記錄SQL
@Aspect
@Component
public class SqlLogAspect {
    @Before("execution(* com.example.mapper.*.*(..))")
    public void logSql(JoinPoint joinPoint) {
        // 獲取執(zhí)行的SQL并記錄到日志文件
    }
}

-- 1. 開啟performance_schema
[mysqld]
performance_schema = ON
performance-schema-consumer-events-statements-history-long = ON

-- 2. 創(chuàng)建定期清理任務(wù)
CREATE EVENT cleanup_sql_history
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM performance_schema.events_statements_history_long
WHERE EVENT_TIME < NOW() - INTERVAL 7 DAY;

-- 查看當前正在執(zhí)行的SQL
SHOW PROCESSLIST;

-- 查看更詳細的信息
SELECT * FROM information_schema.PROCESSLIST 
WHERE INFO LIKE '%表名%';

-- 使用performance_schema查看
SELECT * FROM performance_schema.threads 
WHERE PROCESSLIST_INFO LIKE '%表名%';