logo标签列表

如何清理MySQL InnoDB存储引擎？

mysqlinnodb
138

如何清理 MySQL InnoDB 存储引擎，使其不再存储已删除表的数据？

或者说，我每次都需要重新构建一个全新的数据库吗？

你为什么认为MySQL会存储已删除表的数据呢？ - Robert Munteanu
2
如果我删除了许多巨大的表，我的InnoDB存储文件并不会缩小。 - 700 Software
2
@RobertMunteanu：请查看http://bugs.mysql.com/bug.php?id=1341。 - Max
4个回答
373

以下内容涉及 InnoDB。虽然需要花费一些时间，但这个过程非常重要。

请注意，/var/lib/mysql/ibdata1 是 InnoDB 基础设施中最繁忙的文件。它通常包含六种类型的信息：

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

InnoDB 架构

InnoDB 架构

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

我可以运行 OPTIMIZE TABLE 吗？

不幸的是，对于存储在共享表空间文件 ibdata1 中的 InnoDB 表运行 OPTIMIZE TABLE 会导致两个问题：

  • 使表的数据和索引在 ibdata1 中变得连续
  • 让 ibdata1 增长，因为连续的数据和索引页面被附加到 ibdata1 中

不过，您可以将表数据和表索引与 ibdata1 分离并独立管理。

我可以使用 OPTIMIZE TABLE 和innodb_file_per_table 吗？

假设你将 innodb_file_per_table 添加到 /etc/my.cnf (my.ini) 中。然后，您可以对所有 InnoDB 表运行 OPTIMIZE TABLE 吗？

好消息：启用 innodb_file_per_table 的情况下运行 OPTIMIZE 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. 将所有数据库转储为一个 .sql 文本文件（下面使用 SQLData.sql）（例如，使用 mysqldump）

  2. 删除所有数据库（除了 mysql 和 information_schema）注意: 作为预防措施，请运行此脚本以确保您完全拥有所有用户授权：

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

  • 登录到mysql并运行SET GLOBAL innodb_fast_shutdown = 0; (这将彻底清空ib_logfile0和ib_logfile1中剩余的事务更改)

  • 关闭MySQL

  • 在/etc/my.cnf(或Windows上的my.ini)中添加以下行

    • [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 上不可用。

  • 删除 ibdata* 和 ib_logfile*。可选地，您可以删除 /var/lib/mysql 中的所有文件夹，除了 /var/lib/mysql/mysql。

  • 启动 MySQL（这将重新创建默认大小为 10MB 的 ibdata1，并且大小分别为 1G 的 ib_logfile0 和 ib_logfile1）。

  • 导入 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

    返回 Step 6 并继续操作。

    更新 2013-06-04 11:13 EDT

    关于在第5步中将innodb_log_file_size设置为innodb_buffer_pool_size的25％，这个教条式的规则已经过时了。

    早在2006年7月3日，Percona就发表了一篇很好的文章介绍如何选择适当的 innodb_log_file_size。之后，在2008年11月21日，Percona又撰写了另一篇文章，介绍如何根据峰值工作量计算适当的大小，保留一小时的更改记录。

    此后，我在DBA StackExchange上编写了多篇有关计算日志大小的文章，并引用了这两篇Percona文章。

    个人而言，我仍然会在初始设置中使用25％的规则。然后，在生产环境中随着工作量可以更准确地确定，您可以在维护周期中在几分钟内调整日志大小。

    9
    我也很成功地使用了innodb_file_per_table选项，在一个服务器上有200个数据库，每个数据库有200个表。我能够将不同的数据库符号链接到不同的分区上，因此使用了更多的IO缓冲区和磁盘驱动器，否则这些资源是不可用的 :) - Dave Rix
    3
    顺便提醒一下 @SeanDowney ，如有必要，请增加 innodb_open_tables 的值。默认值为 300。 - RolandoMySQLDBA
    4
    在32位系统中，innodb_buffer_pool_size设置为4Gb是不被允许的。MySQL会无声地禁用InnoDB，并将表更改为MyISAM。请使用略小一些的值来解决此问题。 - David
    2
    MySQL 性能博客指出，“将日志文件大小设置为缓冲池大小的一部分”这一常见建议是错误的。http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ - mak
    5
    天啊，我只想说这可能是我在S.O上看过的最好的答案之一。做得非常好，先生。当我导入一个154GB的数据库时遇到ERROR 2013 (HY000)错误时，您帮助我找到了解决方案。感谢您提供的出色答案！ - Josh Brown
    显示剩余24条评论
    5
    InnoDB引擎不会存储已删除的数据。在插入和删除行时，InnoDB存储文件中会留下未使用的空间。随着时间的推移，总体空间不会减少，但是“已删除并释放”的空间将自动被数据库服务器重用。
    您可以通过手动重新组织表来进一步调整和管理引擎使用的空间。为此，请使用mysqldump转储受影响的表中的数据，删除表，重新启动mysql服务，然后从转储文件中重新创建表。
    2

    我按照这个指南进行完整的重置（以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
    1
    你好！如果问题已经在SO中得到解答，你可以将该问题标记为重复。 - Kuro Neko
    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接