MySQL:减少MyISAM表的ibdata文件大小 [英] MySQL: reducing ibdata file size for MyISAM tables
问题描述
我的问题实际上与这一问题非常相似，并且还为InnoDB Engine表的情况提供了一个很好的答案:
My Question is actually very similar to this one and also includes a good answer for a case with InnoDB Engine tables:
我注意到删除模式不会收缩ibdata文件，因此我一直在寻找一种配置数据库的方法，以便在删除模式后减小数据库的大小.
I have noticed that drop schema do not shrink ibdata files , so i have looked for a methods to configure the DB so that the size will be reduced after deleting a schema.
我发现许多链接都在谈论InnoDB以及每个文件保存表的方式，以便它自己的.frm文件将包含表数据，并将减少该文件.
i have found many links talking about InnoDB and the way to save table per file so that the .frm file it self will contain the table data and it will be reduced.
但是MyISAM表(表大小超过5G)会发生什么.
But what happens with MyISAM tables (with more than 5G table size).
推荐答案
ibdata1和MyISAM是互斥的.
ibdata1 and MyISAM are mutually exclusive.
您应该做的第一件事是计算两个存储引擎同时使用多少个表:
First thing you should do is count how many tables use both storage engines:
SELECT COUNT(1) EngineCount,engine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
GROUP BY engine;
如果某些表是InnoDB:
执行我的InnoDB清理
If SOME tables are InnoDB:
Perform my 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
首先，消除InnoDB的任何痕迹 请执行以下操作:
First, eliminate any traces of InnoDB Do the following:
STEP01)将此添加到my.cnf
STEP01) Add this to my.cnf
[mysqld]
skip-innodb
STEP02)service mysql restart
STEP03)rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*
这些步骤之后，您可以像这样对每个MyISAM表执行压缩:
After these steps, you can perform a Compression of Each MyISAM tables like this:
对于MyISAM表mydb.mytable，只需运行以下命令之一:
For the table mydb.mytable that is MyISAM, just run one of the following:
-
OPTIMIZE TABLE mydb.mytable; -
ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;
OPTIMIZE TABLE mydb.mytable;ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;
如果要对所有MyISAM表进行碎片整理，请执行以下Shell脚本...
If you want to defrag all your MyISAM tables, here is a shell script to do so...
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
一旦您直观地信任了脚本，就运行它
Once you trust the script visually, just run it
mysql ${MYSQL_CONN} < GlobalMyISAMOptmizeTable.sql
尝试一下！
我想澄清我对MyISAM压缩的建议之一
I would like to clarify one of my suggestions for compression of MyISAM
我刚才说过
-
OPTIMIZE TABLE mydb.mytable; -
ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;
OPTIMIZE TABLE mydb.mytable;ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;
这些命令在机械上是相同的. OPTIMIZE TABLE对MyISAM表进行碎片整理，然后运行ANALYZE TABLE来计算新的索引统计信息.
These commands are mechanically identical. OPTIMIZE TABLE performs a defrag of the MyISAM table and then runs ANALYZE TABLE to compute fresh index statistics.
从机械上讲，这是ALTER TABLE mydb.mytable ENGINE=MyISAM;的作用:
Mechanically speaking, this is what ALTER TABLE mydb.mytable ENGINE=MyISAM; does:
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;
这篇关于MySQL:减少MyISAM表的ibdata文件大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
