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

Question

我在localhost中使用MySQL作为R中执行统计的“查询工具”，即每次运行R脚本时，我创建一个新数据库（A），创建一个新表（B），将数据导入B ，提交一个查询来获取我需要的内容，然后我删除 B 并删除 A。

这对我来说工作正常，但我意识到 ibdata 文件大小正在快速增加，我在 MySQL 中没有存储任何内容，但 ibdata1 文件已经超过 100 MB。

我或多或少使用默认 MySQL 设置进行设置，有没有办法可以在固定时间段后自动收缩/清除 ibdata1 文件？

Answer 1

当你删除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

Answer 2

在 bash 中快速编写已接受答案的过程脚本：

#!/usr/bin/env bash
dbs=$(mysql -BNe 'show databases' | grep -vE '^mysql$|^(performance|information)_schema
另存为 purge_binlogs.sh 并以 root 身份运行。
不包括 mysql、information_schema、performance_schema（和 binlog 目录）。
假设您在 /root/.my.cnf 中有管理员凭据，并且您的数据库位于默认的 /var/lib/mysql 目录中。
您还可以在运行此脚本后清除二进制日志以重新获得更多磁盘空间：
PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

)
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 目录中。

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

Answer 3

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

Answer 4

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

阅读后Percona 的博客文章 关于该主题，我在 MariaDB 10.6 中启用了 UNDO LOG 条目的截断，该条目填充了 95% 的 ibdata1，并且在完全删除和恢复，从那一刻起，我的 ibdata1 就不再增长了。

使用默认的 innodb_undo_log_truncate = 0 我的 ibdata1 很容易达到 10% 的数据库空间占用，也就是几十 GB。

当 innodb_undo_log_truncate = 1 时，ibdata1 的大小固定为 76 Mb。

Answer 5

如果您的目标是监视 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

Answer 6

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

• 转储所有数据库
• 卸载mysql-server，
• 添加保留的my.cnf：

    [mysqld]
    innodb_file_per_table=1

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

Answer 7

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

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

以下是我在重建数据库服务器以使 ibdata1 恢复到合理大小时遵循的一些注释/过程：

• 我已经使用了 innodb_file_per_table=1。 p>

• 我使用此工具来确定ibdata1中的空间使用情况确实撤消日志页面（运行了几个小时，这是在我意识到 10.6.13 中存在错误并且不确定是什么导致 ibdata1 增长如此之多之前）。

• 我们有一个主副本和三个副本。

• 我首先从生产中取出一个副本，使用 mydumper 转储/恢复它，然后使用 mariabackup 备份重建的副本。我使用该备份来恢复其余的数据库服务器（速度快得多）。

• 我们已经使用 mariabackup 建立了完整的备份例程。 在做任何事情之前，请确保您有一个已知良好的完整备份！

重建第一台服务器

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

我会刷新表；设置 global read_only = 1; 以 100% 确保备份期间没有任何内容写入数据库（当然，在副本上，stopreplica;，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 分钟将价值 865GB 的数据库转储到一台相当强大的企业服务器上，该服务器在硬件 RAID 10 中配备了 4 个 SSD。旧服务器这需要不到一小时。

Grep 日志中是否有任何错误。

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

然后我添加了此配置以将撤消日志移出 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，企业服务器，10gig网络，RAID 10中的4个SSD）。

撤消日志截断现在肯定可以工作。我最初将 innodb_max_undo_log_size 设置为 10MB，MariaDB 不断地截断它们。当它这样做时它会记录。

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

Answer 8

正如已经指出的，您无法缩小 ibdata1（为此，您需要转储和重建），但通常也没有真正的需要。

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

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

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

Answer 9

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

当您想从 ibdata1 回收空间时，您实际上必须删除该文件：

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

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

现在你可以出发了。当您创建新数据库进行分析时，这些表将位于单独的 ibd* 文件中，而不是位于 ibdata1 中。由于您通常会在不久后删除数据库，因此 ibd* 文件将被删除。

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

您可能已经看过这个：
http://bugs.mysql.com/bug.php?id=1341

通过使用命令 ALTER TABLEENGINE=innodb 或 OPTIMIZE TABLE 可以从 ibdata1 中提取数据和索引页到单独的文件中。但是，除非执行上述步骤，否则 ibdata1 不会缩小。

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

Answer 10

添加到 John P 的回答，

对于 Linux 系统，可以使用以下命令完成步骤 1-6：

1. mysqldump -u [用户名] -p[root_password] [数据库名] > dumpfilename.sql
2. mysqladmin -u [用户名] -p[root_password] drop [数据库名称]
3. sudo /etc/init.d/mysqld stop
4. sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile*
5. sudo /etc/init.d/mysqld start
6. mysqladmin -u [用户名] -p[root_password] create [database_name]
7. mysql -u [用户名] -p[root_password] [database_name] mysql -u [用户名] -p[root_password] [数据库名] < dumpfilename.sql

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