How to Purge Audit Data in Gateway 9.3 and Newer

Document ID : KB000074486
Last Modified Date : 22/03/2018
Show Technical Document Details
Introduction:
If the CA API Gateway ("Gateway") was upgraded from version 9.2 to 9.3, the steps must be followed in the related KB article to move the Gateway database tables from the system tablespace (ibdata file) to its own tablespace (.ibd file) prior to running the steps below.
Instructions:
This procedure consists of several smaller steps:
  1. Block MySQL traffic between appliances
  2. Back up databases
  3. Purge existing audit data
  4. Unblock MySQL traffic
  5. Reinitialize replication

Block MySQL traffic between appliances

  1. Log onto the primary Gateway database node as the ssgconfig user.
  2. Select Option 3: Use a privileged shell (root).
  3. Close the port used for MySQL for all connections originating from the secondary Gateway database node.
    • NOTE: The value of "SECONDARY-IP" will need to be the IP address of the management interface of the secondary Gateway database node.

      iptables -I INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3306 -j REJECT
      iptables -I INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3307 -j REJECT
  • Log onto the secondary Gateway database node as the ssgconfig user.
  • Select Option 3: Use a privileged shell (root).
  • Close the port used for MySQL for all connections originating from the primary Gateway database node.
    • NOTE: The value of "PRIMARY-IP" will need to be the IP address of the management interface of the primary Gateway database node.

      iptables -I INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3306 -j REJECT
      iptables -I INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3307 -j REJECT

Back up databases

  1. Stop the Gateway service: service ssg stop
  2. Back up the SSG database alone on the primary Gateway: mysqldump --databases ssg | gzip > /root/ssg-db-backup.sql.gz
  3. Back up all databases on the primary Gateway as an extra precaution: mysqldump --all-databases ssg | gzip > /root/all-db-backup.sql.gz

Purge existing audit data

  1. Access the primary Gateway database via the MySQL prompt: mysql ssg
  2. Execute the following MySQL queries:
    • 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;
  3. Exit mysql: exit
  4. Start the Gateway service on the primary Gateway: service ssg start

Unblock MySQL traffic

  1. Stop replication on each Gateway database node from the privileged shell: mysql -e "stop slave"
  2. Reopen MySQL communication on the primary Gateway database node:
    • NOTE: The value of "SECONDARY-IP" will need to be the IP address of the management interface of the secondary Gateway database node.

      iptables -D INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3306 -j REJECT
      iptables -D INPUT -i eth0 -p tcp -m tcp -s SECONDARY-IP --dport 3307 -j REJECT
  3. Reopen MySQL communication on the secondary Gateway database node:
    • NOTE: The value of "PRIMARY-IP" will need to be the IP address of the management interface of the primary Gateway database node.

      iptables -D INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3306 -j REJECT
      iptables -D INPUT -i eth0 -p tcp -m tcp -s PRIMARY-IP --dport 3307 -j REJECT

Reinitialize replication

  1. Reinitialize replication on the secondary node: /opt/SecureSpan/Appliance/bin/create_slave.sh
    • ​​​​NOTE: Specify the FQDN, credentials, and port of the primary Gateway database node. When prompted, clone the database from the primary Gateway database node.
  2. ​Reinitialize replication on the primary node: /opt/SecureSpan/Appliance/bin/create_slave.sh
    • NOTE: Specify the FQDN, credentials, and port of the secondary Gateway database node. When prompted, do not clone the database from the secondary Gateway database node.
  3. Verify successful initialization of replication: mysql -e "show slave status\G" | grep ": Yes"
    • ​​NOTE: The console or terminal should return with the following information exactly as depicted:

      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes