I wrote the stored file below that does what you need, recommend testing WELL it before in development or certification environment! I am not responsible for any problems =)
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `convert_tables`(IN schema_name VARCHAR(255))
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE t VARCHAR(255);
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = schema_name AND engine = 'MyISAM' INTO n;
SET i=0;
WHILE i < n DO
SELECT table_name FROM information_schema.tables WHERE table_schema = schema_name AND engine = 'MyISAM' LIMIT i,1 INTO t;
SET @alter_sql = concat('alter table ',t,' ENGINE = InnoDB;');
PREPARE stmt FROM @alter_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i = i + 1;
END WHILE;
END
To run a stored trial:
CALL convert_tables('teste');
Where 'test' is the name of the schema where your tables are.
Explanation:
Procedure initially calculates the number of tables with engine = 'Myisam' and assigns this value to variable n.
Then we iterate in each row of the above search extracting the table name for the variable t.
Having the table name in t, just run the command ALTER TABLE t ENGINE = InnoDB
, however, it is not possible to directly execute the above command because Mysql does not allow using variables as placeholdes for tables. As a workaround, we need to build the above command into a string and create a statement from it, after that just run it.
Sources:
https://stackoverflow.com/questions/2754423/use-a-variable-for-table-name-in-mysql-sproc
https://stackoverflow.com/questions/5817395/how-can-i-loop-through-all-rows-of-a-table-mysql