mysql收缩空间的问题

Question

今天转移数据库（从一个数据库到另一个数据库），转移了一部分之后发现硬盘快满了，于是就把就旧的数据库里已经转移完成的表删除了。
我是先TRUNCATE table_name，发现空间没有释放，又DROP TABLE table_name，发现空间还是没有释放。

我去网上搜了一下，发现收缩空间的命令都是针对表的，比如OPTIMIZE TABLE table_name。
我想请问一下，还有别的办法收缩空间吗？

Answer 1

The characteristics of the InnoDB engine are like this.
The files occupying space are bin-log file (binary log) and ibdata1 (data file, table space is here)
There are also some other logs (ib_logfile0) redo-log and undo-log.

If you execute TRUNCATE to delete data, it will not affect (reduce) the size of the above files, but will increase. You must record your TRUNCATE operation. Even if the data is deleted, it will not be deleted in the ibdata1 space, because to delete the data inside, a large amount of disk space needs to be moved. Innodb only marks those data pages that can be used again, and does not delete this space. Usually, once mysql is started, it will occupy several G of ibdata1 and ibdata2. Allocate a large disk first and then use it slowly so that the data is physically continuous (high efficiency). Avoid allocating disk space all the time and creating a lot of fragmentation.

Unless you clear the entire data directory (equivalent to reinstalling mysql) and then re-import the data.

Disks are very cheap, just install a few large disks. There is no disk, so what kind of database can I use?

Answer 2

I don’t understand. But if buying a hard drive can solve the problem, there is no need to think about other solutions. The overall cost of investment is higher, and the occurrence of this situation itself shows that you really need to consider disk space issues, such as disk arrays and data tables. Split, whether redis/memcached is needed to help the database reduce pressure, etc. This is a more important thing.

Answer 3

Recommend, I have encountered it before, and later dealt with it, and wrote a blog http://astarblog.cn/index.php/2016/04/27/339.html (Is this considered an advertisement?)