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

本文介绍了MySQL:减少MyISAM表的ibdata文件大小的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的问题实际上与这一问题非常相似，并且还为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).

如果某些表是InnoDB:

执行我的InnoDB清理

If SOME tables are InnoDB:

Perform my CleanUp of InnoDB

如何:清理mysql 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 InnoDBDo 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文件大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

MyISAM