We have no audit records in our internal database (audit_detail and audit_main are showing a count of 0).
The ibdata file is set to an initial size of 100M with autoextend max size of 15776M.
The ibdata file is currently only 104M in size.
The Audit Archiver is running and it keeps coming up with a message that the Database is at 96%.
I have restarted both the SSG and MYSQLD services but this is still coming up with this error.
How do I get this to reset?
Release : 10.x 11.x
Component : API GATEWAY
Changes in how mysql defragments things to recover space from mysql 5.x to mysql 8
Mysql 8 is different from the older version, optimize is not used anymore.
First set FOREIGN KEY 0
Truncate each AUDIT tables - 0 rows
Set FOREIGN KEY 1
Then analyze each Audit table
Commands tested to recover space mysql DB
Review the current size:
mysql> SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as size FROM information_schema.TABLES WHERE table_schema = "ssg" ORDER BY size DESC;
+--------------------------------------+-------+
| Table | size |
+--------------------------------------+-------+
| audit_main | 79.30 |
| audit_admin | 26.67 |
.
To truncate the audit tables :
SET FOREIGN_KEY_CHECKS = 0;
truncate table audit_admin;
truncate table audit_detail;
truncate table audit_detail_params;
truncate table audit_main;
truncate table audit_message;
truncate table audit_system;
SET FOREIGN_KEY_CHECKS = 1;
analyze table ssg.audit_admin;
analyze table ssg.audit_detail;
analyze table ssg.audit_detail_params;
analyze table ssg.audit_main;
analyze table ssg.audit_message;
analyze table ssg.audit_system;
Review new size
mysql> SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as size FROM information_schema.TABLES WHERE table_schema = "ssg" ORDER BY size DESC;
+--------------------------------------+-------+
| Table | size |
+--------------------------------------+-------+
| audit_admin | 4.55 |
| audit_main | 0.06 |
.
.