如何压缩或限制 MySQL ibdata1 数据文件

Question 1

我建议将 InnoDB 表存储在单独的文件中，每个表一个文件。这样可以更轻松地管理它们并查看哪些表占用了最多的存储空间。将以下内容放入/etc/mysql/my.cnf（或者它在你的系统上的任何地方）[mysqld]部分：

innodb_file_per_table

之后，很容易识别出占用过多空间的表。然后您需要定期（每天一次、每月一次等）重新创建有问题的表，具体方法如下：

ALTER TABLE tablename ENGINE=InnoDB;

在大型表上运行该操作需要相当长的时间。它将从头开始重新创建表文件，并消除增加文件大小的碎片和漏洞。在 ALTER TABLE 运行时，它使用的磁盘容量是所需磁盘容量的两倍。除非您以“每个表一个文件”模式运行，否则我不确定您是否可以执行此操作。

更新

我想补充一点，摆脱现有的“所有表的单一文件”InnoDBibdata1文件，您需要执行以下操作：

使用 mysqldump 导出所有 InnoDB 表
关闭mysqld
将“innodb_file_per_table”行放入 my.cnf 中
rm ibdata* ib_logfile* 在你的mysql数据目录中
启动 mysqld
导入您在步骤 1 中备份的数据

Answer

我建议将 InnoDB 表存储在单独的文件中，每个表一个文件。这样可以更轻松地管理它们并查看哪些表占用了最多的存储空间。将以下内容放入/etc/mysql/my.cnf（或者它在你的系统上的任何地方）[mysqld]部分：

innodb_file_per_table

之后，很容易识别出占用过多空间的表。然后您需要定期（每天一次、每月一次等）重新创建有问题的表，具体方法如下：

ALTER TABLE tablename ENGINE=InnoDB;

在大型表上运行该操作需要相当长的时间。它将从头开始重新创建表文件，并消除增加文件大小的碎片和漏洞。在 ALTER TABLE 运行时，它使用的磁盘容量是所需磁盘容量的两倍。除非您以“每个表一个文件”模式运行，否则我不确定您是否可以执行此操作。

更新

我想补充一点，摆脱现有的“所有表的单一文件”InnoDBibdata1文件，您需要执行以下操作：

使用 mysqldump 导出所有 InnoDB 表
关闭mysqld
将“innodb_file_per_table”行放入 my.cnf 中
rm ibdata* ib_logfile* 在你的mysql数据目录中
启动 mysqld
导入您在步骤 1 中备份的数据

Question 2

要对特定表启用压缩，您必须：

设置innodb_file_per_tableON
设置innodb_file_formatBarracuda
重新启动 MySQL 并重新创建表空间。
使用更改表定义ALTER TABLE name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Barracuda支持的行格式MySQL 5.5或者带有 InnoDB 插件的 MySQL 5.1。

Answer

要对特定表启用压缩，您必须：

设置innodb_file_per_tableON
设置innodb_file_formatBarracuda
重新启动 MySQL 并重新创建表空间。
使用更改表定义ALTER TABLE name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Barracuda支持的行格式MySQL 5.5或者带有 InnoDB 插件的 MySQL 5.1。

Question 3

压缩 ibdata1 非常重要有一个特定的原因。

ibdata1 中有什么？有四件事：

表数据
表索引
表元数据
MVCC（多版本并发控制）数据

你需要做两件事：

通过将数据和索引从 ibdata1 中分离出来进行清理（我在2010 年 10 月 29 日和2011 年 2 月 4 日）类似于@snap的答案，但由于@snap 在这个问题中首先提到了这个方法，他应该得到赞扬（他的回答被接受）。
从 innodb_data_file_path 中删除 32G 上限。它应该显示为“ibdata1:10M:autoextend”（无论如何，这是默认值）

警告

innodb_data_file_path 应为默认值的原因与存储在 ibdata1 中的内容有关。即使您删除了 @snap 指定的所有数据和索引，表元数据和 MVCC 数据仍会写入 ibdata1。如果 DDL 命令（CREATE TABLE、DROP TABLE、ALTER TABLE 等）数量非常大，表元数据仍会使 ibdata1 增长。如果有大量事务，并且每个事务都会对大量数据进行快照，MVCC 数据仍会使 ibdata1 增长。既然 ibdata1 就是这种情况，那么它最好尽可能精简。因此，在 /etc/my.cnf 中让 innodb_data_file_path=ibdata1:10M:autoextend

顺便说一句：我为@snap +1！！！

Answer