-- 檢查 Performance Schema 狀態(tài)
SHOW VARIABLES LIKE 'performance_schema';
SELECT * FROM performance_schema.setup_instruments WHERE ENABLED='YES' LIMIT 10;

-- 查看當(dāng)前正在運(yùn)行的查詢
SELECT * FROM performance_schema.threads 
WHERE PROCESSLIST_ID IS NOT NULL;
 
-- 分析查詢執(zhí)行詳情
SELECT * FROM performance_schema.events_statements_current 
WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads 
                  WHERE PROCESSLIST_ID = CONNECTION_ID());

-- 查看文件I/O等待情況
SELECT * FROM performance_schema.file_summary_by_event_name 
WHERE EVENT_NAME LIKE 'wait/io/file/%'
ORDER BY SUM_TIMER_WAIT DESC;
 
-- 分析表I/O性能
SELECT * FROM performance_schema.table_io_waits_summary_by_table 
WHERE OBJECT_SCHEMA = 'your_database';

-- 查看當(dāng)前鎖等待情況
SELECT * FROM performance_schema.data_lock_waits;
 
-- 分析鎖統(tǒng)計(jì)信息
SELECT * FROM performance_schema.metadata_locks;

-- 查看 Sys Schema 中的所有視圖
SELECT table_name, table_comment 
FROM information_schema.tables 
WHERE table_schema = 'sys' 
AND table_type = 'VIEW';

-- 查看當(dāng)前會(huì)話性能統(tǒng)計(jì)
SELECT * FROM sys.session 
WHERE conn_id = CONNECTION_ID();
 
-- 查看所有活動(dòng)會(huì)話
SELECT * FROM sys.processlist 
WHERE command != 'Sleep';
 
-- 會(huì)話I/O統(tǒng)計(jì)
SELECT * FROM sys.io_global_by_file_by_bytes 
LIMIT 10;

-- 查看內(nèi)存分配情況
SELECT * FROM sys.memory_global_total;
SELECT * FROM sys.memory_by_thread_by_current_bytes;
 
-- 緩沖池使用情況
SELECT * FROM sys.innodb_buffer_stats_by_schema;

-- 查看慢SQL統(tǒng)計(jì)
SELECT * FROM sys.statements_with_full_table_scans;
SELECT * FROM sys.statements_with_sorting;
SELECT * FROM sys.statements_with_temp_tables;
 
-- 最消耗資源的查詢
SELECT * FROM sys.statement_analysis 
ORDER BY avg_latency DESC 
LIMIT 10;

-- 檢查審計(jì)插件狀態(tài)
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE '%audit%';
 
-- 查看審計(jì)配置
SHOW VARIABLES LIKE 'audit%';

-- 安裝審計(jì)過(guò)濾器（企業(yè)版功能）
-- 引用文檔中提到的腳本：audit_log_filter_linux_install.sql
-- 此腳本配置基于規(guī)則的MySQL審計(jì)功能
 
-- 創(chuàng)建審計(jì)過(guò)濾器
SELECT audit_log_filter_set_filter('log_all', '{
    "filter": {
        "class": {
            "name": "general",
            "event": {
                "name": "status",
                "log": true
            }
        }
    }
}');
 
-- 將過(guò)濾器分配給用戶
SELECT audit_log_filter_set_user('%', 'log_all');

-- 查看審計(jì)日志狀態(tài)
SELECT * FROM mysql.audit_log_filter;
SELECT * FROM mysql.audit_log_user;
 
-- 旋轉(zhuǎn)審計(jì)日志
SET GLOBAL audit_log_flush = ON;

-- 監(jiān)控復(fù)制狀態(tài)
SHOW SLAVE STATUS;
SHOW MASTER STATUS;
 
-- 監(jiān)控InnoDB狀態(tài)
SHOW ENGINE INNODB STATUS;
 
-- 監(jiān)控鎖等待
SELECT * FROM sys.innodb_lock_waits;

-- 用戶權(quán)限審計(jì)
SELECT * FROM mysql.user;
SELECT * FROM information_schema.user_privileges;
 
-- 安全配置檢查
SHOW VARIABLES LIKE 'validate_password%';

-- 查看完整進(jìn)程列表
SHOW FULL PROCESSLIST;
 
-- 等價(jià)的信息Schema查詢
SELECT * FROM information_schema.processlist;

-- 終止特定連接
KILL 12345;

-- 按用戶分組統(tǒng)計(jì)連接數(shù)
SELECT USER, COUNT(*) as connection_count 
FROM information_schema.processlist 
GROUP BY USER;

-- 分析連接來(lái)源分布
SELECT SUBSTRING_INDEX(HOST, ':', 1) as client_host, COUNT(*)
FROM information_schema.processlist 
GROUP BY client_host;

