CODEWITHSUNDEEP
phpmysqlphpmyadmin
Tony Stark
Tony Stark

Reputation: 8084

MySql - Convert InnoDB to MyISAM Storage Engine of Database

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. i attached screen shot for better understand

Thanks in advance.

Upvotes: 36

Views: 111468

Answers (7)

Jhonattan Peláez
Jhonattan Peláez

Reputation: 516

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

Changing storage engine in PhpMyAdmin

Upvotes: 0

Benson K B
Benson K B

Reputation: 43

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

Upvotes: 1

Dany Boivin
Dany Boivin

Reputation: 121

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);

Upvotes: 6

Developer
Developer

Reputation: 3998

use this!!

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

-cheers!!

Upvotes: 67

Avinash Saini
Avinash Saini

Reputation: 1243

ALTER TABLE `table_name` ENGINE=INNODB

Upvotes: 19

Eric Petroelje
Eric Petroelje

Reputation: 60498

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.

Upvotes: 14

Ian
Ian

Reputation: 25336

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.

Upvotes: 8

Related Questions