MySQL uses several files for journaling transactions, among these files there is the
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 are 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
The MySQL slow logs can be found under
Alternatively, a summary report can be created using the
Dutch speaking customers also have the option to monitor the long running queries in the “MySQL slow queries” section on their Service Panel. You can sort them by query time to see the longest queries at the top.
How to reclaim the disk space
Before reclaiming the disk space it is 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.
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 upper mentioned procedure.
If you are encountering this problem on a Hypernode please follow the steps above and after please contact firstname.lastname@example.org so we can help you reclaim your disk space!