mysql - 如何:清理mysql InnoDB存储引擎？

Question

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

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

Answer 1

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

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


表数据
表索引
MVCC (Multiversioning Concurrency Control)数据


回滚段
撤消空间

表元数据（数据字典）
双重写入缓冲区（后台写入，以防止依赖于OS缓存）
插入缓冲区（管理对非唯一二级索引的更改）
请参见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)。然后可以在所有InnoDB表上运行OPTIMIZE TABLE吗？

好消息：在启用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之外）CAVEAT：为慎重起见，请运行此脚本以确保完全拥有所有用户授权：

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（这将重新创建 ibdata1 [默认为10MB]，并分别以1G重新创建 ib_logfile0和 ib_logfile1）。
导入 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。您可以按以下方式还原它：

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

关于在第5步中将 innodb_log_file_size设置为 innodb_buffer_pool_size的25％的原则，这是相当老套的规则。

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

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


Nov 21, 2008： Proper tuning for 30GB InnoDB table on server with 48GB RAM
Aug 27, 2012： MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?


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