how to change storage engine of database in phpmyadmin?

Question

I have a MySQL database. The default storage engine is MyISAM.

I want to change it to InnoDB.

I can change it table by table , but i want to change it one time that affect all the tables .

How can I change the database storage engine? I tried it but seems there is no option in phpmyadmin.

Gurey · Answer 1 · 2014-01-16T10:28:12.393

32

The easiest way to change the default engine is to log on phpMyAdmin and then go to Variables >> storage engine

click edit and type InnoDB.

the default storage engine is now InnoDB

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_storage_engine

edited Jan 16 '14 at 10:28

answered Jan 16 '14 at 10:07

Gurey

432
5
12

Flag -MySQL storage engine- link purposes ~ignore message~ – Luis Feb 10 '16 at 17:39
FYI; This works with WAMP.. for XAMPP find it in Operations tab. Thanks @Gurey – Nevermore Jul 13 '16 at 08:00

score 20 · Accepted Answer · answered Aug 03 '11 at 04:12

20

When logged in go to the table you want to change then click the operations tab. From there you can change the storage type, as well as a few other options

Online Doc

answered Aug 03 '11 at 04:12

nidhin

6,661
6
32
50

actually i want to change the database storage engine one time that can affect all the tables . not table by table . – Kanishka Panamaldeniya Aug 03 '11 at 04:57
You can set it at the time of database creation – nidhin Aug 03 '11 at 04:58
where i can set the storage engine when creating the database , i couldn't find it . – Kanishka Panamaldeniya Aug 03 '11 at 05:13
Please refer http://stackoverflow.com/questions/2286813/how-to-set-default-storage-engine-innodb-in-xampp-for-linux1-7-3a It's not using phpmyadmin though – nidhin Aug 03 '11 at 05:17

score 8 · Answer 3 · edited Apr 11 '13 at 14:31

8

Steps:

Click on the table for which you have to change the storage engine.
Click on operations.
Option to change the storage engine is inside the field set

edited Apr 11 '13 at 14:31

Hugo Dozois

8,147
12
54
58

answered Apr 11 '13 at 14:15

Nil Patil

81
1
3

score 2 · Answer 4 · answered Aug 03 '11 at 05:34

the best answer i got was

The easiest way is just to go to the mysql config file (my.ini in windows, my.cnf in Linux) and just add this: [mysqld] default-storage-engine=InnoDB .

i think that will do the trick easily .

thanks for the help........... :D

score 0 · Answer 5 · answered Dec 31 '12 at 20:50

0

Click on the table name, then click on operations, there you can change the server engine one table at a time.

A+ Computer Solutions- Belize

answered Dec 31 '12 at 20:50

Earlin THimbriel

1

3

Signatures are bad form. – Corey Adler Dec 31 '12 at 21:06

score 0 · Answer 6 · answered Aug 03 '11 at 05:05

0

There is no option in phpMyAdmin to change the storage type at a time. You have to change each table in operations tab.

answered Aug 03 '11 at 05:05

James

19
1
4

2

Then you can export the tables and create one new DB with new storage type and import it. – James Aug 03 '11 at 05:06
Some versions of phpMyAdmin have this option. I just found out. My version 3.5.8.2 didn't have this option. – machineaddict Nov 21 '14 at 09:21

how to change storage engine of database in phpmyadmin?

6 Answers6

Linked