How can I convert all InnoDB Tables to MyISAM?

Hi guys I’m searching for a methode (sql code) with which I can convert in my WordPressDB all InnoDBs to MyISAM .

My code since now is:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;') FROM Information_schema.TABLES WHERE ENGINE = 'InnoDB'

This gives me back:

ALTER TABLE db.wp_comments ENGINE=MyISAM;
ALTER TABLE db.wp_links ENGINE=MyISAM;
ALTER TABLE db.wp_options ENGINE=MyISAM;
ALTER TABLE db.wp_postmeta ENGINE=MyISAM;
ALTER TABLE db.wp_posts ENGINE=MyISAM;
ALTER TABLE db.wp_term_relationships ENGINE=MyISAM;
ALTER TABLE db.wp_term_taxonomy ENGINE=MyISAM;
ALTER TABLE db.wp_termmeta ENGINE=MyISAM;
ALTER TABLE db.wp_terms ENGINE=MyISAM;
ALTER TABLE db.wp_usermeta ENGINE=MyISAM;
ALTER TABLE db.wp_users ENGINE=MyISAM;

Now I want to execute all of this rows at once I want to save all the results in one variable and execute them. But a sql variable can just save one row… How do I execute now all of this rows at once with just plain sql code?

Thanks in advance

P.S.: can I use “Update” to solve this problem as update may can update the engine to MyISAM?

Just tested another (simple ?) way, and worked for me.

Just export your DB as .sql file, edit-it with gedit or notepad;

Replace ‘ENGINE=INNODB’ with ‘ENGINE=MyISAM’ and Save the file edited

Number or replacement done should be the number of your tables

Import it to MySQL (phpMyAdmin or command line)

And Voila !

Take backup of Mysql database. Run this sql query via terminal for the database which you wish to convert into MYISAM.

mysql -h databasehostname -u username -p -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'" | tail -n+2 >> alter.sql

Note: Change ‘db_name’ with your actual database name

Note: You only need the -h flag if your database is not local to the machine you are logged in to the terminal of

Import that alter.sql file into INNODB database

mysql -h databasehostname -u username -p databasename < alter.sql

I don’t know how you would run this from phpmyadmin, but the terminal works beautifully 

– 

0

Why? MyISAM is going away in the next release of MySQL. MyISAM is less efficient, less robust, etc, etc.

Here’s how to do the ALTERs:

1. Generate alter statements into a file or the screen. (You have done that.)
2. Copy them (or feed them) into the mysql commandline tool.
3. Go get a coffee.

But watch out for errors.

source : https://stackoverflow.com/questions/42477334/how-can-i-convert-all-innodb-tables-to-myisam