MySQL Triggers for WordPress Database Audit Logging

A MySQL trigger is a named database object that automatically executes a block of SQL statements in response to an INSERT, UPDATE, or DELETE event on a specific table — the trigger fires before or after the triggering statement (BEFORE INSERT, AFTER UPDATE, AFTER DELETE, etc.) and has access to the NEW (post-change values) and OLD (pre-change values) virtual rows. In the WordPress context, triggers provide database-level audit logging that captures every change to critical tables — wp_users, wp_options, wp_posts, wp_capabilities — regardless of whether the change came from a WordPress plugin, WP-CLI, phpMyAdmin, or a direct mysql connection. This is a key advantage over application-level audit plugins: an attacker or misconfigured script that bypasses WordPress and writes directly to the database is still logged by the trigger. Triggers write to an audit table that is designed for append-only use — the trigger user needs INSERT privilege on the audit table but ideally not DELETE or UPDATE, so audit records cannot be tampered with through normal application channels. The trigger body uses the same SQL that stored procedures use: local variables, IF branching, and calls to built-in functions like USER() (current MySQL user), DATABASE(), and NOW(). Performance consideration: triggers fire synchronously inside the triggering transaction — a trigger that does complex work on a high-write table (like wp_postmeta) adds latency to every post save. For wp_options auditing the overhead is acceptable because options writes are infrequent; for high-frequency tables, consider an AFTER INSERT trigger that does a single fast INSERT into the audit table with no branching. The Stored Procedures post covered encapsulating multi-statement logic; triggers extend that pattern with event-driven automatic execution on table writes.

Problem: A WordPress multisite network with 15 sites had a wp_options row modified by an unknown process that changed the admin_email option — the change was discovered two weeks later. No WordPress audit plugin was active, so there is no record of when the change was made or what MySQL user made it. Future changes to critical options and user capabilities need to be logged at the database level regardless of the entry point.

Solution: Create a dedicated wp_audit_log table and MySQL triggers on wp_options and wp_usermeta that record every change to critical rows — capturing the old value, new value, MySQL user, and timestamp automatically.

