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

Question

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

mysqldatabaseinnodb

580

我在本地使用MySQL作为“查询工具”来执行R语言中的统计分析。也就是说，每次运行R脚本时，我会创建一个新的数据库（A），创建一个新的表格（B），将数据导入B，提交查询以获取所需内容，然后删除B和A。

这个方法对我来说很有效，但是我发现ibdata文件的大小正在快速增加。虽然我没有在MySQL中存储任何东西，但ibdata1文件已经超过了100 MB。

我使用了更多或更少默认的MySQL设置来进行设置，请问有什么方法可以在一定时间后自动缩小/清除ibdata1文件吗？

- lokheart

更多参考：当配置innodb_file_per_table时，ibdata1会呈指数增长 - hakre

10个回答

60

在John P的答案基础上，对于Linux系统，可以使用以下命令完成步骤1-6：

mysqldump -u [用户名] -p[根密码] [数据库名称] > dumpfilename.sql
mysqladmin -u [用户名] -p[根密码] drop [数据库名称]
sudo /etc/init.d/mysqld stop
sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile*
sudo /etc/init.d/mysqld start
mysqladmin -u [用户名] -p[根密码] create [数据库名称]
mysql -u [用户名] -p[根密码] [数据库名称] < dumpfilename.sql

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

- Vinay Vemula

7

对于每个拥有 InnoDB 表的数据库，您需要重复执行 1、2 和 6 步骤。 - user207421

4

在第5步和第6步之间，您需要再添加几个步骤。您需要重新创建数据库并重新分配权限。因此，在mysql客户端命令提示符下，输入create database database_name;，然后输入grant all privileges on database_name.* to 'username'@'localhost' identified by 'password';。 - fred

1

@fred 我在执行这个操作的时候不需要授予权限。可能是因为我使用了相同名称重新创建了数据库？ - crmpicco

2

在 Password: 提示符处输入密码（这是更安全的做法），只需输入 -p 而不需要实际密码。 - ADTC

2

现在这会导致错误 InnoDB: File ./ibdata1: 'open' returned OS error 71. Cannot continue operation，因此无法重新启动服务器！ - Parfait

显示剩余4条评论

34

当你删除InnoDB表时，MySQL不会释放ibdata文件内的空间，这就是为什么它会持续增长。这些文件几乎不会收缩。

如何收缩现有的ibdata文件：

https://dev.mysql.com/doc/refman/5.6/en/innodb-system-tablespace.html#innodb-resize-system-tablespace

你可以编写脚本并安排定期运行脚本，但对于上述设置，多个表空间似乎是更简单的解决方案。

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

有关多个表空间的更多信息：

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

- titanoboa

第一个链接已经损坏，我找到了最接近的匹配页面： https://dev.mysql.com/doc/refman/5.5/en/innodb-system-tablespace.html#innodb-resize-system-tablespace - BlackICE

18

快速将被接受的答案中的步骤编写成Bash脚本：

#!/usr/bin/env bash
dbs=$(mysql -BNe 'show databases' | grep -vE '^mysql$|^(performance|information)_schema$')
mysqldump --events --triggers --databases $dbs > alldatabases.sql && \
    echo "$dbs" | while read -r db; do
        mysqladmin drop "$db"
    done && \
    mysql -e 'SET GLOBAL innodb_fast_shutdown = 0' && \
    /etc/init.d/mysql stop && \
    rm -f /var/lib/mysql/ib{data1,_logfile*} && \
    /etc/init.d/mysql start && \
    mysql < alldatabases.sql

保存为purge_binlogs.sh并以root身份运行。

排除mysql、information_schema、performance_schema（和binlog目录）。

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

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

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

- Pierre-Alexis de Solminihac

今天我还不确定为什么，在类似的过程中我的一些InnoDB表格被损坏了，所以在删除“alldatabases.sql”之前一定要双重检查所有的表格是否健康。至于一些改进：在关闭之前设置“innodb_fast_shutdown=0”，在导入SQL文件之前设置“autocommit=0”，执行“COMMIT”，在导入SQL文件之后设置“autocommit=1”，在删除备份之前使用“mysqlcheck --all-databases”。 - Victor

