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

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

解决方案

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

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

• 表格数据
• 表索引
• ?

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

  • 使表的数据和索引在 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 文件.例如，如果你有一个数据目录为 /var/lib/mysql 的表 mydb.mytable，它会产生以下内容:

  • /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 文本文件(SQLData.sql 在下面使用)

  2. 删除所有数据库(mysql 和 information_schema 除外)CAVEAT :作为预防措施，请运行此脚本以确保绝对确保您拥有所有用户授权:

     mkdir/var/lib/mysql_grantscp/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(或 Windows 上的 my.ini)

     [mysqld]innodb_file_per_tableinnodb_flush_method=O_DIRECTinnodb_log_file_size=1Ginnodb_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/mysqlcp/var/lib/mysql_grants/*/var/lib/mysql/mysqlchown -R mysql:mysql/var/lib/mysql/mysql

  返回第 6 步并继续

  更新 2013-06-04 11:13 EDT

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

  回到 2006 年 7 月 3 日，Percona 发表了一篇不错的文章 为什么选择合适的 innodb_log_file_size.后来，在 2008 年 11 月 21 日，Percona 跟进了另一篇关于 如何根据保持一小时变化的峰值工作负载计算合适的大小.

  此后我在 DBA StackExchange 中写了一些关于计算日志大小的帖子，以及我在何处引用了这两篇 Percona 文章.

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

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

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

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

  解决方案

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

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

  • 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

  InnoDB Architecture

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

  Can I run OPTIMIZE TABLE ?

  Unfortunately, running OPTIMIZE TABLE against an InnoDB table stored in the shared table-space file ibdata1 does two things:

  • Makes the table’s data and indexes contiguous inside ibdata1
  • Makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1

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

  Can I run OPTIMIZE TABLE with innodb_file_per_table ?

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

  Good News : When you run OPTIMIZE TABLE with innodb_file_per_table enabled, this will produce a .ibd file for that table. For example, if you have table mydb.mytable witha datadir of /var/lib/mysql, it will produce the following:

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

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

  Bad News : All you have done is extract the Data Pages and Index Pages of mydb.mytable from 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 ibdata1 AT THIS POINT !!! Please note that ibdata1 has not shrunk at all.

  InnoDB Infrastructure Cleanup

  To shrink ibdata1 once and for all you must do the following:

  1. Dump (e.g., with mysqldump) all databases into a .sql text file (SQLData.sql is used below)

  2. Drop all databases (except for mysql and 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_logfile0 and ib_logfile1)

  4. Shutdown MySQL

  5. Add the following lines to /etc/my.cnf (or my.ini on 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_size is 25% of innodb_buffer_pool_size.

     Also: innodb_flush_method=O_DIRECT is 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_logfile0 and ib_logfile1 at 1G each).

  8. Import SQLData.sql

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

  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:

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

  With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

  I have done this many times in my career as a MySQL DBA. In fact, the first time I did this, I shrank a 50GB ibdata1 file down to only 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 mysql schema begin dropped, look back at Step 2. You made the physical copy of the mysql schema. You can restore it as follows:

  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

  UPDATE 2013-06-04 11:13 EDT

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

  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.

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

  • 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?

  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 logs during a maintenance cycle in just minutes.