如何缩小/清除 MySQL 中的 ibdata1 文件
我在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:
- 对所有数据库、过程、触发器等进行
mysqldumpmysql和performance_schema数据库莉> - 删除所有数据库除了以上两个数据库
- 停止mysql
- 删除
ibdata1和ib_log文件 - 启动mysql
- 从转储中恢复
当您在第 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 或 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.
相关文章