14

如果您在MySQL表中使用InnoDB存储引擎，您可能已经遇到了其默认配置的问题。正如您可能已经注意到的那样，在MySQL的数据目录中（在Debian/Ubuntu中为/var/lib/mysql），有一个名为'ibdata1'的文件。它保存了MySQL实例的几乎所有InnoDB数据（它不是事务日志），可能会变得非常大。默认情况下，该文件的初始大小为10MB，并且会自动扩展。不幸的是，根据设计，InnoDB数据文件无法缩小。这就是为什么DELETE、TRUNCATE、DROP等操作无法回收文件使用的空间的原因。

- Vik

那个链接已经失效了。 - grahamparks

8

没有人提到的是innodb_undo_log_truncate设置可能会产生的影响。

在阅读了Percona的博客文章之后，我在我的MariaDB 10.6中启用了对填充了95%的UNDO LOG条目进行截断的功能，并在完全删除和恢复后，从那时起，我的ibdata1再也没有增长过。

默认情况下innodb_undo_log_truncate = 0，我的ibdata1轻松达到了数据库空间占用的10%，即数十GB。

使用innodb_undo_log_truncate = 1，ibdata1固定在76 MB。

- Slam

难以置信...所有被点赞的错误答案...似乎没有人知道这个innodb_undo_log_truncate选项...我寻找解决方案已经好几年了，你的答案是无价之宝！我正在运行tmpfs/inmemory，在你的帮助下，InnoDB现在只占用50MB，而且这是恒定的！ - lmagyar

附加阅读：https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html - lmagyar

“截断驻留在系统表空间 [ibdata1] 中的撤消日志不受支持。”似乎与您的说法相矛盾，@Imagyar。参考 https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_undo_log_truncate - Rick James

当innodb_file_per_table设置为ON时（默认值），会修改系统表空间吗？架构更改？撤消日志“管理”？统计/指标？锁定？我只是猜测，请帮我进行纠正！有关如何最小化这些内容是否有任何指导？ - lmagyar

7

如果你的目标是监控MySQL的空闲空间，而且你不能停止MySQL来缩小ibdata文件，那么可以通过表状态命令来获取。例如：

MySQL > 5.1.24:

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

MySQL < 5.1.24:

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

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

du -b ibdata1

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

本文介绍了MySQL数据库中用于显示表状态的SHOW TABLE STATUS语句。使用此语句可以获取有关表的详细信息，如名称、引擎、行数等。此外，还可以通过添加LIKE子句来过滤结果，以便只显示特定表的信息。

- Cyno

5

在新版本的mysql-server中，以上述方式处理"mysql"数据库将导致其崩溃。在旧版本中，它可以正常工作。在新版本中，一些表会转换为INNODB表类型，并通过这样做会损坏它们。最简单的方法是：

备份您所有的数据库
卸载mysql-server
在剩余的my.cnf文件中添加以下内容：

    [mysqld]
    innodb_file_per_table=1

清空 /var/lib/mysql 中的所有内容
安装 mysql-server
恢复用户和数据库

- adjustable_wrench

0

正如已经指出的，您无法收缩ibdata1（要这样做，您需要转储并重建），但通常也没有真正的必要。

使用自动扩展（可能是最常见的大小设置），ibdata1预分配存储空间，每次接近满时都会增长。这使得写入更快，因为空间已经分配。

当您删除数据时，它不会缩小，但文件内部的空间被标记为未使用。现在，当您插入新数据时，它将在进一步增加文件之前重用文件中的空闲空间。

因此，只有在实际需要该数据的情况下，它才会继续增长。除非您实际上需要另一个应用程序的空间，否则可能没有缩小它的理由。

- steveayre

73

我认为你对释放空间的需求有点过于轻视。 - drewish

2

