Convert all MyISAM tables into InnoDB

InnoDB has become the default Storage Engine for mysql tables, which offers more features and better performance than MyISAM.

If you have website created in or around the year 2009-2010 then your website still be using MyISAM based tables even if you have already upgraded your mysql server version to the superior or latest, like i had one.


Here are the steps to convert all MyISAM tables into InnoDB.

login into SSH terminal and run the commands and queries.
step 1:

mysql -u root -p database_name

you will be asked to enter password, then you will see mysql console where you can execute queries.

step 2:

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'database_name';

now you will all table names that has MyISAM as storage engine with query format to convert into InnoDB.
example

ALTER TABLE table_name ENGINE=InnoDB;

step 3: now copy all the alter queries and paste in the mysql console. you can copy paste one by one or all at once.
be aware of | and +------------------------------------------------------+ around the list. you can use text/code editor like notepad, notepad++ , vs code to remove those characters.


Thanks & Credits to https://computingforgeeks.com/how-to-convert-all-mysql-tables-from-myisam-into-innodb-storage-engine/