如何在MySQL中收缩/清除ibdata1文件

小开

当你删除innodb表时，MySQL不会释放ibdata文件中的空间，这就是为什么它一直在增长。这些文件几乎从不缩小。

如何缩小现有的ibdata文件:

https://dev.mysql.com/doc/refman/5.6/en/innodb-system-tablespace.html#innodb-resize-system-tablespace

您可以编写脚本，并安排脚本在固定的时间后运行，但对于上面描述的设置，多个表空间似乎是更简单的解决方案。

如果使用配置选项innodb_file_per_table，则创建多个表空间。也就是说，MySQL为每个表创建单独的文件，而不是一个共享文件。这些单独的文件存储在数据库的目录中，当您删除该数据库时，它们将被删除。在您的情况下，这样就不需要收缩/清除ibdata文件了。

小开

最佳答案

ibdata1没有收缩是MySQL一个特别烦人的特性。ibdata1文件实际上无法收缩，除非删除所有数据库、删除文件并重新加载转储文件。

但是您可以配置MySQL，使每个表(包括其索引)存储为一个单独的文件。这样ibdata1就不会变大。根据比尔·卡尔文的评论, MySQL 5.6.6版本默认启用。

这是一段时间前的事了。然而，要设置你的服务器为每个表使用单独的文件，你需要改变my.cnf来启用:

[mysqld]
innodb_file_per_table=1

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html

当你想要从ibdata1中回收空间时，你实际上必须删除文件:

做一个mysqldump的所有数据库，过程，触发器等除了#EYZ1和performance_schema数据库
删除所有数据库除了以上2个数据库
停止mysql
删除ibdata1和ib_log文件
启动mysql
从转储恢复

当你在第5步启动MySQL时，ibdata1和ib_log文件将被重新创建。

现在你可以走了。当您创建一个用于分析的新数据库时，表将位于单独的ibd*文件中，而不是ibdata1文件中。由于您通常很快就会删除数据库，因此ibd*文件将被删除。

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

你可能见过这个:
# EYZ0 < / p >

通过使用命令ALTER TABLE <tablename> ENGINE=innodb或OPTIMIZE TABLE <tablename>，可以从ibdata1提取数据和索引页到单独的文件中。但是，除非执行上述步骤，否则ibdata1不会收缩。

对于information_schema，没有必要也不可能删除。它实际上只是一堆只读视图，而不是表。并且没有与它们相关的文件，甚至没有数据库目录。informations_schema正在使用内存db-engine，在停止/重新启动mysqld时删除并重新生成。看到# EYZ2。

小开

如前所述，您不能收缩ibdata1(为此需要转储和重新构建)，但通常也没有真正的必要。

使用autoextend(可能是最常见的大小设置)，ibdata1预分配存储空间，在存储空间接近满时增长。这使得写入速度更快，因为已经分配了空间。

当你删除数据时，它不会缩小，但文件内部的空间被标记为未使用。现在，当您插入新数据时，它将在进一步增长文件之前重用文件中的空白空间。

所以只有当你真的需要这些数据时，它才会继续增长。除非您确实需要为另一个应用程序使用空间，否则可能没有理由缩小它。

小开

如果您的目标是监视MySQL的空闲空间，而您又不能停止MySQL来缩小ibdata文件，那么可以通过表状态命令来获取它。例子:

MySQL > 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $20}'

MySQL & lt;5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $35}'

然后将这个值与ibdata文件进行比较:

du -b ibdata1

来源:# EYZ0

小开

如果你在MySQL表中使用InnoDB存储引擎，你可能已经遇到了默认配置的问题。你可能已经注意到在MySQL的数据目录(Debian/Ubuntu - /var/lib/ MySQL)中有一个名为“ibdata1”的文件。它保存了MySQL实例中几乎所有的InnoDB数据(它不是事务日志)，可能会变得相当大。默认情况下，该文件的初始大小为10Mb，并自动扩展。不幸的是，根据设计，InnoDB数据文件不能收缩。这就是为什么delete, TRUNCATEs, DROPs等不会回收文件所使用的空间。

我想你可以在那里找到很好的解释和解决方案:

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/ < a href = " http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/ " > < / >

小开

添加到约翰P的回答，

对于linux系统，步骤1-6可以用这些命令完成:

# EYZ0
# EYZ0
# EYZ0
# EYZ0
# EYZ0
# EYZ0

警告:如果你在这个mysql实例上有其他数据库，这些指令将导致你丢失其他数据库。确保修改步骤1、2和6、7以覆盖您希望保留的所有数据库。

小开

在一个新版本的mysql服务器食谱上面将粉碎“mysql”数据库。在旧版本中，它是有效的。在new中，一些表切换到INNODB类型，这样做会损坏它们。最简单的方法是:

转储所有数据库
卸载mysql服务器,
添加remain my.cnf:

    [mysqld]
innodb_file_per_table=1

删除/var/lib/mysql中的所有文件
安装mysql服务器
恢复用户和数据库

小开

快速脚本接受的答案的过程在bash:

#!/usr/bin/env bash
dbs=$(mysql -BNe 'show databases' | grep -vE '^mysql$|^(performance|information)_schema$')
mysqldump --events --triggers --databases $dbs > alldatabases.sql && \
echo "$dbs" | while read -r db; do
mysqladmin drop "$db"
done && \
mysql -e 'SET GLOBAL innodb_fast_shutdown = 0' && \
/etc/init.d/mysql stop && \
rm -f /var/lib/mysql/ib{data1,_logfile*} && \
/etc/init.d/mysql start && \
mysql < alldatabases.sql

保存为purge_binlogs.sh并以root运行。

不包括mysql， information_schema， performance_schema(和binlog目录)。

假设您在/root/.my.cnf中有管理员凭据，并且您的数据库位于默认的/var/lib/mysql目录中。

您也可以在运行此脚本后清除二进制日志，以重新获得更多的磁盘空间:

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

小开

似乎没有人提到innodb_undo_log_truncate设置可能产生的影响。

在阅读了Percona的博客文章主题之后，我在MariaDB 10.6中启用了填充了ibdata1 95%的UNDO LOG条目的截断，并且，在完全删除和恢复之后，从那一刻起，我的ibdata1再也没有增长过。

使用默认的innodb_undo_log_truncate = 0，我的ibdata1很容易达到数据库空间占用的10%，也就是数十gb。

对于innodb_undo_log_truncate = 1， ibdata1，它的稳定在76 Mb。