A friend just asked me how to convert a bunch of MySQL tables from MyISAM to InnoDB, and since it seems that Google yields no practical answers to this question, I am posting the following few lines as a reference for other people. Nothing new, but useful if you are not familiar with either MySQL or bash.
The SQL statement to convert a single table to a different table type in the MySQL client is:
alter table mytable type=InnoDB;
More often than not you will need to convert all the tables in a single DB, so if you are using bash you can save a few boring keystrokes and the inevitable typing errors using something like this:
for t in $(mysql --batch --column-names=false -e "show tables" mydbname); do mysql -e "alter table $t type=InnoDB" mydbname; done
If you need to omit a few tables, or convert only tables starting with a common prefix, you can use grep in the for statement:
for t in $(mysql --batch --column-names=false -e "show tables" mydbname |grep -v "exclude_this"); do mysql -e "alter table $t type=InnoDB" mydbname; done
for t in $(mysql --batch --column-names=false -e "show tables" mydbname |grep "include_this"); do mysql -e "alter table $t type=InnoDB" mydbname; done
Or directly type the names of the tables you need:
for t in table_1 table_2 table_n; do mysql -e "alter table $t type=InnoDB" mydbname; done
Inspiring.
I am going to write a PHP script that will do this for me and may be post the same to my blog.
thanx
Great bit of info, especially for guys like me who don’t really want to learn Bash just to change my MySQL table types.
One note, on my mysql binary (v. 3.23.58) there doesn’t appear to be a “-column-names” switch, and the script above will not work. Running without that switch will get the job done, but the script will give a single error when it tries to convert a “Tables_in_dbname” table to innodb.
Leonard, thanks for pointing out the missing switch in MySQL 3.x.
As you have noticed the warning message does no harm, but if you want to exclude it just use “| grep -v Tables_in” after the mysql invocation, as shown in one of the examples above.