MySQL: reducing ibdata file size for MyISAM tables

innodbmyisamMySQL

My Question is actually very similar to this one and also includes a good answer for a case with InnoDB Engine tables:

What is the best way to reduce the size of ibdata in mysql?,

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.

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.

But what happens with MyISAM tables (with more than 5G table size).

Best Answer

I already answered this in StackOverflow : https://stackoverflow.com/a/11636341/491757

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

Give it a Try !!!

Related Question