O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required