MySQL:减小MyISAM表的ibdata文件大小

Question

我的问题实际上与这个问题非常相似，并且还包括了一个关于InnoDB引擎表的很好的答案：

https://dba.stackexchange.com/questions/8982/is-there-any-best-way-to-reduce-the-size-of-ibdata-in-mysql/8983#8983，

我注意到drop schema不会缩小ibdata文件，因此我寻找了一种方法来配置DB，以便在删除模式后减小文件的大小。

我发现很多链接都在讨论InnoDB和每个文件保存表的方法，这样.frm文件本身就会包含表数据，这样就可以减少数据量。

但是对于MyISAM表(表大小超过5G )会发生什么呢？

Answer 1

ibdata1和MyISAM是互斥的。

你应该做的第一件事是计算有多少个表同时使用了两个存储引擎：

SELECT COUNT(1) EngineCount,engine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
GROUP BY engine;

如果某些表是InnoDB的：

执行我的CleanUp of InnoDB

• Howto: Clean a mysql InnoDB storage engine?
• https://dba.stackexchange.com/questions/8982/is-there-any-best-way-to-reduce-the-size-of-ibdata-in-mysql/8983#8983

如果只有MyISAM表，没有InnoDB表：

首先，消除InnoDB的任何痕迹，执行以下操作：

STEP01)将此代码添加到my.cnf

[mysqld]
skip-innodb

STEP02) service mysql restart

STEP03) rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*

完成这些步骤后，您可以对每个MyISAM表执行压缩，如下所示：

对于为MyISAM的表mydb.mytable，只需运行以下命令之一：

• OPTIMIZE TABLE mydb.mytable;
• ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;

如果你想对所有的MyISAM表进行碎片整理，这里有一个外壳脚本可以做到这一点…

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='MyISAM' AND table_schema NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GlobalMyISAMOptmizeTable.sql
less GlobalMyISAMOptmizeTable.sql

一旦您在视觉上信任该脚本，只需运行它

mysql ${MYSQL_CONN} < GlobalMyISAMOptmizeTable.sql

试一试吧！

更新2012-07-25 09:52美国东部时间

我想澄清一下我对MyISAM压缩的一个建议

我之前说过

• OPTIMIZE TABLE mydb.mytable;
• ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;

这些命令在机械上是相同的。OPTIMIZE TABLE对MyISAM表执行碎片整理，然后运行ANALYZE TABLE来计算最新的索引统计信息。

机械地讲，这就是ALTER TABLE mydb.mytable ENGINE=MyISAM;所做的：

CREATE TABLE mydb.mytabletmp LIKE mydb.mytable;
INSERT INTO mydb.mytabletmp SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytabletmp RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

Answer 2

如前所述，MyISAM不应该使用ibdata。您的my.cnf或MySQL外壳类型中的innodb设置是什么：

    SHOW VARIABLES LIKE "%innodb%";

这些变量设置好了吗？

    innodb_data_home_dir
    innodb_data_file_path

如果您根本没有使用INNODB，那么您应该能够安全地删除ibdata和ib_logfile并重新启动MySQL。不过，通常情况下，在不首先删除表的情况下删除这些表会导致问题。请参阅How to shrink/purge ibdata1 file in MySQL

如果你有一个超过5Gigs的MyISAM表，建议你使用INNODB。(任何超过4 Gigs的数据)。要进行故障排除，您可以尝试添加

    skip-innodb 

在my.cnf中，如果您根本不使用INNODB的话。