Purge Audit

[MTrucillo]

Hi to all,

I need to purge audit from layer7 db. 

I'm trying to backup ssg database but if I try I receive 


mysqldump ssg --skip-extended-insert --hex-blob --set-gtid-purged=off --routines --single-transaction --compress --quick --skip-lock-tables -r /opt/ssg1.sqlmysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `audit_detail` at row: 1603657


skipping audit table 

mysqldump ssg --skip-extended-insert --hex-blob --set-gtid-purged=off --routines --single-transaction --compress --quick --skip-lock-tables --ignore-table=ssg.audit_detail --ignore-table=ssg.audit_detail_params --ignore-table=ssg.audit_main -r /opt/ssg.sql

it work!

So,
How I can safely purge my audit log ? I've heard of a "purge audit script"  but I couldn't find it. Where is it ?

Can I do a truncate of the this tables:? ssg.audit_* ?

thanks in advance

[Zhijun He]

Hi Marco,
Please truncate the audit tables by statements below,
SET FOREIGN_KEY_CHECKS = 0;
truncate table audit_admin;
optimize table audit_admin;
truncate table audit_detail;
optimize table audit_detail;
truncate table audit_detail_params;
optimize table audit_detail_params;
truncate table audit_main;
optimize table audit_main;
truncate table audit_message;
optimize table audit_message;
truncate table audit_system;
optimize table audit_system;
SET FOREIGN_KEY_CHECKS = 1;

For the audit purge script, please refer to KB article,
https://knowledge.broadcom.com/external/article?articleId=42480

NOTE: please don't use the script for large audit tables, because the script uses batch deletion, which is costly and can cause mysql 100% busy -- that could crash gateway. And it may last hours, days ... depends on the size, while the truncate only take few seconds.
The audit purge script should only be used for new created ssg database, or after truncating the audit tables. And, even for small audit tables, it should be scheduled to run at non-business hour.
NOTE2: Audits should be disabled on production env, (ssg log includes all audit messages by default)
https://knowledge.broadcom.com/external/article?articleId=57563


Regards,
Mark

[MTrucillo]

Thanks! :)