-- 查看各數(shù)據(jù)庫(kù)的連接分布
SELECT db, COUNT(*) as connections 
FROM information_schema.processlist 
WHERE db IS NOT NULL 
GROUP BY db;

-- 分析命令類型分布
SELECT command, COUNT(*) as count 
FROM information_schema.processlist 
GROUP BY command 
ORDER BY count DESC;

-- 查找長(zhǎng)時(shí)間運(yùn)行的查詢
SELECT * FROM information_schema.processlist 
WHERE TIME > 60 
ORDER BY TIME DESC;

-- 分析線程狀態(tài)
SELECT state, COUNT(*) as count 
FROM information_schema.processlist 
WHERE state IS NOT NULL 
GROUP BY state 
ORDER BY count DESC;

-- 查看正在執(zhí)行的查詢
SELECT id, user, host, db, time, state, LEFT(info, 50) as query_preview
FROM information_schema.processlist 
WHERE info IS NOT NULL 
AND command != 'Sleep';

-- 查看用戶賬戶基本信息
SELECT user, host, authentication_string, account_locked, password_expired
FROM mysql.user;
 
-- 查看用戶權(quán)限詳情
SHOW GRANTS FOR 'username'@'host';

-- 查找使用通配符的主機(jī)名（安全風(fēng)險(xiǎn)）
SELECT user, host FROM mysql.user 
WHERE host LIKE '%\%' OR host = '%';
 
-- 安全的用戶定義示例
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'secure_password';
CREATE USER 'readonly_user'@'10.0.0.100' IDENTIFIED BY 'secure_password';

-- 創(chuàng)建角色
CREATE ROLE 'read_only', 'write_only', 'admin_role';
 
-- 為角色分配權(quán)限
GRANT SELECT ON database.* TO 'read_only';
GRANT INSERT, UPDATE, DELETE ON database.* TO 'write_only';
GRANT ALL PRIVILEGES ON database.* TO 'admin_role';
 
-- 將角色分配給用戶
GRANT 'read_only' TO 'app_user'@'localhost';
GRANT 'admin_role' TO 'dba_user'@'localhost';

-- 查看當(dāng)前角色
SELECT CURRENT_ROLE();
 
-- 激活角色
SET ROLE 'read_only';
 
-- 設(shè)置默認(rèn)角色
SET DEFAULT ROLE 'read_only' TO 'app_user'@'localhost';

-- 創(chuàng)建角色（默認(rèn)被鎖定）
CREATE ROLE 'r_admin';
 
-- 驗(yàn)證角色狀態(tài)
SELECT user, host, account_locked 
FROM mysql.user 
WHERE user = 'r_admin';
 
-- 將角色轉(zhuǎn)換為可登錄賬戶
ALTER USER 'r_admin' IDENTIFIED BY 'secure_password' ACCOUNT UNLOCK;
 
-- 驗(yàn)證轉(zhuǎn)換結(jié)果
SELECT user, host, account_locked 
FROM mysql.user 
WHERE user = 'r_admin';

-- 授予FILE權(quán)限（謹(jǐn)慎使用）
GRANT FILE ON *.* TO 'backup_user'@'localhost';
 
-- FILE權(quán)限的使用場(chǎng)景
-- 1. SELECT ... INTO OUTFILE
SELECT * FROM sales_data INTO OUTFILE '/tmp/sales_backup.csv';
-- 2. LOAD DATA INFILE
LOAD DATA INFILE '/tmp/updated_data.csv' INTO TABLE sales_data;

-- 授予PROCESS權(quán)限
GRANT PROCESS ON *.* TO 'monitor_user'@'localhost';
 
-- PROCESS權(quán)限的使用
SHOW PROCESSLIST;
SELECT * FROM information_schema.processlist;
 
-- 監(jiān)控應(yīng)用示例
SELECT * FROM sys.processlist 
WHERE command != 'Sleep' 
AND time > 60;

-- 授予RELOAD權(quán)限
GRANT RELOAD ON *.* TO 'maintenance_user'@'localhost';
 
-- RELOAD權(quán)限的使用場(chǎng)景
FLUSH TABLES;
FLUSH LOGS;
FLUSH PRIVILEGES;
FLUSH STATUS;

-- 授予權(quán)限并允許轉(zhuǎn)授
GRANT SELECT, INSERT ON database.* TO 'manager_user'@'localhost' 
WITH GRANT OPTION;
 
-- 被授權(quán)用戶可以繼續(xù)授權(quán)給其他用戶
-- （以manager_user身份執(zhí)行）
GRANT SELECT ON database.table1 TO 'team_user'@'localhost';

-- 授予角色并允許轉(zhuǎn)授
GRANT 'admin_role' TO 'senior_dba'@'localhost' 
WITH ADMIN OPTION;
 
-- 被授權(quán)用戶可以繼續(xù)分配角色
-- （以senior_dba身份執(zhí)行）
GRANT 'admin_role' TO 'junior_dba'@'localhost';