How to configure binlog to get User-Information?

Question

Hi,

I need to get an auditrail in mysql; is there a way to configure the binary log to get not only the changes, also the user, (connection) who made this change? Or do I have to use mySQL Proxy?

TIA

Peter

Answer 1

I don't think its possible to have the binlog show connection info. My approach is to set triggers in the database that log to audit tables. For example, here is one from work:

CREATE TRIGGER whatever_audit_INSERT
  AFTER INSERT ON whatever FOR EACH ROW
  BEGIN
    INSERT INTO whatever_audit(
      audit_when_start, audit_who_start, col1, col2
    ) VALUES (
      now(), @app_user, new.col1, new.col2
    )
END

That's from memory; hopefully I got the syntax right...