Removing audit records from the Gateway database in a multi-node cluster without downtime

Products

• CA API Gateway

Issue/Introduction

The CA API Gateway retains audit records for processed message traffic within the local Gateway database unless configured to use the Internal Audit Sink Policy. In certain circumstances, the Gateway database may become overwhelmed with audit records if excessive auditing is enabled in a high-traffic environment. This document will prescribe how to remove the existing audit records from the Gateway database. It may be necessary to perform this process to expedite a Gateway database upgrade, facilitate the remediation of a failed replication configuration, or to complete a Gateway database migration from one environment to the other. As the Gateway database tablespace is almost exclusively consumed by audit records, the removal of these audit records can make the aforementioned processes simpler and faster.

Environment

API Gateway 10.X
MySQL 8.0

Resolution

It would be best practice to perform the following steps only in an urgent situation and ideally in a low-traffic situation too, otherwise the following article should be used instead to manage audit records on an on-going basis: Configuring and installing the audit record maintenance script.

The following must be run from the privileged shell of the Gateway nodes in the impacted cluster:

1. Back up the existing primary Gateway database: mysqldump --all-databases --set-gtid-purged=OFF | gzip > /root/audit-record-removal.sql.gz
2. Access the primary Gateway database via the MySQL prompt: mysql ssg
3. Execute the following MySQL queries:

NOTE: These commands can be block-copied and executed at once. The MySQL database will process each query serially.

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;

analyze table audit_admin;
analyze table audit_detail;
analyze table audit_detail_params;
analyze table audit_main;
analyze table audit_message;
analyze table audit_system;