This article will discuss the topic of saving disk space from a large MySQL ibdata file after upgrade of an API Gateway appliance to 9.3 and newer. Please note this process is considered to be "at your own risk", as there can be side effects that need attention at a later time. In general, the ibdata file should not be removed after upgrading to MySQL 5.7 (included in Gateway 9.3 and later). The ideal scenario is to shrink the ibdata file before upgrading to Gateway 9.3 and later.
In a particular set of circumstances, there may be a large ibdata file consuming a lot of disk space in the /var/lib/mysql directory. After upgrading to 9.3 and newer, the MySQL database was upgraded from 5.5 to 5.7 (or newer) and the defaults changed from using the ibdata file to store the information of the database to the innodb_files_per_table being enabled which creates a directory for each database within the /var/lib/mysql directory and files within each subdirectory representing each table in the database. So for example, if using 'ssg' as the database name, there will be a directory created as /var/lib/mysql/ssg which holds all the files for the tables within the SSG database.
The benefit of this new innodb_files_per_table functionality is to allow for dynamic resizing which eliminates the need to frequently purge/maintain the ibdata file (since it always grew and never shrank on it's own) which was a difficult issue for administrators of API Gateway 9.2 and lower. Now with MySQL 5.7 and newer (on Gateway 9.3 and newer appliances), if a user purges the audit data, it will automatically reflect in the consumption of the disk in the /var/lib/mysql partition, as opposed to before when the ibdata file wouldn't shrink and would need to be purged at some point, causing downtime. Now, no more downtime.
However, if the database size was quite large before upgrading in-place to API Gateway 9.3 or newer, the ibdata file size will still remain quite large even after the upgrade, requiring a one-time purge to bring it back to it's default size of 100 MB. If there is a need to reduce this ibdata file size, please follow the steps in this article.
This article applies to API Gateway appliances, starting with version 9.3 and up to the most recent supported version. Specifically, this applies to situations where there was an in-place upgrade of the API Gateway from 9.2 and lower to 9.3 and higher.
Depending on the circumstances, the following KB article may need to be followed as well to resolve MySQL errors: https://knowledge.broadcom.com/external/article/137801/how-to-resolve-mysql-database-errors-on.html -- Particularly if the following error is seen (any table name can be mentioned though): [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.