MySQLでAudit Trailを構築する方法：エンタープライズアプリケーションのデータ変更履歴を追跡する

なぜエンタープライズアプリケーションにAudit Trailが必要なのか？

顧客から問い合わせが来た場面を想像してみてください。「注文のステータスが変わっているんですが、私は何もしていません」。データベースを開いて確認すると、確かにデータは変わっている——しかし誰が・何時に・元の値は何だったのかは誰にもわからない。

私自身もそのような状況を経験しました——しかも、もっとひどいケースで。障害が起きたのは深夜3時で、データベースが部分的にcorruptionしていました。バックアップからリストアするしかなかったのですが、どのデータが正常な変更でどれが障害によるものなのか、判別する手段がありませんでした。それ以来、バックアップだけでなく、すべてのデータ変更を記録することを強く意識するようになりました。

Audit Trail（Change Logとも呼ばれます）とは、重要なデータへのINSERT/UPDATE/DELETEの操作をすべて記録する履歴テーブルのことです——誰が・どのレコードに・いつ・何をしたか、変更前後の値は何かという問いに答えられます。

PCI DSS（カード決済）、HIPAA（医療）、ISO 27001といった標準規格では、機密データへのすべての変更を追跡できることが求められています。Audit Trailがなければ、監査を通過することはできません——それだけシンプルな話です。

Audit Logテーブルの設計

まずはログテーブルの設計が基盤となります——あらゆる問いに答えられるだけのデータを持ちつつ、システムを遅くするほど複雑にしない設計が理想です。以下は私が試行錯誤の末に採用した構造です：

CREATE TABLE audit_log (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    table_name  VARCHAR(100)   NOT NULL,
    record_id   BIGINT UNSIGNED NOT NULL,
    action      ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    changed_by  VARCHAR(100)   DEFAULT NULL,
    changed_at  DATETIME(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    old_data    JSON           DEFAULT NULL,
    new_data    JSON           DEFAULT NULL,
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_changed_at   (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

この構造を選んだ理由：

• old_data / new_dataはJSON型を使用——1つのログテーブルですべての業務テーブルに対応でき、テーブルごとに個別作成する必要がありません。
• changed_at DATETIME(3)——ミリ秒単位で保存。同じ1秒内に複数の操作が発生する場合に重要です。
• changed_by——操作を行ったユーザー。アプリケーションがセッション変数経由で渡します（次のセクション参照）。
• インデックス(table_name, record_id)で、1レコードの全変更履歴を数ミリ秒で検索できます。

セッション変数でユーザー情報をMySQLに渡す

TriggerはどのユーザーがアプリにログインしているかをMySQL接続からは知ることができません。解決策は、アプリケーションが操作を実行する前にセッション変数をセットすることです。

重要なクエリの前に以下の一行を追加します（PHP、Python、またはどの言語でも）：

-- アプリケーションがupdate/deleteの前にこのクエリを呼び出す
SET @current_user = '[email protected]';

Pythonでmysql-connectorを使う場合：

def execute_with_audit(conn, query, params, username):
    cursor = conn.cursor()
    # セッション変数を先にセット
    cursor.execute("SET @current_user = %s", (username,))
    # その後に実際のクエリを実行
    cursor.execute(query, params)
    conn.commit()
    cursor.close()

Trigger内では@current_userでこの値を取得します。

Auditが必要なテーブルへのTriggerを作成する

ordersテーブルを例にとります——ECシステムで最も機密性の高いテーブルです。AFTER INSERT、AFTER UPDATE、AFTER DELETEの3つのTriggerが必要です。

INSERT用Trigger

DELIMITER //
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, changed_by, new_data)
    VALUES (
        'orders',
        NEW.id,
        'INSERT',
        @current_user,
        JSON_OBJECT(
            'id',         NEW.id,
            'customer_id',NEW.customer_id,
            'status',     NEW.status,
            'total',      NEW.total,
            'created_at', NEW.created_at
        )
    );
END//
DELIMITER ;

UPDATE用Trigger

DELIMITER //
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, changed_by, old_data, new_data)
    VALUES (
        'orders',
        NEW.id,
        'UPDATE',
        @current_user,
        JSON_OBJECT(
            'customer_id',OLD.customer_id,
            'status',     OLD.status,
            'total',      OLD.total
        ),
        JSON_OBJECT(
            'customer_id',NEW.customer_id,
            'status',     NEW.status,
            'total',      NEW.total
        )
    );
END//
DELIMITER ;

DELETE用Trigger

DELIMITER //
CREATE TRIGGER trg_orders_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, changed_by, old_data)
    VALUES (
        'orders',
        OLD.id,
        'DELETE',
        @current_user,
        JSON_OBJECT(
            'id',         OLD.id,
            'customer_id',OLD.customer_id,
            'status',     OLD.status,
            'total',      OLD.total
        )
    );
END//
DELIMITER ;

