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

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

但是您可以配置 MySQL，以便将每个表（包括其索引）存储为单独的文件。这样 ibdata1 就不会增长得那么大。根据 Bill Karwin's comment，从 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 回收空间时，您实际上必须删除该文件：

对除 mysql 和 performance_schema 数据库之外的所有数据库、过程、触发器等进行 mysqldump 删除除上述 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

您可能已经看到了：
http://bugs.mysql.com/bug.php?id=1341

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

关于information_schema，没有必要也不可能丢弃。它实际上只是一堆只读视图，而不是表。并且没有与它们关联的文件，甚至没有数据库目录。 informations_schema 正在使用内存数据库引擎，并在 mysqld 停止/重新启动时被删除并重新生成。请参阅https://dev.mysql.com/doc/refman/5.7/en/information-schema.html。

---

添加到 John P's answer，

对于 linux 系统，步骤 1-6 可以使用以下命令完成：

mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql mysqladmin -u [username] -p[root_password] drop [database_name] sudo /etc/init.d/mysqld stop sudo rm /var/lib/ mysql/ibdata1 sudo rm /var/lib/mysql/ib_logfile* sudo /etc/init.d/mysqld start mysqladmin -u [username] -p[root_password] create [database_name] mysql -u [username] -p[root_password] [数据库名称] <转储文件名.sql

警告：如果您在此 mysql 实例上有其他数据库，这些说明将导致您丢失其他数据库。确保修改步骤 1,2 和 6,7 以涵盖您希望保留的所有数据库。

---

当您删除 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 文件的需要。

有关多个表空间的更多信息：

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

---

在 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 存储引擎用于（某些）您的 MySQL 表，您可能已经遇到了默认配置的问题。您可能已经注意到，在 MySQL 的数据目录（在 Debian/Ubuntu – /var/lib/mysql）中有一个名为“ibdata1”的文件。它包含 MySQL 实例的几乎所有 InnoDB 数据（它不是事务日志），并且可能会变得非常大。默认情况下，该文件的初始大小为 10Mb，并且会自动扩展。不幸的是，InnoDB 数据文件的设计是不能收缩的。这就是为什么 DELETE、TRUNCATE、DROP 等不会回收文件使用的空间的原因。

我认为您可以在那里找到很好的解释和解决方案：

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

---

如果您的目标是监控 MySQL 可用空间并且您无法阻止 MySQL 缩小您的 ibdata 文件，那么通过表状态命令获取它。例子：

MySQL > 5.1.24：

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

MySQL < 5.1.24：

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

然后将此值与您的 ibdata 文件进行比较：

du -b ibdata1

来源：http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

---

在新版本的 mysql-server 上面的食谱中会粉碎“mysql”数据库。在旧版本中它可以工作。在新的一些表切换到表类型 INNODB，这样做会损坏它们。最简单的方法是：

转储所有数据库

卸载mysql服务器，

添加仍然是my.cnf：

    [mysqld]
    innodb_file_per_table=1

擦除 /var/lib/mysql 中的所有内容

安装mysql服务器

恢复用户和数据库

---

如前所述，您不能缩小 ibdata1 （为此，您需要转储和重建），但通常也没有真正的需要。

使用自动扩展（可能是最常见的大小设置）ibdata1 预分配存储，每次几乎满时都会增长。由于空间已经分配，这使得写入速度更快。

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

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

---

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

在阅读了有关该主题的 Percona's blog post 之后，我在 MariaDB 10.6 中启用了截断 UNDO LOG 条目的功能，这些条目填充了 ibdata1 的 95%，并且在完全删除和恢复之后，从那一刻起，我的 ibdata1再也没有长大。

使用默认的 innodb_undo_log_truncate = 0 我的 ibdata1 轻松达到 10% 的数据库空间占用，也就是几十 GB。

使用 innodb_undo_log_truncate = 1、ibdata1，它的大小固定为 76 Mb。

---