Ubuntu 22.04 MySQL 稽核日誌設定

MySQL 稽核功能概述

在現代企業環境中，資料庫稽核是資訊安全的重要環節。MySQL 稽核日誌可以記錄所有資料庫操作，包括登入嘗試、查詢執行、資料修改等活動。這些日誌對於安全事件調查、合規性審計以及效能分析都具有重要價值。

為什麼需要 MySQL 稽核

安全性：偵測未授權存取和可疑活動
合規性：滿足 PCI DSS、HIPAA、SOX 等法規要求
問題排除：追蹤資料庫問題的根本原因
效能監控：分析查詢模式和效能瓶頸
責任歸屬：確認誰在何時做了什麼操作

Ubuntu 22.04 環境準備

首先確認 MySQL 版本和系統環境：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# 檢查 Ubuntu 版本
lsb_release -a

# 檢查 MySQL 版本
mysql --version

# 安裝 MySQL（如尚未安裝）
sudo apt update
sudo apt install mysql-server -y

# 啟動並設定開機自動啟動
sudo systemctl start mysql
sudo systemctl enable mysql

Enterprise Audit Plugin

MySQL Enterprise Audit 是 Oracle 官方提供的商業版稽核解決方案，僅在 MySQL Enterprise Edition 中可用。

Enterprise Audit 特點

完整的稽核功能：支援所有 SQL 操作的稽核
XML 和 JSON 格式：靈活的日誌輸出格式
效能優化：經過 Oracle 優化的實作
官方支援：享有 Oracle 技術支援

安裝 Enterprise Audit Plugin

如果您擁有 MySQL Enterprise Edition 授權：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# 登入 MySQL
mysql -u root -p

# 安裝稽核 Plugin
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

# 確認安裝成功
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'audit%';

基本設定

在 /etc/mysql/mysql.conf.d/mysqld.cnf 中加入設定：

1
2
3
4
5
6
7
[mysqld]
# 啟用稽核日誌
audit_log_format = JSON
audit_log_file = /var/log/mysql/audit.log
audit_log_policy = ALL
audit_log_rotate_on_size = 104857600
audit_log_rotations = 7

重新啟動 MySQL 使設定生效：

1
sudo systemctl restart mysql

開源替代方案：MariaDB Audit Plugin

對於使用 MySQL Community Edition 的用戶，MariaDB Audit Plugin 是一個優秀的開源替代方案。此 Plugin 與 MySQL Community Edition 相容，提供完整的稽核功能。

安裝 MariaDB Audit Plugin

1
2
3
4
5
6
7
8
9
# 安裝必要的開發工具
sudo apt install build-essential cmake libssl-dev -y

# 下載 MariaDB Audit Plugin 原始碼
cd /tmp
git clone https://github.com/MariaDB/server.git --depth 1 --branch 10.11

# 或者直接使用預編譯的 Plugin（推薦）
# 從 MariaDB 官網下載對應版本

使用 Percona Audit Log Plugin（推薦替代方案）

Percona 提供了一個與 MySQL Community Edition 完全相容的稽核 Plugin：

1
2
3
4
5
6
7
# 安裝 Percona 套件庫
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update

# 安裝 Percona Audit Log Plugin
sudo apt install percona-audit-log-plugin -y

啟用 Percona Audit Log Plugin

1
2
3
4
5
6
7
8
-- 登入 MySQL
mysql -u root -p

-- 安裝 Plugin
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 驗證安裝
SHOW PLUGINS;

McAfee MySQL Audit Plugin（開源選項）

另一個熱門的開源選擇是 McAfee MySQL Audit Plugin：

1
2
3
4
5
6
7
# 下載 McAfee Audit Plugin
cd /tmp
git clone https://github.com/mcafee/mysql-audit.git
cd mysql-audit

# 編譯安裝（需要對應 MySQL 版本的原始碼）
# 請參考官方文件進行編譯

設定 McAfee Audit Plugin：

1
2
3
4
5
6
-- 安裝 Plugin
INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

