MySql - Convert InnoDB to MyISAM Storage Engine of Database

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

Thanks in advance.

Do you mean you have already tried this ? [http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html](http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html) — Skippy Fastol, Apr 23 '12 at 12:53
I want to convert database not tables.This link you gave is for conversion of tables not database — Tony Stark, Apr 23 '12 at 13:27
Guys if this question helpful, you can upvote also not only answer... — Tony Stark, Mar 14 '13 at 07:20
Possible duplicate of [How to convert all tables from MyISAM into InnoDB?](http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb) — T.Todua, Oct 22 '15 at 20:05
Sorry to ask you but does the fact to convert innodb table to myisam could create issues or not ? I mean if I have innodb without foreign keys restrictions and alls features related. Currently I have innodb tables but I want to convert them as myisam because the innodb tables are used like if it was myisam ones. Thanks. — zeflex, Sep 22 '16 at 20:38

score 66 · Answer 1 · answered Oct 08 '13 at 13:22

66

use this!!

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

-cheers!!

answered Oct 08 '13 at 13:22

Developer

3,857
4
37
47

1

I want to convert InnoDB to MyISAM storage engine of database not table. – Tony Stark Oct 09 '13 at 10:44
2

Thanks. This saved me a lot of time. When I did this on a Raspberry PI running MySQL (mariadb), the system finally stopped locking up when doing big inserts or updates. Literally 10x faster – Rahim Khoja Jul 05 '16 at 15:15

score 19 · Answer 2 · answered Jun 27 '13 at 05:53

19

ALTER TABLE `table_name` ENGINE=INNODB

answered Jun 27 '13 at 05:53

Avinash Saini

1,203
11
10

score 14 · Accepted Answer · answered Apr 23 '12 at 12:53

14

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.

answered Apr 23 '12 at 12:53

Eric Petroelje

59,820
9
127
177

Thanks for the answer it helps me & yes sorry for late reply. – Tony Stark Mar 07 '13 at 13:22
@ArunVM - if so, please post an answer on how that can be done. I know it can be done for the server, or for individual tables, but not for a database. – Eric Petroelje Dec 13 '18 at 16:02

score 8 · Answer 4 · answered Apr 23 '12 at 12:51

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.

score 4 · Answer 5 · answered Sep 03 '14 at 02:39

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

score 1 · Answer 6 · answered Apr 30 '16 at 12:13

1

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

answered Apr 30 '16 at 12:13

Benson K B

43
1
10

score 0 · Answer 7 · answered Apr 19 '17 at 15:26

0

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

Changing storage engine in PhpMyAdmin

answered Apr 19 '17 at 15:26

Jhonattan Peláez

516
4
6

MySql - Convert InnoDB to MyISAM Storage Engine of Database

7 Answers7

Linked