[FIXED] Change storage engine from MyISAM to InnoDB - all tables at once - USING phpmyadmin

Issue

I have like 100 tables in mysql database and all are having storage engine: MyISAM. I want to change their storage engine all at once to innoDB and I DO NOT want to use:

1.PHP (How to convert all tables from MyISAM into InnoDB?

2. Laravel(Laravel & InnoDB)

3. One table at a time.(how to change storage engine of database in phpmyadmin?)

4. SQL query .(How to change table storage engine from MyISAM to InnoDB)

5. by xampp or similar way. (How to set the default storage engine to InnoDB in XAMPP )

So I want to change : ALL TABLE STORAGE ENGINE AT ONCE and using phpmyadmin interface.

Any help?

Solution

Will 2 steps suffice?

(1) Run the SELECT against information_schema, as seen in some of those links. But have it output 100 fully-formed ALTER statements.

SELECT CONCAT("ALTER TABLE ", table_name, " ENGINE=InnoDB;
")
    FROM information_schema.tables
    WHERE engine='MyISAM'
      AND table_schema='MyDatabase'
      AND ...;

(2) Copy and paste the 100 lines into phpadmin. Go have a coffee. Or spend the time reading about the gotchas in converting to see if you will have some issues.

Doing them truly in parallel is unlikely to be any faster -- you will be I/O bound. And the small tables will finish fast, leaving the big tables to stretch out the time anyway.

Output sample:

SELECT CONCAT("ALTER TABLE ", table_name, " ENGINE=InnoDB;
")
    FROM information_schema.tables
    WHERE engine='MyISAM'
      AND table_schema='test';

produces something like:

ALTER TABLE 07 ENGINE=InnoDB;\n
ALTER TABLE 597377b ENGINE=InnoDB;\n
ALTER TABLE adjprice ENGINE=InnoDB;\n
ALTER TABLE big ENGINE=InnoDB;\n
ALTER TABLE bigv ENGINE=InnoDB;\n
ALTER TABLE blobid ENGINE=InnoDB;\n
ALTER TABLE charlatin1 ENGINE=InnoDB;\n
ALTER TABLE collorder ENGINE=InnoDB;\n
ALTER TABLE collorder2 ENGINE=InnoDB;\n
ALTER TABLE countries ENGINE=InnoDB;\n
ALTER TABLE d1 ENGINE=InnoDB;\n

Then paste however many rows are generated back into the tool. No need to manually type the names.

If you first convert 100 tables, they will not be included when you run the query again. Only the new 25 will be included.

No, you cannot "automatically" change any new tables to a different engine. However...

By saying this in my.cnf, you can have all new table for which you do not explicitly specify an ENGINE be InnoDB:

[mysqld]
default_storage_engine = InnoDB

Answered By - Rick James