API Portal 3.5 database partition is full. How do I truncate tables and reduce size of database?

Document ID : KB000121431
Last Modified Date : 03/12/2018
Show Technical Document Details
How do I truncate tables and reduce the size of the database in API Portal 3.5?
API Portal 3.5.x
To remove all your metrics and analytics data from the lrsdata database. 
  1. Log into the API Portal and access the root prompt 
  2. Stop the API Portal: /opt/Deployments/lrs/server/bin/catalina.sh stop 
  3. Backup all databases instances by running the command: mysqldump --all-databases | gzip > /path-to/ibdata-first-backup.sql.gz
  4. Delete the data in the following tables by running the command: 
mysql lrsdata -e " 
truncate table message_context_mapping_keys; 
truncate table message_context_mapping_values; 
truncate table published_service; 
truncate table service_metrics; 
truncate table service_metrics_details; 

Note: All API usage data and analytics data will be deleted.

To shrink the ibdata file:
Similar to reducing the size of the ibdata file for the ssg database on a API Gateway
  1. Backup once again since truncating the lrsdata tables: mysqldump --all-databases | gzip > /path-to/ibdata-second-backup.sql.gz
  2. Stop the MySQL service on the Portal: service mysql stop
  3. Move or remove the existing ibdata files: rm -rf /var/lib/mysql/ib*
  4. Start the MySQL service on the primary Portal: service mysql start
  5. Extract the contents of the entire mysql database backup:gzip -d /path-to/ibdata-second-backup.sql.gz
  6. Restore the primary Portal database backup:mysql < /path-to/ibdata-second-backup.sql
  7. Start the API Portal: /opt/Deployments/lrs/server/bin/catalina.sh start