Data – Changes Audit Trail

You would probably want to use the general query log.

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.

One important thing with logging for security is that an attacker cannot access the log to erase traces of their presence, so consider append-only files.

FWIW in Oracle we can send logs automatically to a remote syslog, but I don't believe MySQL has this feature yet. Perhaps you could fake it with SNMP but I have not tried it.

Are there other ways this has been dealt with?

If you use MySQL Replication, you could have a Master/Slave that has the following:

• AUDIT_LOG as InnoDB on the Master
• AUDIT_LOG as ARCHIVE on the Slave

This would provide fast writing of the AUDIT_LOG on the Master and makes the data available for reads almost instantly. The Slave would have the backup as an ARCHIVE with a small disk footprint.

DO NOT SEND ANY ALTER TABLE SQL AGAINST AUDIT_LOG ONCE REPLICATION IS SET UP. Otherwise, you lose the small disk footprint feature of the ARCHIVE Storage Engine.

Any drawbacks or trade-offs that are apparent?

You must configure InnoDB

• with enough RAM to accommodate data and indexes that are frequently queried : What are the main differences between InnoDB and MyISAM?
• with tablespaces for table separate from ibdata1 : What is the best way to reduce the size of ibdata in mysql?
• to access multiple CPUs (if using MySQL 5.5) : Possible to make MySQL use more than one core?