How to Shrink the IBdata File in MySQL in 9.2 and Lower

Products

• CA API Gateway

Issue/Introduction

The API Gateway uses MySQL with the InnoDB engine to store configuration data, audits, and published service policies. This implementation uses a single database tablespace object (the ibdata file) to store all binary data within the database. The MySQL server is configured to prevent this file from exceeding a certain size. If the file must grow to store more data but cannot grow due to the upper limit then the ibdata file will need to be shrunk and certain data within the database must be discarded. This article prescribes removing all audit data from the database as audit records tend to consume the most storage space in the Gateway database.

Environment

This article only refers to API Gateway appliances running version 9.2 and lower.

• In Gateway version 9.3, MySQL was upgraded to version 5.7 which utilizes the ibdata "files per table" feature. That feature actually removes the need to shrink the ibdata file as each table has it's own ibdata file and is dynamic in sizing. Therefore, if the Gateway is an appliance running version 9.3 and newer with MySQL 5.7, do not follow this article as it does not apply to version 9.3 and newer when running with MySQL 5.7.

Resolution

This procedure consists of several smaller steps (Note: For a single node cluster, only step 2 and 3 is required):

1. Blocking MySQL traffic between appliances
2. Purging existing audit data
3. Shrinking the existing ibdata file
4. Unblocking MySQL traffic
5. Reinitializing replication

1. Block MySQL Traffic

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 using the commands below
   
   • The value of "SECONDARY-IP" below 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

4. Log onto the secondary Gateway database node as the ssgconfig user
5. Select Option 3: Use a privileged shell (root)
6. Close the port used for MySQL for all connections originating from the primary Gateway database node using the commands below
   
   • The value of "PRIMARY-IP" below 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

2. Purge Existing Audit Data

1. Stop the Gateway service on each Gateway database node: service ssg stop
2. Back up the existing primary Gateway database: mysqldump --all-databases | gzip > /root/ibdata-full-backup.sql.gz
3. Access the primary Gateway database via the MySQL prompt: mysql ssg
4. 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;
     exit

5. Back up the primary Gateway database: mysqldump --all-databases | gzip > /root/ibdata-shrink-backup.sql.gz

3. Shrink the ibdata File

1. Stop the MySQL service on the primary Gateway: service mysql stop
2. Remove the existing ibdata files: rm -rf /var/lib/mysql/ib*
3. Start the MySQL service on the primary Gateway: service mysql start
4. Extract the contents of the primary Gateway database backup:gzip -d /root/ibdata-shrink-backup.sql.gz
5. Restore the primary Gateway database backup: mysql < /root/ibdata-shrink-backup.sql
6. Start the Gateway service on the primary Gateway: service ssg start
7. Repeat the this section on the secondary database node.

 

4. 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:

   • 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:

   • 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

 

5. Reinitialize Replication

1. Reinitialize replication on the secondary node: /opt/SecureSpan/Appliance/bin/create_slave.sh
   
   • 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
   
   • 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"

• The console or terminal should return with the following information exactly as depicted
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

Additional Information

In Gateway version 9.3, MySQL was upgraded to version 5.7 which utilizes the ibdata "files per table" feature. That feature actually removes the need to shrink the ibdata file as each table has it's own ibdata file and is dynamic in sizing. Therefore no article such as this exists for 9.3 or higher.