What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage?
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?