'ALTER table' for all tables in a database

Question

How can I run the following for every table in a database:

ALTER table [table_name] type=innodb;

I don't want to have to manually run it for each table, but rather run it for all tables in a database. As an aside: If your curious as to why I am running this: http://bugs.mysql.com/bug.php?id=1341 & http://bugs.mysql.com/bug.php?id=1287

Answer 1

Assuming your have your credentials setup on your my.cnf a command like this may do what you want.

mysql --batch --skip-column-names --execute \
'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," type=innodb;") 
 from information_schema.TABLES where TABLE_SCHEMA="database_name"' | mysql

Of course replace database_name with your database name.

As for you main issue. It sounds like you want to reclaim space after letting your innodb tables get large. Like the bug said, the only safe way to deal with this is to use mysqldump to export everything. Delete the files and then restore.

As that article notes. You can enable innodb_file_per_table option to your server's my.cnf will make each file exist as a separate file. That way reclaiming space can be done by deleting/restoring the problem table instead of everything.

Answer 2

About Zoredache's answer (which has been very useful for me, thank you): --skip-column-names should be used to remove the headline.

mysql --batch --skip-column-names -e \ 
'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," type=innodb;")  
 from information_schema.TABLES where TABLE_SCHEMA="database_name"' | mysql

Answer 3

I don't know which language you're using, but you can probably just run:

show table status from `DATABASE`;

Then loop through the result set, generating a query for each.