MySQL数据文件不会缩小 [英] MySQL data file won't shrink

问题描述

随着时间的推移，我用于MySQL数据库的ibdata1文件增长到了约32GB.最近，我从数据库中删除了大约10GB的数据(出于很好的考虑，重新启动了mysql)，但该文件不会缩小.有什么办法可以减少这个文件的大小

My ibdata1 file for MySQL database grew to about 32GB over time. Recently I deleted about 10GB of data from my databases (and restarted mysql for good measure), but the file won't shrink. Is there any way to reduce the size of this file

推荐答案

无论您删除多少数据，InnoDB表空间的文件大小永远不会自动减小.

The file size of InnoDB tablespaces will never reduce automatically, no matter how much data you delete.

您可以做的是，尽管要付出很多努力，但要通过设置为每个表创建一个表空间

What you could do, although it is a lot of effort, is to create one tablespace for every table by setting

innodb_file_per_table

关于这一点的很长的篇幅是，您需要从mysql服务器中导出所有数据(设置新服务器会更容易)，然后重新导入数据.您会发现有很多名为tablename.ibd的文件，它们仅保存一个表的数据，而不是一个包含每个表的数据的单个ibdata1文件.

The long part about this is, that you need to export ALL DATA from the mysql server (setting up a new server would be easier) and then reimport the data. Instead of one single ibdata1 file which holds the data for each and every table, you will find a lot of files called tablename.ibd which hold the data only for one single table.

之后:

然后从表中删除大量数据时，可以让mysql通过发出命令来重新创建数据文件

When you then delete a lot of data from tables, you can let mysql recreate the data-file by issuing

alter table <tablename> engine=myisam;

切换到MyIsam(并删除该表的InnoDB数据文件)，然后

to switch to MyIsam (and have the InnoDB data file for this table deleted) and then

alter table <tablename> engine=innodb;

重新创建表.

这篇关于MySQL数据文件不会缩小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！