tablespace - MySQL从5.5升级到5.7后innodb_file_per_table默认开启问题

Question

最近将数据库从库从5.5升级到5.7,发现储存MySQL磁盘空间一下子占用变大了很多，排查了一下发现是升级后MySQL的系统表空间(ibdata1)很大[原本就很大],单个innodb表的表空间也变得很大[升级后变大]，正常情况下应该只有一方会很大(系统的MyISAM表不多)

具体情况如下:
升级前，mysql数据库5.5，innodb_file_per_table使用默认配置，即是关闭的，innodb的表和索引都存储在一起(ibdata1文件很大)；
升级到5.7时，innodb_file_per_table仍使用默认配置，但是默认是开启的，升级时使用mysql_upgrade后，每个表使用了独立的表空间，即每个数据库文件夹下的表空间文件很大，但是原本的系统表空间ibdata1并没有对应的变小。

查询手册发现：
在mysql5.5的时候innodb_file_per_table配置是默认关闭的，
但是数据库升级到5.7的时候，innodb_file_per_table配置默认开启了(其实在mysql5.6.6的时候就默认开启了)，
官网说明：http://dev.mysql.com/doc/refm...

请问有没有方法在不重新导入数据的情况下(重新导入耗时太长)，使原本的系统表空间ibdata1文件变小。

Answer 1

First you need to know what you want to do

1. MySQL was upgraded from 5.5 to 5.7
2. I don’t want to re-import the data and reduce the ibdata1 space

If your data is in a production environment, it is recommended that you do compatibility work before upgrading the database, fully evaluate and find incompatibilities or problem points, and solve them. When upgrading the version in the production environment, it is recommended to import and export the database, and it is not recommended to upgrade in place.

If it is a test environment, you can do whatever you want, even after executing mysql_upgrade, close MySQL, delete the ibdata1 file, and restart to see the effect.

Or use some table sorting commands or try to modify the table storage engine to sort some tables and see if it has any effect.