如何：清理 mysql InnoDB 存储引擎？

Howto: Clean a mysql InnoDB storage engine?

提问by Bryan Field

Is it possible to clean a mysql innodb storage engine so it is not storing data from deleted tables?

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

Or do I have to rebuild a fresh database every time?

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

回答by RolandoMySQLDBA

Here is a more complete answer with regard to InnoDB. It is a bit of a lengthy process, but can be worth the effort.

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

Keep in mind that /var/lib/mysql/ibdata1is the busiest file in the InnoDB infrastructure. It normally houses six types of information:

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

• Table Data
• Table Indexes
• MVCC (Multiversioning Concurrency Control)Data
  • Rollback Segments
  • Undo Space
• Table Metadata (Data Dictionary)
• Double Write Buffer (background writing to prevent reliance on OS caching)
• Insert Buffer (managing changes to non-unique secondary indexes)
• See the Pictorial Representation of ibdata1

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

InnoDB Architecture

InnoDB 架构

Many people create multiple ibdatafiles hoping for better disk-space management and performance, however that belief is mistaken.

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

Can I run OPTIMIZE TABLE?

我能跑OPTIMIZE TABLE吗？

Unfortunately, running OPTIMIZE TABLEagainst an InnoDB table stored in the shared table-space file ibdata1does two things:

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

• Makes the table's data and indexes contiguous inside ibdata1
• Makes ibdata1grow because the contiguous data and index pages are appendedto ibdata1

• 使表的数据和索引在内部连续 ibdata1
• 使得ibdata1成长，因为连续的数据和索引页被追加到ibdata1

You can however, segregate Table Data and Table Indexes from ibdata1and manage them independently.

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

Can I run OPTIMIZE TABLEwith innodb_file_per_table?

我可以运行OPTIMIZE TABLE使用innodb_file_per_table？

Suppose you were to add innodb_file_per_tableto /etc/my.cnf (my.ini). Can you then just run OPTIMIZE TABLEon all the InnoDB Tables?

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

Good News: When you run OPTIMIZE TABLEwith innodb_file_per_tableenabled, this will produce a .ibdfile for that table. For example, if you have table mydb.mytablewitha datadir of /var/lib/mysql, it will produce the following:

好消息：当您在启用的情况下运行OPTIMIZE TABLE时innodb_file_per_table，这将.ibd为该表生成一个文件。例如，如果您有一个mydb.mytabledatadir 为 的表/var/lib/mysql，它将产生以下内容：

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

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

The .ibdwill contain the Data Pages and Index Pages for that table. Great.

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

Bad News: All you have done is extract the Data Pages and Index Pages of mydb.mytablefrom living in ibdata. The data dictionary entry for every table, including mydb.mytable, still remains in the data dictionary (See the Pictorial Representation of ibdata1). YOU CANNOT JUST SIMPLY DELETE ibdata1AT THIS POINT !!!Please note that ibdata1has not shrunk at all.

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

InnoDB Infrastructure Cleanup

InnoDB 基础设施清理

To shrink ibdata1once and for all you must do the following:

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

1. Dump (e.g., with mysqldump) all databases into a .sqltext file (SQLData.sqlis used below)

2. Drop all databases (except for mysqland information_schema) CAVEAT: As a precaution, please run this script to make absolutely sure you have all user grants in place:

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

3. Login to mysql and run SET GLOBAL innodb_fast_shutdown = 0;(This will completely flush all remaining transactional changes from ib_logfile0and ib_logfile1)

4. Shutdown MySQL

5. Add the following lines to /etc/my.cnf(or my.inion Windows)

   [mysqld]
   innodb_file_per_table
   innodb_flush_method=O_DIRECT
   innodb_log_file_size=1G
   innodb_buffer_pool_size=4G
   

   (Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_sizeis 25% of innodb_buffer_pool_size.

   Also: innodb_flush_method=O_DIRECTis not available on Windows)

6. Delete ibdata*and ib_logfile*, Optionally, you can remove all folders in /var/lib/mysql, except /var/lib/mysql/mysql.

7. Start MySQL (This will recreate ibdata1[10MB by default] and ib_logfile0and ib_logfile1at 1G each).

8. Import SQLData.sql

1. 转储（例如，使用mysqldump）所有数据库到一个.sql文本文件中（SQLData.sql下面使用）

2. 删除所有数据库（除了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
   

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.

   另外：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

Now, ibdata1will still grow but only contain table metadata because each InnoDB table will exist outside of ibdata1. ibdata1will no longer contain InnoDB data and indexes for other tables.

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

For example, suppose you have an InnoDB table named mydb.mytable. If you look in /var/lib/mysql/mydb, you will see two files representing the table:

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

• mytable.frm(Storage Engine Header)
• mytable.ibd(Table Data and Indexes)

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

With the innodb_file_per_tableoption in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytableand the file /var/lib/mysql/mydb/mytable.ibdwill actually shrink.

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

I have done this many times in my career as a MySQL DBA. In fact, the first time I did this, I shrank a 50GBibdata1file down to only 500MB!

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

Give it a try. If you have further questions on this, just ask. Trust me; this will work in the short term as well as over the long haul.

试一试。如果您对此有更多疑问，请提问。相信我; 这将在短期和长期发挥作用。

CAVEAT

警告

At Step 6, if mysql cannot restart because of the mysqlschema begin dropped, look back at Step 2. You made the physical copy of the mysqlschema. You can restore it as follows:

在第 6 步，如果 mysql 由于mysqlschema begin 被删除而无法重新启动，请回顾第 2 步。您制作了mysqlschema的物理副本。您可以按如下方式恢复它：

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

Go back to Step 6 and continue

返回第 6 步并继续

UPDATE 2013-06-04 11:13 EDT

更新 2013-06-04 11:13 EDT

With regard to setting innodb_log_file_sizeto 25% of innodb_buffer_pool_sizein Step 5, that's blanket rule is rather old school.

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

Back on July 03, 2006, Percona had a nice article why to choose a proper innodb_log_file_size. Later, on Nov 21, 2008, Percona followed up with another article on how to calculate the proper size based on peak workload keeping one hour's worth of changes.

回到上July 03, 2006，Percona的有一个很好的文章为什么要选择合适的innodb_log_file_size。后来，Nov 21, 2008Percona 又发表了另一篇关于如何根据保持一小时变化的峰值工作负载计算适当大小的文章。

I have since written posts in the DBA StackExchange about calculating the log size and where I referenced those two Percona articles.

从那以后，我在 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?

• Aug 27, 2012：在具有 48GB RAM 的服务器上正确调整 30GB InnoDB 表
• Jan 17, 2013: MySQL 5.5 - Innodb - innodb_log_file_size 大于 4GB？

Personally, I would still go with the 25% rule for an initial setup. Then, as the workload can more accurate be determined over time in production, you could resize the logsduring a maintenance cycle in just minutes.

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

回答by bigjeff

The InnoDB engine does not store deleted data. As you insert and delete rows, unused space is left allocated within the InnoDB storage files. Over time, the overall space will not decrease, but over time the 'deleted and freed' space will be automatically reused by the DB server.

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

You can further tune and manage the space used by the engine through an manual re-org of the tables. To do this, dump the data in the affected tables using mysqldump, drop the tables, restart the mysql service, and then recreate the tables from the dump files.

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