How to Free Disk Space From MySQL ibdata1

MySQL uses several files for journaling transactions, among these files there is the ibdata1 file.

Due to the way MySQL stores its data, this file can grow very large and unfortunately cannot simply be removed without risking corruption and/or data loss.

How to Find the Root Cause

The most likely cause is one or more queries that take a very long time to complete. These queries run transactions which stay open as long as the query itself. While that transaction is running, all changes compared to the original table are kept inside the ibdata1 file.

The MySQL slow logs can be found under /var/log/mysql/mysql-slow.log.

Alternatively, a summary report can be created using the pt-query-digest utility:

pt-query-digest /var/log/mysql/mysql-slow.log

How to Reclaim the Disk Space

Before reclaiming the disk space it’s important that the long running queries have been resolved/stopped!

The only safe way to free up the disk space taken by ibdata1 is a procedure only Hypernode support can perform, as this procedure requires root privileges. Furthermore this procedure can only be performed on MySQL 5.6 and 5.7 at the moment.

This procedure includes:

dumping/backing up all databases
stopping MySQL
removing the MySQL data directory
recreating the empty skeleton databases
restarting MySQL
re-importing the databases from the backup

As mentioned in this Stack Exchange article there sadly is no other solution than the aforementioned procedure.

If you’re encountering this problem on a Hypernode, please contact support@hypernode.com so we can help you reclaim your disk space!