Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 8 down vote accepted

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.

share|improve this answer
1  
Very elegant solution. –  John Gardeniers Mar 22 '10 at 5:10

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
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.