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;
If SOME tables are InnoDB:
Perform my CleanUp of InnoDB
If you have only MyISAM tables and no InnoDB tables:
First, eliminate any traces of InnoDB
Do the following:
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*
After these steps, you can perform a Compression of Each MyISAM tables like this:
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;
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 !!!
UPDATE 2012-07-25 09:52 EDT
I would like to clarify one of my suggestions for compression of MyISAM
I said earlier
OPTIMIZE TABLE mydb.mytable;ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable;
These commands are mechanically identical. OPTIMIZE TABLE performs a defrag of the MyISAM table and then runs ANALYZE TABLE to compute fresh index statistics.
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;
