如何压缩/清除ibdata1文件在MySQL [英] 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版本。

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

http://dev.mysql.com/doc /refman/5.5/en/innodb-multiple-tablespaces.html

由于您要从 ibdata1 code>您实际上必须删除该文件：

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

1. 执行 mysqldump mysql 和 performance_schema 数据库 之外的所有数据库，过程， li>
   
2. 删除所有数据库（除了上述2个数据库）


3. 停止mysql


4. code> ibdata1 和 ib_log 文件


5. 启动mysql


6. 从转储中恢复

1. Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
2. Drop all databases except the above 2 databases
3. Stop mysql
4. Delete ibdata1 and ib_log files
5. Start mysql
6. Restore from dump

当您在步骤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

You have probably seen this:
http://bugs.mysql.com/bug.php?id=1341

使用命令 ALTER TABLE< tablename> ENGINE = innodb 或 OPTIMIZE TABLE< tablename> 可以从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.

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