I have one MySQL table using the InnoDB storage engine; it contains about 2M data rows. When I deleted data rows from the table, it did not release allocated disk space. Nor did the size of the ibdata1 file reduce after running the optimize table command.
我有一个使用InnoDB存储引擎的MySQL表;它包含大约2M数据行。当我从表中删除数据行时，它并没有释放分配的磁盘空间。在运行了优化表命令之后，ibdata1文件的大小也没有减少。
Is there any way to reclaim disk space from MySQL?
有没有办法从MySQL中回收磁盘空间?
I am in a bad situation; this application is running in about 50 different locations and now problem of low disk space is appearing at almost all of them.
我的处境很糟糕;这个应用程序在大约50个不同的位置运行，现在几乎所有的位置都出现了低磁盘空间的问题。
5 个解决方案
#1
121
MySQL doesn't reduce the size of ibdata1. Ever. Even if you use optimize table to free the space used from deleted records, it will reuse it later.
MySQL没有减少ibdata1的大小。永远。即使使用优化表从已删除的记录中释放所使用的空间，它也将在以后重用它。
An alternative is to configure the server to use innodb_file_per_table, but this will require a backup, drop database and restore. The positive side is that the .ibd file for the table is reduced after an optimize table.
另一种方法是将服务器配置为使用innodb_file_per_table，但这将需要备份、删除数据库和恢复。积极的一面是，在优化表之后，表的.ibd文件减少了。
#2
30
Just had the same problem myself.
我自己也有同样的问题。
What happens is, that even if you drop the database, innodb will still not release disk space. I had to export, stop mysql, remove the files manually, start mysql, create database and users, and then import. Thank god I only had 200MB worth of rows, but it spared 250GB of innodb file.
发生的情况是，即使删除数据库，innodb仍然不会释放磁盘空间。我必须导出，停止mysql，手动删除文件，启动mysql，创建数据库和用户，然后导入。感谢上帝，我只有200MB的行，但是它保留了250GB的innodb文件。
Fail by design.
失败的设计。
#3
20
If you don't use innodb_file_per_table, reclaiming disk space is possible, but quite tedious, and requires a significant amount of downtime.
如果不使用innodb_file_per_table，回收磁盘空间是可能的，但是非常繁琐，并且需要大量的停机时间。
The How To is pretty in-depth - but I pasted the relevant part below.
How To是相当深入的——但是我粘贴了下面的相关部分。
Be sure to also retain a copy of your schema in your dump.
一定要在转储中保留模式的副本。
Currently, you cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:
目前，无法从系统表空间中删除数据文件。要减小系统表空间大小，请使用以下步骤:
Use mysqldump to dump all your InnoDB tables.
使用mysqldump转储所有InnoDB表。
Stop the server.
停止服务器。
Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
删除所有现有的表空间文件，包括ibdata和ib_log文件。如果您希望保留信息的备份副本，那么在删除MySQL安装中的文件之前，请将所有ib*文件复制到另一个位置。
Remove any .frm files for InnoDB tables.
删除InnoDB表的任何.frm文件。
Configure a new tablespace.
配置一个新的表空间。
Restart the server.
重新启动服务器。
Import the dump files.
导入转储文件。
#4
0
Other way to solve the problem of space reclaiming is, Create multiple partitions within table - Range based, Value based partitions and just drop/truncate the partition to reclaim the space, which will release the space used by whole data stored in the particular partition.
另一种解决空间回收问题的方法是，在基于表范围、基于值的分区中创建多个分区，然后删除/截断该分区以回收空间，从而释放存储在特定分区中的整个数据所使用的空间。
There will be some changes needed in table schema when you introduce the partitioning for your table like - Unique Keys, Indexes to include partition column etc.
当您为您的表引入分区时，比如—惟一键、包含分区列的索引等，表模式将需要一些更改。
#5
-1
There are several ways to reclaim diskspace after deleting data from table for MySQL Inodb engine
在为MySQL Inodb引擎从表中删除数据之后，有几种方法可以回收磁盘空间
If you don't use innodb_file_per_table from the beginning, dumping all data, delete all file, recreate database and import data again is only way ( check answers of FlipMcF above )
如果您从一开始就不使用innodb_file_per_table，那么转储所有数据、删除所有文件、重新创建数据库和再次导入数据是唯一的方法(请查看FlipMcF的答案)
If you are using innodb_file_per_table, you may try
如果您使用innodb_file_per_table，您可以尝试。
- If you can delete all data truncate command will delete data and reclaim diskspace for you.
- 如果您可以删除所有的数据，截断命令将删除数据并为您回收磁盘空间。
- Alter table command will drop and recreate table so it can reclaim diskspace. Therefore after delete data, run alter table that change nothing to release hardisk ( ie: table TBL_A has charset uf8, after delete data run ALTER TABLE TBL_A charset utf8 -> this command change nothing from your table but It makes mysql recreate your table and regain diskspace
- Alter table命令将删除并重新创建表，以便它可以回收磁盘空间。因此，在删除数据之后，运行不更改任何内容的alter table来释放hardisk(即:table TBL_A有charset uf8，在删除数据后运行alter table TBL_A charset utf8 ->这个命令不会从表中更改任何内容，但会使mysql重新创建表并重新获取磁盘空间
- Create TBL_B like TBL_A . Insert select data you want to keep from TBL_A into TBL_B. Drop TBL_A, and rename TBL_B to TBL_A. This way is very effective if TBL_A and data that needed to delete is big (delete command in MySQL innodb is very bad performance)
- 创建TBL_B，如TBL_A。将要从TBL_A保存的select数据插入到TBL_B中。删除TBL_A，并将TBL_B重命名为TBL_A。如果TBL_A和需要删除的数据很大，这种方法非常有效(MySQL innodb中的delete命令性能非常差)
#1
121
MySQL doesn't reduce the size of ibdata1. Ever. Even if you use optimize table to free the space used from deleted records, it will reuse it later.
MySQL没有减少ibdata1的大小。永远。即使使用优化表从已删除的记录中释放所使用的空间，它也将在以后重用它。
An alternative is to configure the server to use innodb_file_per_table, but this will require a backup, drop database and restore. The positive side is that the .ibd file for the table is reduced after an optimize table.
另一种方法是将服务器配置为使用innodb_file_per_table，但这将需要备份、删除数据库和恢复。积极的一面是，在优化表之后，表的.ibd文件减少了。
#2
30
Just had the same problem myself.
我自己也有同样的问题。
What happens is, that even if you drop the database, innodb will still not release disk space. I had to export, stop mysql, remove the files manually, start mysql, create database and users, and then import. Thank god I only had 200MB worth of rows, but it spared 250GB of innodb file.
发生的情况是，即使删除数据库，innodb仍然不会释放磁盘空间。我必须导出，停止mysql，手动删除文件，启动mysql，创建数据库和用户，然后导入。感谢上帝，我只有200MB的行，但是它保留了250GB的innodb文件。
Fail by design.
失败的设计。
#3
20
If you don't use innodb_file_per_table, reclaiming disk space is possible, but quite tedious, and requires a significant amount of downtime.
如果不使用innodb_file_per_table，回收磁盘空间是可能的，但是非常繁琐，并且需要大量的停机时间。
The How To is pretty in-depth - but I pasted the relevant part below.
How To是相当深入的——但是我粘贴了下面的相关部分。
Be sure to also retain a copy of your schema in your dump.
一定要在转储中保留模式的副本。
Currently, you cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:
目前，无法从系统表空间中删除数据文件。要减小系统表空间大小，请使用以下步骤:
Use mysqldump to dump all your InnoDB tables.
使用mysqldump转储所有InnoDB表。
Stop the server.
停止服务器。
Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
删除所有现有的表空间文件，包括ibdata和ib_log文件。如果您希望保留信息的备份副本，那么在删除MySQL安装中的文件之前，请将所有ib*文件复制到另一个位置。
Remove any .frm files for InnoDB tables.
删除InnoDB表的任何.frm文件。
Configure a new tablespace.
配置一个新的表空间。
Restart the server.
重新启动服务器。
Import the dump files.
导入转储文件。
#4
0
Other way to solve the problem of space reclaiming is, Create multiple partitions within table - Range based, Value based partitions and just drop/truncate the partition to reclaim the space, which will release the space used by whole data stored in the particular partition.
另一种解决空间回收问题的方法是，在基于表范围、基于值的分区中创建多个分区，然后删除/截断该分区以回收空间，从而释放存储在特定分区中的整个数据所使用的空间。
There will be some changes needed in table schema when you introduce the partitioning for your table like - Unique Keys, Indexes to include partition column etc.
当您为您的表引入分区时，比如—惟一键、包含分区列的索引等，表模式将需要一些更改。
#5
-1
There are several ways to reclaim diskspace after deleting data from table for MySQL Inodb engine
在为MySQL Inodb引擎从表中删除数据之后，有几种方法可以回收磁盘空间
If you don't use innodb_file_per_table from the beginning, dumping all data, delete all file, recreate database and import data again is only way ( check answers of FlipMcF above )
如果您从一开始就不使用innodb_file_per_table，那么转储所有数据、删除所有文件、重新创建数据库和再次导入数据是唯一的方法(请查看FlipMcF的答案)
If you are using innodb_file_per_table, you may try
如果您使用innodb_file_per_table，您可以尝试。
- If you can delete all data truncate command will delete data and reclaim diskspace for you.
- 如果您可以删除所有的数据，截断命令将删除数据并为您回收磁盘空间。
- Alter table command will drop and recreate table so it can reclaim diskspace. Therefore after delete data, run alter table that change nothing to release hardisk ( ie: table TBL_A has charset uf8, after delete data run ALTER TABLE TBL_A charset utf8 -> this command change nothing from your table but It makes mysql recreate your table and regain diskspace
- Alter table命令将删除并重新创建表，以便它可以回收磁盘空间。因此，在删除数据之后，运行不更改任何内容的alter table来释放hardisk(即:table TBL_A有charset uf8，在删除数据后运行alter table TBL_A charset utf8 ->这个命令不会从表中更改任何内容，但会使mysql重新创建表并重新获取磁盘空间
- Create TBL_B like TBL_A . Insert select data you want to keep from TBL_A into TBL_B. Drop TBL_A, and rename TBL_B to TBL_A. This way is very effective if TBL_A and data that needed to delete is big (delete command in MySQL innodb is very bad performance)
- 创建TBL_B，如TBL_A。将要从TBL_A保存的select数据插入到TBL_B中。删除TBL_A，并将TBL_B重命名为TBL_A。如果TBL_A和需要删除的数据很大，这种方法非常有效(MySQL innodb中的delete命令性能非常差)