Have you dropped some larger MySQL table with a few GB, but the disk space has not been released? Are you wondering why?
You are probably running a MySQL server version less than 5.6 and the deleted table was InnoDB type. You’ve also found the ibdata1, file, which occupied a large amount of data on the disk. The size of this file is unlikely to change at all, no matter how much data you delete from the table or add into it. For example, you may have a problem if you are running MySQL server on a FAT32 file system where one file can have a maximum size of 4GB, so you can easily hit the roof when the ibdata1 file can no longer grow.
What is the issue then?
By default up to version 5.6, all InnoDB tables with data and indexes are stored in one common file, already mentioned ibdata1, as opposed to MyISAM tables, which are stored in separate files. Therefore, after deleting the data from the database, this file will not be reduced, the removed data section will only be internally marked as free, but disk space will not be freed. New data is then written to this “free space”. Therefore, by deleting/inserting the data, the size of the file is mostly unchanged, only the content is “recycled”.
How to solve it?
Simply. By configuring MySQL, you need to make sure that the InnoDB tables do not use one common ibdata1 file for their data, but that each table is stored in a separate .ibd file like the MyISAM tables are stored separately. Since MySQL 5.6 his is set up automatically, for older versions this change must be done manually.
Add a line to the [mysqld] section of the MySQL configuration file:
After restarting the MySQL server, a separate .ibd file is created for each InnoDB table, where the contents of these tables are automatically flipped so that table data, including their indexes, will no longer be stored in ibdata1, but in their files, which also has the advantage when a file is damaged, you lose a maximum of one table. However, if all InnoDB databases/tables are stored in this one ibdata1 file, you will lose all of the tables stored in it if it is corrupted.
However, this configuration change will only result in a separate file for each InnoDB table. This will not reduce the size of the ibdata1 file. It will remain the same as before the change, the data in it will be marked as free, but will not be physically reduced from the disk.
To free up the disk space occupied by ibdata1 in addition to adding the innodb_file_per_table to the configuration file, you need to take additional steps before changing the configuration.
How to decrease ibdata1?
- First, it is necessary to back up all databases, at least those using InnoDB.
- Drop all the databases (except for the “mysql” database) – just those that use InnoDB should be enough.
- Stop MySQL server.
- Add innodb_file_per_table to the configuration file..
- On the disk, delete the ibdata1 file, as well as all ib_logfile.
- Start MySQL server.
- Retrieve the databases from the backup.
After these actions, when a MySQL server is started, it creates a new, default blank size ibdata1 file (a few MB). By importing the backup, all InnoDB tables will be stored in their separate .ibd files, and ibdata1 will not increase. So if you drop a table, then it will also remove its .ibd file on the disk.
Do you suggest another solution? Is there a mistake in the article? Write to comment.