-- ── Create audit log table ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS wp_audit_log (
    audit_id     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    audit_time   DATETIME        NOT NULL DEFAULT NOW(),
    db_user      VARCHAR(128)    NOT NULL DEFAULT '',
    table_name   VARCHAR(64)     NOT NULL DEFAULT '',
    operation    ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    row_pk       VARCHAR(255)    NOT NULL DEFAULT '',  -- primary key of changed row
    column_name  VARCHAR(64)     NOT NULL DEFAULT '',
    old_value    LONGTEXT,
    new_value    LONGTEXT,
    PRIMARY KEY  (audit_id),
    INDEX        idx_time  (audit_time),
    INDEX        idx_table (table_name, audit_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Trigger: audit critical wp_options rows ───────────────────────────────────
DELIMITER $$

CREATE TRIGGER IF NOT EXISTS trg_wp_options_audit
AFTER UPDATE ON wp_options
FOR EACH ROW
BEGIN
    -- Only log changes to security-sensitive options
    IF OLD.option_name IN (
        'admin_email', 'blogname', 'siteurl', 'home',
        'default_role', 'users_can_register', 'active_plugins',
        'template', 'stylesheet'
    ) AND OLD.option_value != NEW.option_value THEN

        INSERT INTO wp_audit_log
            (db_user, table_name, operation, row_pk, column_name, old_value, new_value)
        VALUES
            (USER(), 'wp_options', 'UPDATE', OLD.option_name,
             'option_value', LEFT(OLD.option_value, 2000), LEFT(NEW.option_value, 2000));
    END IF;
END$$

-- ── Trigger: audit user capability changes ────────────────────────────────────
CREATE TRIGGER IF NOT EXISTS trg_wp_usermeta_caps_audit
AFTER UPDATE ON wp_usermeta
FOR EACH ROW
BEGIN
    IF OLD.meta_key = 'ynYYAa7bFT_capabilities'
       AND OLD.meta_value != NEW.meta_value THEN

        INSERT INTO wp_audit_log
            (db_user, table_name, operation, row_pk, column_name, old_value, new_value)
        VALUES
            (USER(), 'wp_usermeta', 'UPDATE', CONCAT('user_id=', OLD.user_id),
             'capabilities', LEFT(OLD.meta_value, 2000), LEFT(NEW.meta_value, 2000));
    END IF;
END$$

-- ── Trigger: audit user account creation ──────────────────────────────────────
CREATE TRIGGER IF NOT EXISTS trg_wp_users_insert_audit
AFTER INSERT ON wp_users
FOR EACH ROW
BEGIN
    INSERT INTO wp_audit_log
        (db_user, table_name, operation, row_pk, column_name, old_value, new_value)
    VALUES
        (USER(), 'wp_users', 'INSERT', CONCAT('ID=', NEW.ID),
         'user_login', NULL, NEW.user_login);
END$$

-- ── Trigger: audit user account deletion ──────────────────────────────────────
CREATE TRIGGER IF NOT EXISTS trg_wp_users_delete_audit
BEFORE DELETE ON wp_users
FOR EACH ROW
BEGIN
    INSERT INTO wp_audit_log
        (db_user, table_name, operation, row_pk, column_name, old_value, new_value)
    VALUES
        (USER(), 'wp_users', 'DELETE', CONCAT('ID=', OLD.ID),
         'user_login', OLD.user_login, NULL);
END$$

DELIMITER ;

// ── Read the audit log in WordPress admin ──────────────────────────────────────
function myplugin_get_recent_audit_entries( int $limit = 50 ): array {
    global $wpdb;

    return (array) $wpdb->get_results( $wpdb->prepare(
        "SELECT audit_id, audit_time, db_user, table_name, operation,
                row_pk, column_name,
                LEFT(old_value, 200) AS old_value,
                LEFT(new_value, 200) AS new_value
         FROM   wp_audit_log
         ORDER  BY audit_time DESC
         LIMIT  %d",
        $limit
    ), ARRAY_A );
}

// Register a WP-CLI command to query the audit log
if ( defined( 'WP_CLI' ) && WP_CLI ) {
    WP_CLI::add_command( 'audit log', function( array $args, array $assoc_args ): void {
        $limit   = (int) ( $assoc_args['limit'] ?? 20 );
        $table   = sanitize_key( $assoc_args['table'] ?? '' );
        $entries = myplugin_get_recent_audit_entries( $limit );

        if ( $table ) {
            $entries = array_filter( $entries, fn( $e ) => $e['table_name'] === $table );
        }

        WP_CLI\Utils\format_items( 'table', $entries,
            [ 'audit_time', 'db_user', 'table_name', 'operation', 'row_pk', 'column_name' ]
        );
    }, [
        'synopsis' => [
            [ 'type' => 'assoc', 'name' => 'limit', 'optional' => true, 'default' => 20 ],
            [ 'type' => 'assoc', 'name' => 'table', 'optional' => true ],
        ],
    ] );
}

NOTE: MySQL triggers are not included in a standard mysqldump output unless the --triggers flag is passed — which is actually the default behaviour, so a plain mysqldump helloadmin > backup.sql does include triggers. However, if your backup tool uses SELECT INTO OUTFILE or a table-by-table dump without the trigger dump step, triggers will be missing from the backup. Verify with SHOW TRIGGERS FROM helloadmin; and grep -c "CREATE TRIGGER" backup.sql after taking a backup. Also, a trigger that fails (e.g., due to a constraint violation on the audit table) will cause the triggering statement to fail and roll back — if the audit table is unavailable, all UPDATEs on wp_options will fail. Set a CONTINUE HANDLER FOR SQLEXCEPTION inside the trigger to log to an error table or silently continue on audit write failures to avoid cascading failures on production.