I wrote up a CleanUp procedure for InnoDB in StackOverflow
https://stackoverflow.com/a/4056261/491757 (Oct 29, 2010)
Here it is
To shrink ibdata1 once and for all you must do the following:
1) MySQLDump all databases into a SQL text file (call it SQLData.sql)
2) Drop all databases (except mysql schema)
3) Shutdown mysql
4) Add the following lines to /etc/my.cnf
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.
5) Delete ibdata1, ib_logfile0 and ib_logfile1
At this point, there should only be the mysql schema in /var/lib/mysql
6) Restart mysql
This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each
7) Reload SQLData.sql into mysql
ibdata1 will grow but only contain table metadata
Each InnoDB table will exist outside of ibdata1
Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table
- mytable.frm (Storage Engine Header)
- mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and Indexes anymore.
With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.
I have done this many times in my career as a MySQL DBA
In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.
Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!
If you do not change any of the FULLTEXT startup parameters, your conversion from MyISAM to InnoDB will take care of creating the FULLTEXT indexes for InnoDB.
CONVERSION STEPS
If you can connect to MySQL from a command-line interface, run this query
SELECT DISTINCT table_schema,table_name
FROM information_schema.statistics WHERE index_type='FULLTEXT';
This will give you every table that has a FULLTEXT index.
Using that query, run this query
SELECT CONCAT('ALTER TABLE ',B.table_schema,'.',B.table_name,' ENGINE=InnoDB') Cmd
FROM (SELECT DISTINCT table_schema,table_name
FROM information_schema.statistics WHERE index_type='FULLTEXT') A
INNER JOIN information_schema.tables B USING (table_schema,table_name)
WHERE B.engine='MyISAM';
This will show you all the commands to convert MyISAM tables with FULLTEXT indexes into InnoDB.
You can dump the commands to a text file.
SQL="SELECT CONCAT('ALTER TABLE ',B.table_schema,'.',B.table_name,' ENGINE=InnoDB')"
SQL="${SQL} FROM (SELECT DISTINCT table_schema,table_name"
SQL="${SQL} FROM information_schema.statistics"
SQL="${SQL} WHERE index_type='FULLTEXT') A"
SQL="${SQL} INNER JOIN information_schema.tables B"
SQL="${SQL} USING (table_schema,table_name)"
SQL="${SQL} WHERE B.engine='MyISAM'"
mysql -uroot -p -ANe"${SQL}" > ConversionScript.sql
less ConversionScript.sql
This will create the conversion script and let you view it.
If you are satisfied with the content of the scripts, log in to mysql and run this:
mysql> source ConversionScript.sql
Give it a Try !!!
Best Answer
Althuogh these are fine questions you're asking, the real root cause of this was a filesystem filling up. That is the problem you need to solve:
If the primary function of this machine is a database server, the MySQL database should be on it's own partition. If it's just a secondary function, at the very least make sure /var is off on it's own partition (or the base directory of whereever your MySQL data directory is). You should not be able to crash MySQL by adding files to /home or /
You should be alerted to filesystems that get far too close to being full. Nagios or hyperic can do this for you and a whole host of other sanity checks and warnings that can alert you to potential dangers.