如何在MySQL中缩小/清除ibdata1文件?

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.

我使用MySQL在localhost作为执行统计数据的“查询工具”在R,即R每次运行一个脚本,我创建一个新的数据库(a)、(B)创建一个新表,数据导入到B,提交一个查询来获得我所需要的东西,然后我滴水B和a。

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.

这对我来说很正常，但是我意识到ibdata文件的大小正在迅速增加，我在MySQL中没有存储任何东西，但是ibdata1文件已经超过了100mb。

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?

我正在使用或多或少的默认的MySQL设置为设置，是否有一种方法，我可以在一段固定的时间后自动收缩/清除ibdata1文件?

7 个解决方案

#1

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.

ibdata1并没有缩小，这是MySQL特别令人讨厌的特性。除非删除所有数据库、删除文件并重新加载转储文件，否则ibdata1文件实际上无法缩小。

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.

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

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:

刚才我做了这个。但是，为了使您的服务器能够为每个表使用单独的文件，您需要更改my.cnf，以便启用这个:

[mysqld]
innodb_file_per_table=1

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

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

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

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

1. Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
2. 除了mysql和performance ce_schema数据库之外，是否要对所有数据库、过程、触发器等进行mysqldump ?
3. Drop all databases except the above 2 databases
4. 删除除上述两个数据库之外的所有数据库。
5. Stop mysql
6. 停止mysql
7. Delete ibdata1 and ib_log files
8. 删除ibdata1和ib_log文件。
9. Start mysql
10. 启动mysql
11. Restore from dump
12. 恢复从转储

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

在步骤5中启动MySQL时，将重新创建ibdata1和ib_log文件。

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.

现在你该走了。当您创建一个用于分析的新数据库时，表将位于单独的ibd*文件中，而不是ibdata1中。当您通常在稍后删除数据库时，ibd*文件将被删除。

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

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

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

您可能已经见过这个:http://bugs.mysql.com/bug.php?

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.

通过使用命令ALTER TABLE ENGINE=innodb或优化表 ，可以从ibdata1提取数据和索引页，以分离文件。但是，除非您执行上述步骤，否则ibdata1不会收缩。

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.

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

#2

When you delete innodb tables, MySQL does not free the space inside the ibdata file, that's why it keeps growing. These files hardly ever shrink.

当您删除innodb表时，MySQL并没有释放ibdata文件中的空间，这就是为什么它一直在增长。这些文件几乎不会缩水。

How to shrink an existing ibdata file:

如何缩小现有的ibdata文件:

http://dev.mysql.com/doc/refman/5.5/en/innodb-resize-system-tablespace.html

http://dev.mysql.com/doc/refman/5.5/en/innodb-resize-system-tablespace.html

You can script this and schedule the script to run after a fixed period of time, but for the setup described above it seems that multiple tablespaces are an easier solution.

您可以编写脚本并将脚本安排在固定的时间之后运行，但是对于上面所描述的设置，似乎多个表空间是一个更简单的解决方案。

If you use the configuration option innodb_file_per_table, you create multiple tablespaces. That is, MySQL creates separate files for each table instead of one shared file. These separate files a stored in the directory of the database, and they are deleted when you delete this database. This should remove the need to shrink/purge ibdata files in your case.

如果使用配置选项innodb_file_per_table，则创建多个表空间。也就是说，MySQL为每个表创建单独的文件，而不是一个共享文件。这些单独的文件存储在数据库目录中，当您删除该数据库时，它们将被删除。这应该可以消除在您的情况下收缩/清除ibdata文件的需要。

More information about multiple tablespaces:

关于多个表空间的更多信息:

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

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

#3

Adding to John P's answer,

增加了约翰P的答案，

For a linux system, steps 1-6 can be accomplished with these commands:

对于linux系统，步骤1-6可以通过以下命令完成:

