How to decrease disk space occupied by /var/lib/mysql?

Tablespaces do not give space back to the OS. Deleted rows simply leave free space in the tablespace.

If the tables were generated while innodb_file_per_table was ON, then do SHOW TABLE STATUS to see which ones have more than a few MB of “Data_free”. For those, do OPTIMIZE TABLE. (Start with the smallest).

If all the tables were built with OFF, you are in a bad situation. The standard fix is to take a full dump, clear the disk (consider upgrading mysql at the same time), and reload everything. This time have innodb_file_per_table=ON before starting.

1 Like