We recently did a migration from MySQL 5.5 to MySQL 5.7, on two different servers. For the migration, we used a master-slave replication. The old DB (5.5) was set as master, the new one as slave (5.7).
Everything went well and the migration was successful. The DB is large, about 600G of data.
The problem we have now is that on the new DB, ther is one large .ibd file of 82G. Let's asume the DB name is my_database. This file is located under: /var/lib/mysql/my_database:
-rw-r----- 1 mysql mysql 82G Apr 20 10:28 articles.ibd
The articles table is big, but why there is such large ibd file on the new MySQL server, and was not there on the old one.
As we have now some storage limits (SSD), we would like to somehow delete this file. How to do it and why this file is created there on MySQL5.7?
As far as we checked, this is due to the https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html, but is it safe disable this option with SET GLOBAL innodb_file_per_table = OFF; remove the .ibd files and restart MySQL?
As while doing replication innodb_file_per_table was enable so it created .ibd per table, if you disable per_table then .ibd file per table are not created and data is stored in single file named ibdata1 , but as you have already replicated and data has been created you cant delete the single .ibd file, doing this will delete all data in the table
Its recommended that you create a fresh copy of replication disabling per_table or you can delete/archive some old data from tables and later optimize tables. which will regain/reduce size of .ibd file and save some disk size
You can try taking backup using Percona Xtrabackup tool if you dont want to dump or replicate mysql again
Was able to find a solution, it is about disabling the innodb_file_per_table with SET GLOBAL innodb_file_per_table = OFF; or setting it up in my.cnf file and then remove the ibd files by running:
Credits goes to this answer: https://stackoverflow.com/a/49939925/1165041