删除表&之后的MySQL中的可用空间列? [英] Free space in MySQL after deleting tables & columns?

问题描述

我有大约20GB的数据库.我需要删除5个表&在其他3个表中删除几列.

I have a database of around 20GB. I need to delete 5 tables & drop a few columns in some other 3 tables.

删除5个表以释放大约3 GB的空间，并在其他表中删除列将释放另外8GB的空间.

Dropping 5 tables with free some 3 GB and dropping columns in other tables should free another 8GB.

如何从MySQL回收此空间.

How do I reclaim this space from MySQL.

我已经读过转储数据库并将其还原为解决方案之一，但我不确定该如何工作，甚至不确定这是否仅适用于删除整个数据库或仅删除部分数据库?

I've read dumping the database and restoring it back as one of the solution but I'm not really sure how that works, I am not even sure if this only works for deleting the entire database or just parts of it?

请提出解决方法.谢谢.

Please suggest how to go about this. THanks.

推荐答案

从注释中听起来，您正在使用InnoDB，但没有每表文件"选项.

From the comments, it sounds like you're using InnoDB without the file per table option.

在这种模式下，通常不可能从innodb表空间中回收空间.您唯一的做法是转储整个数据库，打开每表文件模式，然后重新加载(使用完全干净的mysql实例).对于大型数据库，这将花费很长时间. mk-parallel-dump和restore工具可能会更快一些，但是仍然需要一段时间.确保首先在非生产服务器上测试此过程.

Reclaiming space from the innodb tablespace is not generally possible in this mode. Your only course of action is to dump the whole database, turn on file-per-table mode, and reload it (with a completely clean mysql instance). This is going to take a long time with a large database; mk-parallel-dump and restore tools might be a bit quicker, but it will still take a while. Be sure to test this process on a non-production server first.

这篇关于删除表&之后的MySQL中的可用空间列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！