How to convert all tables from MyISAM into InnoDB?

Question

I know I can issue an alter table individually to change the table storage from MyISAM to InooDB.

I am wondering if there is a way to quickly change all of them to InnoDB?

Answer 1

You could write a script to do it in your favourite scripting language. The script would do the following:

1. Issue SHOW FULL TABLES.
2. For each row returned, check that the second column says 'BASE TABLE' and not 'VIEW'.
3. If it is not 'VIEW', issue the appropriate ALTER TABLE command.

Answer 2

<?php
    // connect your database here first 
    // 
    $sql = "SHOW tables";
    $rs = mysql_query($sql);

    while($row = mysql_fetch_array($rs))
    {
        $tbl = $row[0];
        $sql = "ALTER TABLE $tbl  ENGINE=INNODB";
         mysql_query($sql);
    }
?>

Answer 3

You can execute this statement in the mysql command line tool:

echo "SELECT concat(concat('ALTER TRABLE ', TABLE_NAME), ' ENGINE=InnoDB;') 
FROM TABLES 
WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE' 
AND TABLE_SCHEMA='name-of-database'" | mysql > convert.sql

You may need to specify username and password using: mysql -u username -p The result is an sql script that you can pipe back into mysql:

mysql name-of-database < convert.sql

Replace "name-of-database" in the above statement and command line.