Pieced together from various tutorials on the web with a lot of my own additions, here is a snippet of SQL that will alter or convert all tables in a database from “MyISAM” to “InnoDB”. As long as your MySQL user account has privileges to create stored procedures, all you have to do is simply edit the @DATABASE_NAME variable at the top of the script and run it in PhpMyAdmin if you’ve got it. Enjoy!
# SQL Script: AlterTablesInnoDB # Author: Nathan Thomas # Date: 06/03/2015 SET @DATABASE_NAME='YourDBName'; CREATE TEMPORARY TABLE IF NOT EXISTS AlterTablesInnoDB_TEMP ( SELECT CONCAT('ALTER TABLE `',@DATABASE_NAME,'`.`', table_name, '` ENGINE=InnoDB; ') AS STMT FROM information_schema.tables WHERE table_schema=@DATABASE_NAME AND `ENGINE`='MyISAM' AND `TABLE_TYPE`='BASE TABLE' ORDER BY table_name ASC ); SET @NUMROWS=(SELECT COUNT(*) FROM AlterTablesInnoDB_TEMP); ALTER TABLE AlterTablesInnoDB_TEMP ADD `ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; DROP PROCEDURE IF EXISTS AlterTablesInnoDB; DELIMITER $$ CREATE PROCEDURE AlterTablesInnoDB( IN NUMROWS INT(11) UNSIGNED ) BEGIN DECLARE i INT DEFAULT 1; WHILE i<=NUMROWS DO SET @QRY=''; SET @QRY=(SELECT `STMT` FROM AlterTablesInnoDB_TEMP WHERE ID=i); PREPARE stmt FROM @QRY; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET i=i+1; END WHILE; END$$ CALL AlterTablesInnoDB(@NUMROWS);
And then after you have run that script, you can get rid of the stored procedure we created by running this command:
DROP PROCEDURE IF EXISTS AlterTablesInnoDB;