如何缩小/清除 MySQL 中的 ibdata1 文件 [英] How to shrink/purge ibdata1 file in MySQL

问题描述

我在localhost中使用MySQL作为在R中执行统计的查询工具"，即每次运行R脚本时，我创建一个新数据库(A)，创建一个新表(B)，导入数据到 B，提交查询以获得我需要的，然后我删除 B 并删除 A.

I am using MySQL in localhost as a "query tool" for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit a query to get what I need, and then I drop B and drop A.

它对我来说很好用，但我意识到 ibdata 文件的大小正在迅速增加，我没有在 MySQL 中存储任何内容，但是 ibdata1 文件已经超过 100 MB.

It's working fine for me, but I realize that the ibdata file size is increasing rapidly, I stored nothing in MySQL, but the ibdata1 file already exceeded 100 MB.

我在设置中或多或少使用默认的 MySQL 设置，有没有办法在固定的时间段后自动缩小/清除 ibdata1 文件?

I am using more or less default MySQL setting for the setup, is there a way for I can automatically shrink/purge the ibdata1 file after a fixed period of time?

推荐答案

ibdata1 不收缩是 MySQL 一个特别令人讨厌的特性.ibdata1 文件实际上无法缩小，除非您删除所有数据库、删除文件并重新加载转储.

That ibdata1 isn't shrinking is a particularly annoying feature of MySQL. The ibdata1 file can't actually be shrunk unless you delete all databases, remove the files and reload a dump.

但是您可以配置 MySQL，以便将每个表(包括其索引)存储为单独的文件.这样 ibdata1 就不会变得那么大.根据 Bill Karwin 的评论，这是默认启用的从 MySQL 5.6.6 版本开始.

But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large. According to Bill Karwin's comment this is enabled by default as of version 5.6.6 of MySQL.

不久前我就这样做了.但是，要设置您的服务器为每个表使用单独的文件，您需要更改 my.cnf 以启用此功能:

It was a while ago I did this. However, to setup your server to use separate files for each table you need to change my.cnf in order to enable this:

[mysqld]
innodb_file_per_table=1

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html

当您想从 ibdata1 中回收空间时，您实际上必须删除该文件:

As you want to reclaim the space from ibdata1 you actually have to delete the file:

1. 对所有数据库、过程、触发器等进行mysqldumpmysql 和performance_schema 数据库莉>
2. 删除所有数据库除了以上两个数据库
3. 停止mysql
4. 删除ibdata1和ib_log文件
5. 启动mysql
6. 从转储中恢复

当您在第 5 步中启动 MySQL 时，将重新创建 ibdata1 和 ib_log 文件.

When you start MySQL in step 5 the ibdata1 and ib_log files will be recreated.

现在你可以走了.创建用于分析的新数据库时，表将位于单独的 ibd* 文件中，而不是位于 ibdata1 中.由于您通常很快就会删除数据库，ibd* 文件将被删除.

Now you're fit to go. When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1. As you usually drop the database soon after, the ibd* files will be deleted.

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

您可能已经看到了:
http://bugs.mysql.com/bug.php?id=1341

通过使用命令 ALTER TABLE ;ENGINE=innodb 或 OPTIMIZE TABLE 可以从 ibdata1 中提取数据和索引页到单独的文件.但是，除非您执行上述步骤，否则 ibdata1 不会收缩.

By using the command ALTER TABLE <tablename> ENGINE=innodb or OPTIMIZE TABLE <tablename> one can extract data and index pages from ibdata1 to separate files. However, ibdata1 will not shrink unless you do the steps above.

关于information_schema，没有必要也不可能删除.它实际上只是一堆只读视图，而不是表格.并且没有与它们关联的文件，甚至没有数据库目录.informations_schema 正在使用内存数据库引擎，并在 mysqld 停止/重新启动时删除并重新生成.请参阅 https://dev.mysql.com/doc/refman/5.7/en/information-schema.html.

Regarding the information_schema, that is not necessary nor possible to drop. It is in fact just a bunch of read-only views, not tables. And there are no files associated with the them, not even a database directory. The informations_schema is using the memory db-engine and is dropped and regenerated upon stop/restart of mysqld. See https://dev.mysql.com/doc/refman/5.7/en/information-schema.html.

这篇关于如何缩小/清除 MySQL 中的 ibdata1 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！