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

How to shrink/purge ibdata1 file in MySQL

提问by lokheart

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.

我在localhost中使用MySQL作为在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 文件已经超过 100 MB。

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 文件？

回答by John P

That ibdata1isn't shrinking is a particularly annoying feature of MySQL. The ibdata1file 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 ibdata1will not grow as large. According to Bill Karwin's commentthis is enabled by default as of version 5.6.6 of MySQL.

但是您可以配置 MySQL，以便将每个表（包括其索引）存储为单独的文件。这样ibdata1就不会长得那么大了。根据Bill Karwin 的评论，从 MySQL 5.6.6 版开始，默认情况下启用此功能。

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.cnfin 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 ibdata1you actually have to delete the file:

因为你想收回空间，ibdata1你实际上必须删除文件：

1. Do a mysqldumpof all databases, procedures, triggers etc except the mysqland performance_schemadatabases
2. Drop all databases except the above 2 databases
3. Stop mysql
4. Delete ibdata1and ib_logfiles
5. Start mysql
6. Restore from dump

1. 执行除和数据库之外的mysqldump所有数据库、过程、触发器等mysqlperformance_schema
2. 删除除上述 2 个数据库以外的所有数据库
3. 停止mysql
4. 删除ibdata1和ib_log文件
5. 启动mysql
6. 从转储恢复

When you start MySQL in step 5 the ibdata1and ib_logfiles 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?id=1341

By using the command ALTER TABLE <tablename> ENGINE=innodbor 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 <tablename> ENGINE=innodb或OPTIMIZE TABLE <tablename>一个可以从 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_schemais 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使用内存数据库引擎和被丢弃，一旦停止再生/重启mysqld的。请参阅https://dev.mysql.com/doc/refman/5.7/en/information-schema.html。

回答by Vinay Vemula

Adding to John P's answer,

添加到John 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. DROP DATABASE [database_name];
3. sudo /etc/init.d/mysqld stop
4. 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_logfile1etc...)
5. sudo /etc/init.d/mysqld start
6. create database [database_name];
7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
2. DROP DATABASE [database_name];
3. sudo /etc/init.d/mysqld stop
4. sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile（并删除任何其他可能命名为 ib_logfile 的文件ib_logfile0，ib_logfile1等等...）
5. sudo /etc/init.d/mysqld start
6. create database [database_name];
7. mysql -u [username]-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 以涵盖您希望保留的所有数据库。

回答by titanoboa

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

回答by Vik

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 数据文件无法缩小。这就是为什么 DELETE、TRUNCATE、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/

回答by Pierre-Alexis de Solminihac

Quickly scripted the accepted answer's procedure in bash:

在 bash 中快速编写已接受答案的程序：

#!/usr/bin/env bash
DATABASES="$(mysql -e 'show databases \G' | grep "^Database" | grep -v '^Database: mysql$\|^Database: binlog$\|^Database: performance_schema\|^Database: information_schema' | sed 's/^Database: //g')"
mysqldump --databases $DATABASES -r alldatabases.sql && echo "$DATABASES" | while read -r DB; do
    mysql -e "drop database \`$DB\`"
done && \
    /etc/init.d/mysql stop && \
    find /var/lib/mysql -maxdepth 1 -type f \( -name 'ibdata1' -or -name 'ib_logfile*' \) -delete && \
    /etc/init.d/mysql start && \
    mysql < alldatabases.sql && \
    rm -f alldatabases.sql

Save as purge_binlogs.shand run as root.

另存为purge_binlogs.sh并运行为root.

Excludes mysql, information_schema, performance_schema(and binlogdirectory).

不包括mysql, information_schema, performance_schema（和binlog目录）。

Assumes you have administrator credendials in /root/.my.cnfand that your database lives in default /var/lib/mysqldirectory.

假设您有管理员凭据，/root/.my.cnf并且您的数据库位于默认/var/lib/mysql目录中。

You can also purge binary logs after running this script to regain more disk space with:

您还可以在运行此脚本后清除二进制日志以重新获得更多磁盘空间：

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

回答by Cyno

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 文件，那么请通过 table status 命令获取它。例子：

MySQL > 5.1.24:

MySQL > 5.1.24：

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

MySQL < 5.1.24:

MySQL < 5.1.24：

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

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

回答by adjustable_wrench

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:

在 mysql-server 的新版本中，上面的配方将粉碎“mysql”数据库。在旧版本中它有效。在新的一些表切换到表类型 INNODB，这样做会损坏它们。最简单的方法是：

• dump all you databases
• uninstall mysql-server,
• add in remained my.cnf:

• 转储所有数据库
• 卸载mysql服务器，
• 添加仍然是my.cnf：

    [mysqld]
    innodb_file_per_table=1

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

• 擦除 /var/lib/mysql 中的所有内容
• 安装 mysql 服务器
• 恢复用户和数据库

回答by steveayre

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.

使用自动扩展（可能是最常见的大小设置） 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.

因此，如果您确实需要该数据，它只会继续增长。除非您确实需要其他应用程序的空间，否则可能没有理由缩小它。