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

Document ID : KB000042833
Last Modified Date : 05/07/2018
Show Technical Document Details
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.

 

    Instructions:

    The following procedure must be run from the privileged shell of the Gateways in the impacted cluster.

    1. Disable replication on both Gateways: mysqladmin stop-slave
    2. Block MySQL traffic on both Gateways: iptables -I INPUT -p tcp -m tcp --dport 3306 -j REJECT
    3. Verify MySQL communication has been severed on both Gateways. Terminate any persistent connections. netstat -na | grep 3306

    NOTE: If results similar to the following appear then?replication is not completely blocked:
    tcp 0 0 10.0.1.2:23159 10.0.1.3:3306 ESTABLISHED

    1. Take the primary Gateway out of service on the load balancer (if one is in use).
    2. Stop the primary Gateway service: service ssg stop
    3. Back up the existing primary Gateway database: mysqldump --all-databases | gzip > /root/audit-record-removal.sql.gz
    4. Access the primary Gateway database via the MySQL prompt: mysql ssg
    5. 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;

    1. Start the primary Gateway service: service ssg start
    2. Place the primary Gateway in service on the load balancer
    3. Take the secondary Gateway out of service on the load balancer
    4. Stop the Gateway service on the secondary: service ssg stop
    5. Unblock MySQL communication on both nodes: iptables -D INPUT -p tcp -m tcp --dport 3306 -j REJECT
    6. Reinitialize replication on the secondary to the primary: /opt/SecureSpan/Appliance/bin/create_slave.sh
    7. NOTE: Clone the primary Gateway database to the secondary node when prompted.
    8. Reinitialize replication on the primary to the secondary: /opt/SecureSpan/Appliance/bin/create_slave.sh
    9. NOTE: Do not clone the remote database when prompted.
    10. Start the Gateway service on the secondary: service ssg start
    11. Place the secondary Gateway in service on the load balancer.