Howto：清理 mysql InnoDB 存储引擎？

Question

是否可以清理 mysql innodb 存储引擎，使其不存储已删除表中的数据？

或者我每次都必须重建一个新的数据库？

Answer 1

这是关于 InnoDB 的更完整的答案。这是一个有点漫长的过程，但值得付出努力。

请记住，/var/lib/mysql/ibdata1 是 InnoDB 基础架构中最繁忙的文件。它通常包含六种类型的信息：

• 表数据
• 表索引
• MVCC（多版本并发控制） 数据
  • 回滚段
  • 撤消空格
• 表元数据（数据字典）
• 双写缓冲区（后台写入以防止依赖操作系统缓存）
• 插入缓冲区（管理对非唯一二级索引的更改）
• 请参阅 ibdata1 的图示

InnoDB 架构

许多人创建多个 ibdata 文件，希望获得更好的磁盘空间管理和性能，但是这种信念是错误的。

我可以运行 OPTIMIZE TABLE ？

不幸的是，运行 OPTIMIZE TABLE 针对存储在共享表空间文件 ibdata1 中的 InnoDB 表执行两件事：

• 表的数据和索引在 ibdata1 内连续
• 使 code>ibdata1 会增长，因为连续的数据和索引页附加到 ibdata1

但是，您可以将表数据和表索引与 ibdata1 分离code> 并独立管理它们。

我可以运行 OPTIMIZE TABLE 与 <代码>innodb_file_per_table？

假设您要添加 innodb_file_per_table 到 /etc/my.cnf (my.ini)。然后你可以运行 OPTIMIZE TABLE 在所有 InnoDB 表上？

好消息：当您运行使用 rel="noreferrer">innodb_file_per_table 启用，这将为该表生成一个 .ibd 文件。例如，如果表 mydb.mytable 的数据目录为 /var/lib/mysql，它将生成以下内容：

• /var/lib/mysql/ mydb/mytable.frm
• /var/lib/mysql/mydb/mytable.ibd

.ibd 将包含该表的数据页和索引页。伟大的。

坏消息：您所做的只是从 ibdata 中提取 mydb.mytable 的数据页和索引页。每个表的数据字典条目（包括mydb.mytable）仍然保留在数据字典中（请参阅ibdata1 的图形表示）。 此时您不能简单地删除 ibdata1！！！请注意，ibdata1 根本没有缩小。

InnoDB 基础设施清理

要一劳永逸地缩小 ibdata1，您必须执行以下操作：

1. 将所有数据库转储（例如，使用 mysqldump）到 .sql< /code> 文本文件（下面使用SQLData.sql）

2. 删除所有数据库（mysql 和information_schema 除外）CAVEAT< /strong> ：作为预防措施，请运行此脚本以绝对确保您拥有所有用户授权：

   mkdir /var/lib/mysql_grants
   cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/。
   chown -R mysql:mysql /var/lib/mysql_grants
   

3. 登录 mysql 并运行SET GLOBAL innodb_fast_shutdown = 0;（这将完全刷新ib_logfile0中的所有剩余事务更改和 ib_logfile1)

4. 关闭 MySQL

5. 将以下行添加到 /etc/my.cnf （或 my.ini 在 Windows 上）

   <前><代码>[mysqld]
   innodb_file_per_table
   innodb_flush_method=O_DIRECT
   innodb_log_file_size=1G
   innodb_buffer_pool_size=4G

   （旁注：无论您设置什么 innodb_buffer_pool_size，请确保 innodb_log_file_size 是 innodb_buffer_pool_size 的 25%。

   另外：innodb_flush_method=O_DIRECT 在 Windows 上不可用）

6. 删除 ibdata* 和 ib_logfile*，或者，您可以删除 中的所有文件夹/var/lib/mysql，/var/lib/mysql/mysql 除外。

7. 启动 MySQL（这将重新创建 ibdata1 [默认情况下为 10MB] 以及 ib_logfile0 和 ib_logfile1，各 1G）。

8. 导入SQLData.sql

现在，ibdata1仍然会增长，但只包含表元数据，因为每个InnoDB表将存在于 ibdata1 之外。 ibdata1 将不再包含其他表的 InnoDB 数据和索引。

例如，假设您有一个名为 mydb.mytable 的 InnoDB 表。如果您查看/var/lib/mysql/mydb，您将看到代表该表的两个文件：

• mytable.frm（存储引擎标头）
• mytable.ibd （表数据和索引）

使用/etc/my.cnf中的innodb_file_per_table选项，您可以运行OPTIMIZE TABLE mydb.mytable 文件 /var/lib/mysql/mydb/mytable.ibd 实际上会缩小。

在我作为 MySQL DBA 的职业生涯中，我曾多次这样做过。事实上，我第一次这样做时，我将 50GB ibdata1 文件缩小到只有 500MB！

尝试一下。如果您对此还有其他疑问，请询问。相信我；这无论是在短期还是长期都将发挥作用。

警告

在步骤 6 中，如果 mysql 由于 mysql 架构开始被删除而无法重新启动，请返回步骤 2。您创建了 mysql 架构的物理副本。您可以按如下方式恢复：

mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql

返回步骤 6 并继续

UPDATE 2013-06-04 11:13 EDT

关于设置 innodb_log_file_size 为 innodb_buffer_pool_size 在第 5 步中，这是相当老派的一揽子规则。

早在2006年7月3日，Percona 有一篇不错的文章 为什么要选择合适的innodb_log_file_size。后来，2008 年 11 月 21 日，Percona 又发表了另一篇关于如何根据峰值工作负载保持一小时的更改来计算适当的大小。

此后，我在 DBA StackExchange 中撰写了有关计算日志大小的帖子，并在其中引用了这两篇 Percona 文章。

• 2012 年 8 月 27 日 : 在 48GB RAM 的服务器上正确调整 30GB InnoDB 表
• < code>2013 年 1 月 17 日 : MySQL 5.5 - Innodb - innodb_log_file_size 总和大于 4GB？

就个人而言，我仍然会采用 25% 规则进行初始设置。然后，由于在生产中随着时间的推移可以更准确地确定工作负载，您可以调整日志大小< /a> 在维护周期内仅需几分钟。

Answer 2

InnoDB引擎不存储已删除的数据。当您插入和删除行时，未使用的空间将在 InnoDB 存储文件中分配。随着时间的推移，总体空间不会减少，但随着时间的推移，“删除和释放”的空间将被数据库服务器自动重用。

您可以通过手动重新组织表来进一步调整和管理引擎使用的空间。为此，请使用 mysqldump 转储受影响表中的数据，删除这些表，重新启动 mysql 服务，然后从转储文件重新创建表。

Answer 3

我按照本指南进行完全重置（以 root 身份）：

mysqldump --all-databases --single-transaction | gzip -c > /tmp/mysql.all.sql.gz
service mysql stop
mv /var/lib/mysql /var/lib/mysql.old; mkdir -m700 /var/lib/mysql; chown mysql:mysql /var/lib/mysql

mysql_install_db                # mysql 5.5
mysqld --initialize-insecure    # mysql 5.7

service mysql start
zcat /tmp/mysql.all.sql.gz | mysql
service mysql restart

Answer 4

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

看看我的答案 如何收缩/清除MySQL 中的 ibdata1 文件。