MySql - Convert InnoDB to MyISAM Storage Engine of

Question

How to convert database storage engine from InnoDB to MyISAM on MySQL? I found so many sites which convert the storage engine of database table, but not for database.

Thanks in advance.

Answer 1

use this!!

SET storage_engine=MYISAM;
ALTER TABLE table_name ENGINE = MyISAM;

-cheers!!

Answer 2

Databases are just MySQL's way of doing namespaces, as such the database has no engine associated to it, only the tables have a storage engine. Which is why you can have a database with several different tables each having a different engine.

You will have to modify each table one by one to switch them to InnoDB.

Answer 3

In PhpMyadmin 4.5 Select the variable tab and find for storage engine and Select Edit and type MyISAM.

Answer 4

You can't change the default storage engine for a database, but you can change it for the whole server using the default-storage-engine config setting. Note that this will not actually change the storage engine for existing tables though, just for new ones created after the change.

Answer 5

You can change the storage engine through PhpMyAdmin. In the details view of the table. See the screenshot:

Changing storage engine in PhpMyAdmin

Answer 6

to make it permanent, add to my.cnf (few locations depending on context)

/etc/my.cnf

default-storage-engine= MyISAM

for safety, output the db list with show databases;

in my case, using php for quickie..

$db = mysql_connect('localhost','someadmin','somepass');
$dbs = array();
$dbs[] = 'test';
$dbs[] = 'myImportantDb';

foreach($dbs as $v){
    mysql_select_db($v);
    $q = mysql_query('show tables');
    $tables = array();
    while($r = mysql_fetch_row($q)){
            $tables[] = $r[0];
    }
    foreach($tables as $t){
        echo "do $v.$t\n";
        mysql_query('ALTER TABLE `'.$t.'` ENGINE=MyISAM;');
    }
}
mysql_close($db);