我有一个60G的固态硬盘分区。由于我使用4+G的数据库，空间很快就用完了。我计划很快将mysql移动到另一个分区，但是这个问题及其答案在此期间会对我有所帮助。 - NullVoxPopuli

3

谢谢您的回答，它非常有帮助。我已清理了一些旧数据中的表格……很高兴知道磁盘上的大小不会在短时间内再次增长。 - Brad

2

我有一个500G的ibdata1文件 - 但几乎所有存储在其中的数据现在都存储在每个数据库文件中。我非常需要缩小这个巨大的空间浪费！ - frankster

5

完全胡说八道！一个不断膨胀的文件，无论你是否快要用完空间，都需要进行修剪。我会称之为“存储泄漏”。 - ADTC

0

我们最近在MariaDB 10.6.13（修复于10.6.14）中遇到了一个错误，导致我们的ibdata1文件每天增长超过5GB。这个文件增长到超过400GB，直到磁盘空间使用警报开始发送出去。

我们的数据库总共约为865GB，因此使用mysqldump进行逻辑备份/还原将需要至少几天时间。如果您有一个大型数据库，可以使用mydumper更快地进行逻辑备份/还原，因为显然这仍然是缩小ibdata1文件的唯一方法。

这里是重建我们的数据库服务器以将ibdata1恢复到合理大小时我遵循的一些注意事项/过程：

我已经使用innodb_file_per_table=1。
我使用this tool来确定ibdata1中的空间使用情况确实是undo日志页面（运行需要几个小时，此时我还不知道10.6.13版本存在一个错误，并且不确定导致ibdata1增长如此之大的原因）。
我们有一个主服务器和三个副本。
首先将一个副本从生产环境中移除，使用mydumper进行转储和还原，然后使用mariabackup备份重建后的副本。我使用该备份还原了其余的数据库服务器（速度非常快）。
我们已经有了使用mariabackup的完整备份例程。在执行任何操作之前，请确保拥有一份已知良好的完整备份！

重建第一个服务器

停止对数据库的所有写入操作。

我执行flush tables; set global read_only = 1;来确保备份期间数据库不会写入任何内容（当然，在副本上执行stop replica;，我们的配置中已经设置了read_only=1）。

使用mydumper来备份数据库。我使用了这个脚本，在备份完成后会发送一封邮件（如果你已经设置好了电子邮件）：

#!/usr/bin/bash
time mydumper -t "$(nproc)" \
  --compress \
  --triggers \
  --routines \
  --events \
  --rows 10000000 \
  --verbose 3 \
  --build-empty-files \
  --regex '^(?!(test|performance_schema|information_schema|sys))' \
  --logfile /mnt/mysql_backups/ibdatafix_backup.log \
  --outputdir /mnt/mysql_backups/ibdatafix_backup 2>&1

echo "DUMP ON $(hostname) DONE" | mail -s "DUMP ON $(hostname) DONE" root

这个过程花了12分钟，在一台配置相当强大的企业服务器上，使用4个SSD硬件RAID 10来转储865GB的数据库。在旧服务器上，这个过程只需要不到一个小时。

在日志中搜索任何错误信息。

