MySQL 8 Cookbook

Shrinking the InnoDB system tablespace

If you are not using innodb_file_per_table, then all of the table data is stored in system tablespace. If you drop a table, the space is not reclaimed. You can shrink the system tablespace and reclaim the disk space. This requires a major downtime, so it is recommended to perform the task on a slave by taking it out of rotation and then promoting it to master.

You can check the free space by querying the INFORMATION_SCHEMA tables:

mysql> SELECT SUM(data_free)/1024/1024 FROM INFORMATION_SCHEMA.TABLES;+--------------------------+| sum(data_free)/1024/1024 |+--------------------------+|               6.00000000 |+--------------------------+1 row in set (0.00 sec)

1. Stop the writes to the database. If it is a master, ...