mysql - 如何 : Clean a mysql InnoDB storage engine?

Question

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

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

Answer 1

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

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

表数据

表索引

MVCC (Multiversioning Concurrency Control)数据

回滚段

撤消空间

表元数据(数据字典)

双写入缓冲区(后台写入以防止依赖操作系统缓存)

插入缓冲区(管理对非唯一二级索引的更改)

见 Pictorial Representation of ibdata1

InnoDB 架构



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

可以运行吗 OPTIMIZE TABLE ?

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

使表的数据和索引在 ibdata1 内连续

使 ibdata1增长是因为连续的数据和索引页是 附加 至 ibdata1

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

可以运行吗 OPTIMIZE TABLE 与 innodb_file_per_table ?

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

好消息 : 运行时 OPTIMIZE TABLE 与 innodb_file_per_table 启用，这将产生 .ibd该表的文件。例如，如果您有表 mydb.mytable数据目录为 /var/lib/mysql ，它将产生以下结果:

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

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

.ibd将包含该表的数据页和索引页。太好了。

坏消息 : 您所做的就是提取 mydb.mytable 的数据页和索引页。从住在 ibdata .每个表的数据字典条目，包括 mydb.mytable ，仍然保留在数据字典中(参见 Pictorial Representation of ibdata1)。 您不能只是简单地删除 ibdata1在这一点上!!! 请注意 ibdata1根本没有缩小。

InnoDB 基础设施清理

缩水 ibdata1您必须一劳永逸地执行以下操作:

转储(例如，使用 mysqldump)所有数据库到 .sql文本文件(以下使用 SQLData.sql)

删除所有数据库(除了 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 (或 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 上不可用)

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

启动 MySQL(这将重新创建 ibdata1 [默认 10MB] 和 ib_logfile0 和 ib_logfile1，每个 1G)。

进口 SQLData.sql

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

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

mytable.frm (存储引擎标题)

mytable.ibd (表数据和索引)

与 innodb_file_per_table /etc/my.cnf 中的选项，您可以运行 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 步并继续

更新 2013-06-04 11:13 EDT

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

返回 July 03, 2006 , Percona 有一篇不错的文章 why to choose a proper innodb_log_file_size .后来，在 Nov 21, 2008 , Percona 在 how to calculate the proper size based on peak workload keeping one hour's worth of changes 上跟进了另一篇文章.

从那以后，我在 DBA StackExchange 中写了一些关于计算日志大小的文章，并在那里我引用了这两篇 Percona 文章。

Aug 27, 2012 :Proper tuning for 30GB InnoDB table on server with 48GB RAM

Jan 17, 2013 :MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

就个人而言，我仍然会采用 25% 的规则进行初始设置。然后，由于可以在生产中随着时间的推移更准确地确定工作负载， you could resize the logs在短短几分钟的维护周期中。