なぜBEFOREではなくAFTERを使うのか？シンプルな理由です：操作が実際に成功したときだけログを記録したいからです。メイントランザクションがrollbackされると、Triggerもロールバックされるのでログとデータは常に同期が保たれます。

Audit Trailシステムのテスト

動作確認とログの確認

-- アプリケーションがユーザーをセットするのをシミュレート
SET @current_user = '[email protected]';

-- 新しい注文を作成
INSERT INTO orders (customer_id, status, total, created_at)
VALUES (42, 'pending', 1500000, NOW());

-- ステータスを更新
UPDATE orders SET status = 'confirmed', total = 1650000 WHERE id = LAST_INSERT_ID();

-- ログを確認
SELECT
    id,
    action,
    changed_by,
    changed_at,
    old_data,
    new_data
FROM audit_log
WHERE table_name = 'orders'
ORDER BY changed_at DESC
LIMIT 10;

特定レコードの変更履歴を検索するクエリ

-- 注文ID = 123の全変更履歴を表示
SELECT
    action,
    changed_by,
    changed_at,
    JSON_UNQUOTE(JSON_EXTRACT(old_data, '$.status')) AS old_status,
    JSON_UNQUOTE(JSON_EXTRACT(new_data, '$.status')) AS new_status,
    JSON_UNQUOTE(JSON_EXTRACT(old_data, '$.total'))  AS old_total,
    JSON_UNQUOTE(JSON_EXTRACT(new_data, '$.total'))  AS new_total
FROM audit_log
WHERE table_name = 'orders' AND record_id = 123
ORDER BY changed_at ASC;

モニタリング：不審な操作の検出

以下の2つのクエリを定期的に実行（例：cron jobで15分ごと）することで、不審な操作を早期に検出できます：

-- 過去1時間で最も多くレコードを削除したユーザーを検索
SELECT
    changed_by,
    COUNT(*) AS delete_count
FROM audit_log
WHERE action = 'DELETE'
  AND changed_at >= NOW() - INTERVAL 1 HOUR
GROUP BY changed_by
HAVING delete_count > 10
ORDER BY delete_count DESC;

-- 過去24時間のテーブル別全変更を表示
SELECT
    table_name,
    action,
    COUNT(*) AS total
FROM audit_log
WHERE changed_at >= NOW() - INTERVAL 24 HOUR
GROUP BY table_name, action
ORDER BY table_name, action;

実際に対処が必要な問題

ログテーブルは急速に大きくなる——PartitionまたはArchiveが必要

1日5,000件の注文を処理するシステムで、各注文が平均3〜4回ステータス更新されると、audit_logはordersテーブルだけで月約60万行蓄積されます。最もシンプルな解決策は月単位のpartitionです：

-- 月別RANGE partitionでaudit_logテーブルを作成
CREATE TABLE audit_log (
    id         BIGINT UNSIGNED AUTO_INCREMENT,
    -- ... その他のカラム ...
    changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id, changed_at)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(changed_at) * 100 + MONTH(changed_at)) (
    PARTITION p2026_01 VALUES LESS THAN (202602),
    PARTITION p2026_02 VALUES LESS THAN (202603),
    PARTITION p2026_03 VALUES LESS THAN (202604),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

1年以上古いログを削除したい場合はALTER TABLE audit_log DROP PARTITION p2025_01を実行するだけです——通常のDELETEと比べて50〜100倍高速です。MySQLが各行をスキャンする代わりに、partitionのメタデータを削除するだけで済むからです。

すべてのテーブルをAuditするべきではない

Triggerを設定するのは本当に必要なテーブルだけにしています：orders、payments、users、inventory。sessions、cache、job_queueは対象外です——Webリクエスト1回でsessionを数十回読み書きすることもあり、これらをauditしてもログテーブルが膨れ上がるだけで、追跡に価値はありません。

audit_logテーブルを削除から保護する

見落とされがちなリスクがあります。attackerがアプリケーションの接続を乗っ取った場合、証拠隠滅のためにaudit_logごと削除される可能性があります。専用ユーザーを作成して権限を分離しましょう：

CREATE USER 'audit_writer'@'localhost' IDENTIFIED BY 'strong_password';
GRANT INSERT ON yourdb.audit_log TO 'audit_writer'@'localhost';
-- UPDATE、DELETEはGRANTしない

注意：TriggerはMySQL操作を実行したユーザーの権限で動作します——そのユーザーがaudit_logへのINSERT権限を持っていないと、Triggerによる書き込みができません。

まとめ

半日でセットアップが完了し、完全な変更履歴の追跡能力が手に入ります。このパターンをいくつかのERPシステムやECサイトに導入してきましたが、データに関するインシデントが発生するたびに、最初に開くのがaudit_logです。アプリケーションログを何時間も掘り起こすのではなく、ほとんどの場合、数分以内に答えが見つかります。まだAudit Trailを導入していないシステムがあれば、今日がはじめる絶好のタイミングです。