What I think is the reason for this issue
Recently, I was trying to reduce the memory usage by disabling the performance_schema (I read a tutorial that disabling it will reduce the memory usage.)
So, I basically added these lines to /etc/mysql/my.cnf.
performance_schema = 0 key_buffer_size = 8M max_connections = 30 # Limit connections thread_stack = 128K
Memory usage was reduced. Everything was fine. Until I noticed my droplet was down due to 100% Disk usage (I eventually got it down to around 95% by clearing some logs, etc.)
What happened as a result
Long story short, I found that /var/lib/mysql/mysql.ibd was taking up like 8 GB and the undo_001 in the same directory, like 2 GB which is not normal for the small amount of database and size I have.
I read a lot about how to resolve this issue. And according to many articles and discussions, this file is not shrinkable, and deleting it will result in things to break. As per those articles I read, the only way is to drop all tables, create a new namespace or something, and reload all the tables again. All of these are beyond my understanding.
So, I wanted to know if you have any ideas/guides on how to solve this issue without having to go through all those work? Is there any simple solution to reclaim this space and solve this issue?
(I have found similar questions on here, but all of them are talking about the "specific tables or databases" inside folders. But I am facing this issue on the "mysql.ibd" file itself. The individual databases seem to have very less size. The innodb_file_per_table is turned ON.)