-- 設定稽核規則
SET GLOBAL audit_json_file = ON;
SET GLOBAL audit_json_log_file = '/var/log/mysql/mysql-audit.json';

稽核事件類型與過濾

事件類型分類

MySQL 稽核可以捕獲以下類型的事件：

事件類型	說明	範例
CONNECT	連線相關事件	登入、登出、連線失敗
QUERY	SQL 查詢事件	SELECT、INSERT、UPDATE、DELETE
TABLE	資料表存取事件	資料表讀取、寫入
QUERY_DDL	資料定義語言	CREATE、ALTER、DROP
QUERY_DML	資料操作語言	INSERT、UPDATE、DELETE
QUERY_DCL	資料控制語言	GRANT、REVOKE

設定事件過濾

在 MySQL 設定檔中配置過濾規則：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[mysqld]
# 僅記錄特定事件類型
audit_log_policy = LOGINS
# 可選值：ALL, LOGINS, QUERIES, NONE

# 排除特定使用者
audit_log_exclude_accounts = 'monitoring@localhost,backup@localhost'

# 包含特定使用者
audit_log_include_accounts = 'admin@%,app_user@%'

使用過濾函數（MySQL 8.0+）

MySQL 8.0 引入了更靈活的過濾機制：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- 建立過濾規則
SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');

-- 建立僅記錄 DML 操作的過濾器
SELECT audit_log_filter_set_filter('log_dml_only', '{
  "filter": {
    "class": [
      { "name": "table_access" }
    ]
  }
}');

-- 將過濾器指派給使用者
SELECT audit_log_filter_set_user('%', 'log_all');
SELECT audit_log_filter_set_user('app_user@%', 'log_dml_only');

-- 移除使用者的過濾器
SELECT audit_log_filter_remove_user('app_user@%');

進階過濾範例

建立複雜的過濾規則：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 記錄所有失敗的登入嘗試
SELECT audit_log_filter_set_filter('failed_logins', '{
  "filter": {
    "class": [
      {
        "name": "connection",
        "event": [
          {
            "name": "connect",
            "log": {
              "field": { "name": "status", "value": { "not": 0 } }
            }
          }
        ]
      }
    ]
  }
}');

-- 記錄對敏感資料表的所有存取
SELECT audit_log_filter_set_filter('sensitive_tables', '{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "log": {
          "field": {
            "name": "table_name.str",
            "value": ["customers", "credit_cards", "passwords"]
          }
        }
      }
    ]
  }
}');

日誌格式與儲存

JSON 格式日誌

JSON 格式是現代化的日誌格式，便於機器解析：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
{
  "timestamp": "2025-09-04T10:30:45.123456Z",
  "id": 1,
  "class": "connection",
  "event": "connect",
  "connection_id": 12345,
  "account": {
    "user": "app_user",
    "host": "192.168.1.100"
  },
  "login": {
    "user": "app_user",
    "os": "",
    "ip": "192.168.1.100",
    "proxy": ""
  },
  "connection_data": {
    "connection_type": "ssl",
    "status": 0,
    "db": "production_db"
  }
}

查詢事件的 JSON 格式：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
{
  "timestamp": "2025-09-04T10:31:22.654321Z",
  "id": 2,
  "class": "general",
  "event": "query",
  "connection_id": 12345,
  "account": {
    "user": "app_user",
    "host": "192.168.1.100"
  },
  "query_data": {
    "query": "SELECT * FROM customers WHERE id = 123",
    "status": 0,
    "sql_command": "select"
  }
}

XML 格式日誌

傳統的 XML 格式仍被許多 SIEM 系統支援：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
<AUDIT_RECORD>
  <TIMESTAMP>2025-09-04T10:30:45.123456Z</TIMESTAMP>
  <RECORD_ID>1</RECORD_ID>
  <NAME>Connect</NAME>
  <CONNECTION_ID>12345</CONNECTION_ID>
  <STATUS>0</STATUS>
  <USER>app_user</USER>
  <HOST>192.168.1.100</HOST>
  <OS_USER></OS_USER>
  <IP>192.168.1.100</IP>
  <DB>production_db</DB>
