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
Question:
How do I truncate tables and reduce the size of the database in API Portal 3.5?
Environment:
API Portal 3.5.x
Answer:
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 " 
SET FOREIGN_KEY_CHECKS = 0; 
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; 
SET FOREIGN_KEY_CHECKS = 1;" 


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