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;