1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
2. mysqldump -u[用户名]-p[root_password] [database_name] > dumpfilename.sql。
3. DROP DATABASE database_name
4. 减少数据库database_name
5. sudo /etc/init.d/mysqld stop
6. sudo /etc/init.d / mysqld停止
7. sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile (and delete any other ib_logfile's that may be named ib_logfile0, ib_logfile1 etc...)
8. sudo rm /var/lib/mysql/ibdata1 sudo rm /var/lib/mysql/ib_logfile(并删除任何可能命名为ib_logfile0、ib_logfile1等的ib_logfile)
9. sudo /etc/init.d/mysqld start
10. sudo /etc/init.d / mysqld开始
11. create database [database_name]
12. 创建数据库(database_name)
13. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql
14. -u[用户名]-p[root_password] [database_name] < dumpfilename.sql。

Warning: these instructions will cause you to lose other databases if you have other databases on this mysql instance. Make sure that steps 1,2 and 6,7 are modified to cover all databases you wish to keep.

警告:如果在这个mysql实例上有其他数据库，这些说明将导致您丢失其他数据库。确保步骤1、2和6、7都被修改，以覆盖您希望保留的所有数据库。

#4

If you use the InnoDB storage engine for (some of) your MySQL tables, you’ve probably already came across a problem with its default configuration. As you may have noticed in your MySQL’s data directory (in Debian/Ubuntu – /var/lib/mysql) lies a file called ‘ibdata1′. It holds almost all the InnoDB data (it’s not a transaction log) of the MySQL instance and could get quite big. By default this file has a initial size of 10Mb and it automatically extends. Unfortunately, by design InnoDB data files cannot be shrinked. That’s why DELETEs, TRUNCATEs, DROPs, etc. will not reclaim the space used by the file.

如果您使用InnoDB存储引擎(一些)MySQL表，您可能已经遇到了它的默认配置的问题。正如你可能已经注意到在你的MySQL的数据目录(在Debian / Ubuntu - /var/lib/mysql)是一个文件名为“ibdata1′。它几乎包含了MySQL实例的所有InnoDB数据(它不是事务日志)，并且可能会变得非常大。默认情况下，该文件的初始大小为10Mb，并自动扩展。不幸的是，设计InnoDB数据文件不能被压缩。这就是为什么DELETEs、TRUNCATEs、drop等不会回收文件所使用的空间。

I think you can find good explanation and solution there :

我认为你可以找到很好的解释和解决方法:

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

#5

If your goal is to monitor MySQL free space and you can't stop MySQL to shrink your ibdata file, then get it through table status commands. Example:

如果您的目标是监视MySQL空闲空间，并且不能阻止MySQL缩小您的ibdata文件，那么可以通过表状态命令获取它。例子:

MySQL > 5.1.24:

MySQL > 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $20}'

MySQL < 5.1.24:

MySQL < 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $35}'

Then compare this value to your ibdata file:

然后将此值与您的ibdata文件进行比较:

du -b ibdata1

Source: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

来源:http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

#6

In a new version of mysql-server recipes above will crush "mysql" database. In old version it works. In new some tables switches to table type INNODB, and by doing so you will damage them. The easiest way is to dump all you databases, uninstall mysql-server, add in remained my.cnf:

在新版本的mysql-server食谱中，将会粉碎“mysql”数据库。在旧版本中它是有效的。在新的一些表中切换到表类型INNODB，这样做会损坏它们。最简单的方法是转储所有的数据库，卸载mysql-server，添加仍然是my.cnf:

[mysqld]
innodb_file_per_table=1


erase all in /var/lib/mysql
install mysql-server
restore users and databases

#7

As already noted you can't shrink ibdata1 (to do so you need to dump and rebuild), but there's also often no real need to.

正如已经注意到的，您不能收缩ibdata1(这样做您需要转储和重建)，但是也常常没有真正的需要。

Using autoextend (probably the most common size setting) ibdata1 preallocates storage, growing each time it is nearly full. That makes writes faster as space is already allocated.

使用autoextend(可能是最常见的大小设置)ibdata1预先分配存储，每次它几乎满的时候增长。因为空间已经分配，所以写得更快。

When you delete data it doesn't shrink but the space inside the file is marked as unused. Now when you insert new data it'll reuse empty space in the file before growing the file any further.

当您删除数据时，它不会收缩，但是文件内的空间被标记为未使用。现在，当您插入新数据时，它将在文件中重新使用空白空间，然后再进一步扩展该文件。

So it'll only continue to grow if you're actually needing that data. Unless you actually need the space for another application there's probably no reason to shrink it.

所以如果你真的需要这些数据，它只会继续增长。除非你真的需要另一个应用的空间，否则没有理由缩小它。