我尝试简单地删除我们的数据库，关闭MariaDB，并删除ibdata1文件，但是这引起了问题，所以我关闭了MariaDB，清空了整个数据目录（rm -rf /var/lib/mysql/*），然后运行mysql_install_db重新开始（确保你有好的备份！）。

然后我添加了这个配置，将undo日志移出ibdata1。根据你的环境进行调整，如果你想这样做的话。此时我认为这是个好主意，主要是因为没有办法截断ibdata1，而且我不想很快再次重建所有的服务器。

# About 3GB.
innodb_max_undo_log_size = 3221225472

# I moved the undo logs into a separate volume. You can set this to
# /var/lib/mysql.
innodb_undo_directory = /var/lib/mysql_undo_logs

# This appears to only work if innodb_undo_directory is set and
# the undo logs are separate from ibdata1.
innodb_undo_log_truncate = 1

innodb_undo_tablespaces = 3

暂时将这些添加到配置文件中以进行恢复：

innodb_flush_log_at_trx_commit=0
net_read_timeout=60
performance_schema=off
innodb_adaptive_hash_index=off
sql_mode=NO_ENGINE_SUBSTITUTION
innodb_doublewrite=off
slow_query_log = 0

现在启动MySQL / MariaDB并恢复：

#!/usr/bin/bash

time myloader \
  --compress-protocol \
  --directory /mnt/mysql_backups/ibdatafix_backup \
  --overwrite-tables \
  --queries-per-transaction 1000 \
  # The recommendation here is half of your available cores. I set this 
  # too high and it lead to timeouts and a botched restore.
  --threads 4 \
  --verbose 3 > restore.log 2>&1

echo "RESTORE ON $(hostname) DONE" | mail -s "RESTORE ON $(hostname) DONE" root

这个任务大约花了12个小时才完成。

请务必检查restore.log文件中的错误信息（grep -i error restore.log）。我的第一次恢复操作完全失败，因为我没有发现其中的错误。

删除临时配置并重新启动MySQL/MariaDB。

在进行其他操作之前，我运行了pt-table-checksum来确保主服务器和所有副本具有相同的数据（尤其是新恢复的副本）。

如果您需要重建其他服务器（其他副本/主服务器），可以使用mariabackup（或Percona XtraBackup），它速度更快。类似于以下命令（我在一个副本上运行此命令，所以添加了所有的从属选项）：

# On the freshly rebuilt server:
rm -rf /mnt/mysql_backups/ibdatafix_backup/*

mariabackup --defaults-file=/etc/mysql/my.cnf --parallel=4 \
  --compress --compress-threads=4 --safe-slave-backup --slave-info \
  --user=root --backup --target-dir=/mnt/mysql_backups/ibdatafix_backup

# Then on the other server(s), shut down the DB, wipe /var/lib/mysql, and copy the backup back over:
systemctl stop mariadb/mysql

cd /var/lib/mysql

rm -rf *

# This is an NFS share mounted on all DB servers.
rsync -av /mnt/mysql_backups/ibdatafix_backup .

mariabackup --decompress --parallel=8 --remove-original --target-dir=.

mariabackup --prepare --use-memory=32G --target-dir=.

chown -R mysql:mysql .

systemctl start mariadb/mysql

# Set up replication etc.

这一切大约花了每台服务器两个小时才完成（865GB的数据库，企业级服务器，10G网络，4个SSD组成的RAID 10）。

撤销日志截断现在绝对有效。我最初将innodb_max_undo_log_size设置为10MB，MariaDB不断地进行截断。它会在执行时记录下来。

我们高度活跃的服务器上的ibdata1现在保持稳定，只有12MB。

- Bren

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John P · Accepted Answer

ibdata1 文件大小无法缩小是 MySQL 的一个非常令人恼火的特性。除非你删除所有数据库、移除文件并重新加载转储，否则无法实际缩小 ibdata1 文件。

但是你可以配置 MySQL，使每个表（包括其索引）都存储为单独的文件。这样，ibdata1 不会变得过大。根据 Bill Karwin 的评论，自 MySQL 5.6.6 版本以来，默认启用了此功能。

我做过这件事情已经有一段时间了。然而，要设置服务器使用每个表的单独文件，您需要更改 my.cnf 以启用此功能：

[mysqld]
innodb_file_per_table=1

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

To reclaim space from ibdata1, you need to delete the file:

Perform a mysqldump of all databases, procedures, triggers, etc., except for the mysql and performance_schema databases.
Drop all databases except for the above 2 databases.
Stop MySQL.
Delete the ibdata1 and ib_log files.
Start MySQL.
Restore from the dump.

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

Now you're good to go. When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1. Since 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

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

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

As for the information_schema, it is not necessary or possible to drop it. It consists of read-only views, not tables, and there are no associated files, not even a database directory. The informations_schema uses 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.