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

加入John P的答案,

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

mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql

DROP DATABASE [database_name];

sudo /etc/init.d/mysqld stop

sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile (和删除任何其他ib_logfile的,可能被命名为ib_logfile0,ib_logfile1等...)

sudo /etc/init.d/mysqld start

create database [database_name];

mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

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

删除innodb表时,MySQL不会释放ibdata文件中的空间,这就是它不断增长的原因.这些文件几乎不会缩小.

如何缩小现有的ibdata文件:

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

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

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

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

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

如果你为你的(某些)MySQL表使用InnoDB存储引擎,你可能已经遇到了其默认配置的问题.您可能已经注意到MySQL的数据目录(在Debian/Ubuntu中 - /var/lib/mysql)中有一个名为"ibdata1"的文件.它几乎包含了MySQL实例的所有InnoDB数据(它不是事务日志),并且可能会变得非常大.默认情况下,此文件的初始大小为10Mb,并自动扩展.不幸的是,通过设计InnoDB数据文件无法收缩.这就是为什么DELETE,TRUNCATE,DROP等不会回收文件使用的空间.

我想你可以找到很好的解释和解决方案:

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

在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

另存为purge_binlogs.sh并运行root.

不包括mysql,information_schema,performance_schema(和binlog目录).

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

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

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

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