</AUDIT_RECORD>

日誌輪替設定

設定自動日誌輪替以管理磁碟空間：

1
2
3
4
5
6
7
8
9
[mysqld]
# 當日誌達到 100MB 時輪替
audit_log_rotate_on_size = 104857600

# 保留 7 個輪替檔案
audit_log_rotations = 7

# 日誌檔案路徑
audit_log_file = /var/log/mysql/audit.log

使用 logrotate 進行更細緻的控制：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
# 建立 logrotate 設定檔
sudo tee /etc/logrotate.d/mysql-audit << 'EOF'
/var/log/mysql/audit.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    postrotate
        mysqladmin flush-logs
    endscript
}
EOF

日誌儲存最佳實踐

1
2
3
4
5
6
7
# 建立專用的日誌目錄
sudo mkdir -p /var/log/mysql/audit
sudo chown mysql:adm /var/log/mysql/audit
sudo chmod 750 /var/log/mysql/audit

# 設定適當的權限
sudo chmod 640 /var/log/mysql/audit/*.log

效能影響與優化

效能影響評估

啟用稽核日誌會對資料庫效能產生一定影響。以下是主要考量因素：

因素	影響程度	說明
日誌量	高	記錄的事件越多，I/O 負擔越大
日誌格式	中	JSON 比 XML 略慢但更緊湊
同步寫入	高	同步寫入確保日誌完整但影響效能
過濾規則	低	複雜過濾規則輕微增加 CPU 使用

效能優化策略

1. 使用非同步日誌寫入

1
2
3
4
5
6
[mysqld]
# 使用非同步寫入模式
audit_log_strategy = ASYNCHRONOUS

# 設定緩衝區大小
audit_log_buffer_size = 4194304

2. 實施智慧過濾

只記錄必要的事件：

1
2
3
4
5
6
7
8
9
-- 僅記錄 DDL 和登入失敗
SELECT audit_log_filter_set_filter('minimal', '{
  "filter": {
    "class": [
      { "name": "connection", "event": [{ "name": "connect", "log": { "field": { "name": "status", "value": { "not": 0 } } } }] },
      { "name": "general", "event": [{ "name": "status", "log": { "field": { "name": "sql_command", "value": ["create", "alter", "drop", "grant", "revoke"] } } }] }
    ]
  }
}');

3. 使用獨立的日誌磁碟

1
2
3
4
# 將稽核日誌移至獨立磁碟
sudo mkdir -p /mnt/audit_logs/mysql
sudo mount /dev/sdb1 /mnt/audit_logs
sudo chown mysql:mysql /mnt/audit_logs/mysql

更新 MySQL 設定：

1
2
[mysqld]
audit_log_file = /mnt/audit_logs/mysql/audit.log

4. 效能監控腳本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
#!/bin/bash
# mysql_audit_monitor.sh - 監控稽核日誌效能影響

# 檢查稽核日誌大小
AUDIT_SIZE=$(du -sh /var/log/mysql/audit.log 2>/dev/null | awk '{print $1}')
echo "稽核日誌大小: $AUDIT_SIZE"

# 檢查日誌寫入速率
CURRENT_SIZE=$(stat -c %s /var/log/mysql/audit.log 2>/dev/null)
sleep 60
NEW_SIZE=$(stat -c %s /var/log/mysql/audit.log 2>/dev/null)
RATE=$(( (NEW_SIZE - CURRENT_SIZE) / 60 ))
echo "日誌寫入速率: ${RATE} bytes/sec"

# 檢查 MySQL 效能指標
mysql -u root -e "SHOW GLOBAL STATUS LIKE 'Audit%';" 2>/dev/null

基準測試

執行效能基準測試以評估稽核對系統的影響：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 使用 sysbench 進行基準測試
sysbench oltp_read_write \
  --mysql-host=localhost \
  --mysql-user=test \
  --mysql-password=test \
  --mysql-db=test \
  --tables=10 \
  --table-size=100000 \
  --threads=4 \
  --time=300 \
  run

與 SIEM 整合

Elasticsearch + Kibana 整合

使用 Filebeat 將稽核日誌送至 Elasticsearch：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
# /etc/filebeat/filebeat.yml
filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /var/log/mysql/audit.log
  json.keys_under_root: true
  json.add_error_key: true
  fields:
    log_type: mysql_audit
  fields_under_root: true

output.elasticsearch:
  hosts: ["elasticsearch:9200"]
  index: "mysql-audit-%{+yyyy.MM.dd}"

setup.kibana:
  host: "kibana:5601"

安裝並啟動 Filebeat：

1
2
3
4
5
6
7
8
9
# 安裝 Filebeat
wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add -
echo "deb https://artifacts.elastic.co/packages/8.x/apt stable main" | sudo tee /etc/apt/sources.list.d/elastic-8.x.list
sudo apt update
sudo apt install filebeat -y

# 啟動 Filebeat
sudo systemctl enable filebeat
sudo systemctl start filebeat

Splunk 整合

建立 Splunk 輸入設定：

1
2
3
4
5
# /opt/splunkforwarder/etc/apps/mysql_audit/local/inputs.conf
[monitor:///var/log/mysql/audit.log]
disabled = false
sourcetype = mysql:audit:json
index = security

建立欄位抽取設定：

1
2
3
4
5
6
7
8
# /opt/splunkforwarder/etc/apps/mysql_audit/local/props.conf
[mysql:audit:json]
SHOULD_LINEMERGE = false
LINE_BREAKER = ([\r\n]+)
KV_MODE = json
TIME_FORMAT = %Y-%m-%dT%H:%M:%S.%6N%Z
TIME_PREFIX = "timestamp":"
MAX_TIMESTAMP_LOOKAHEAD = 32

Graylog 整合

設定 Graylog 接收 MySQL 稽核日誌：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# 使用 rsyslog 轉發日誌
sudo tee /etc/rsyslog.d/50-mysql-audit.conf << 'EOF'
module(load="imfile")
input(type="imfile"
      File="/var/log/mysql/audit.log"
      Tag="mysql-audit"
      Severity="info"
      Facility="local0")

local0.* @graylog.example.com:1514
EOF

sudo systemctl restart rsyslog

警報規則設定

在 SIEM 中設定重要的警報規則：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 範例：Elasticsearch Watcher 警報
{
  "trigger": {
    "schedule": { "interval": "5m" }
  },
  "input": {
    "search": {
      "request": {
        "indices": ["mysql-audit-*"],
        "body": {
          "query": {
            "bool": {
              "must": [
                { "match": { "event": "connect" } },
                { "range": { "connection_data.status": { "gt": 0 } } }
              ],
              "filter": {
                "range": { "@timestamp": { "gte": "now-5m" } }
              }
            }
          }
        }
      }
    }
  },
  "condition": {
    "compare": { "ctx.payload.hits.total.value": { "gt": 10 } }
  },
  "actions": {
    "email_admin": {
      "email": {
        "to": "security@example.com",
        "subject": "MySQL 登入失敗警報",
        "body": "偵測到 {{ctx.payload.hits.total.value}} 次登入失敗"
      }
    }
  }
}

合規性需求對應

PCI DSS 合規

PCI DSS（支付卡產業資料安全標準）要求追蹤所有資料存取：

要求	稽核設定	說明
10.2.1	CONNECT 事件	記錄所有使用者存取
10.2.2	管理員操作	記錄 root 使用者所有操作
10.2.4	無效存取嘗試	記錄登入失敗事件
10.2.5	權限變更	記錄 GRANT、REVOKE 操作
10.2.6	稽核日誌操作	保護日誌完整性

PCI DSS 合規設定範例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- 建立 PCI DSS 合規過濾器
SELECT audit_log_filter_set_filter('pci_dss', '{
  "filter": {
    "class": [
      { "name": "connection" },
      { "name": "general", "event": [
        { "name": "status", "log": {
          "field": { "name": "sql_command", "value": ["grant", "revoke", "create", "alter", "drop"] }
        }}
      ]},
      { "name": "table_access", "log": {
        "field": { "name": "table_database.str", "value": ["cardholder_data"] }
      }}
    ]
  }
}');

-- 套用至所有使用者
SELECT audit_log_filter_set_user('%', 'pci_dss');

HIPAA 合規

HIPAA（健康保險可攜性與責任法案）要求保護醫療資訊：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
-- 建立 HIPAA 合規過濾器
SELECT audit_log_filter_set_filter('hipaa', '{
  "filter": {
    "class": [
      { "name": "connection" },
      { "name": "table_access", "log": {
        "field": { "name": "table_name.str", "value": ["patients", "medical_records", "prescriptions", "diagnoses"] }
      }},
      { "name": "general", "event": [
        { "name": "status", "log": {
          "field": { "name": "sql_command", "value": ["update", "delete", "insert"] }
        }}
      ]}
    ]
  }
}');

SOX 合規

沙賓法案（Sarbanes-Oxley）要求財務資料完整性：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- 建立 SOX 合規過濾器
SELECT audit_log_filter_set_filter('sox', '{
  "filter": {
    "class": [
      { "name": "connection" },
      { "name": "table_access", "log": {
        "field": { "name": "table_database.str", "value": ["financial_db", "accounting_db"] }
      }},
      { "name": "general", "event": [
        { "name": "status", "log": true }
      ]}
    ]
  }
}');

一般資料保護規則要求追蹤個人資料存取：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
-- 建立 GDPR 合規過濾器
SELECT audit_log_filter_set_filter('gdpr', '{
  "filter": {
    "class": [
      { "name": "connection" },
      { "name": "table_access", "log": {
        "field": { "name": "table_name.str", "value": ["users", "personal_data", "customer_info", "contact_details"] }
      }},
      { "name": "general", "event": [
        { "name": "status", "log": {
          "field": { "name": "sql_command", "value": ["select", "update", "delete", "insert"] }
        }}
      ]}
    ]
  }
}');

合規性報告自動化

建立自動化報告腳本：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#!/bin/bash
# compliance_report.sh - 產生合規性報告

REPORT_DATE=$(date +%Y-%m-%d)
REPORT_DIR="/var/reports/mysql-audit"
mkdir -p "$REPORT_DIR"

# 產生每日摘要報告
mysql -u audit_reader -p"${AUDIT_PASSWORD}" << EOF > "$REPORT_DIR/daily_summary_${REPORT_DATE}.txt"
-- 登入統計
SELECT
    DATE(timestamp) as date,
    COUNT(*) as total_connections,
    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as successful,
    SUM(CASE WHEN status != 0 THEN 1 ELSE 0 END) as failed
FROM mysql.audit_log
WHERE DATE(timestamp) = CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(timestamp);

-- 高風險操作
SELECT
    user,
    sql_command,
    COUNT(*) as operation_count
FROM mysql.audit_log
WHERE DATE(timestamp) = CURDATE() - INTERVAL 1 DAY
  AND sql_command IN ('drop', 'truncate', 'grant', 'revoke')
GROUP BY user, sql_command;
EOF

# 發送報告
mail -s "MySQL 稽核日報 - ${REPORT_DATE}" security@example.com < "$REPORT_DIR/daily_summary_${REPORT_DATE}.txt"

結論

MySQL 稽核日誌是確保資料庫安全性和滿足合規性要求的關鍵工具。在 Ubuntu 22.04 環境中，您可以選擇：

MySQL Enterprise Audit：適合有商業授權需求的企業
Percona Audit Log Plugin：優秀的開源替代方案
McAfee MySQL Audit Plugin：另一個可靠的開源選擇

無論選擇哪種方案，都應該：

根據合規需求設定適當的事件過濾
實施效能優化策略以降低對生產系統的影響
與 SIEM 系統整合以實現集中化監控和警報
定期審查稽核日誌和產生合規性報告

透過正確設定 MySQL 稽核日誌，您可以有效保護敏感資料、偵測安全威脅，並滿足各種法規合規要求。