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, ...

Get MySQL 8 Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.