1

I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin

I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value

skip-innodb 

Afterwards, I found some errors in some php scripts in the server

Unknown table engine 'InnoDB'

I checked some databases in phpmyadmin i found in each database some tables IN USE

How can I make a successful conversion of all tables from InnoDB to MyISAM ?

1

Setting skip-innodb does disable the InnoDB Storage Engine, but that does not convert the tables.

You need to script the conversion of all the tables.

STEP #1

Comment out the skip-innodb

[mysqld]
#skip-innodb

STEP #2

service mysql restart

STEP #3

Execute the Following Script snf View Results

CONVERT_SCRIPT=/tmp/InnoDBConversionToMyISAM.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='InnoDB' AND table_schema <> 'mysql'"
mysql -uroot -p -ANe"${SQL}" > ${CONVERT_SCRIPT}

STEP #4

View the script

vi /tmp/InnoDBConversionToMyISAM.sql

or

less /tmp/InnoDBConversionToMyISAM.sql

If you like the contents, then ...

STEP #5

Login to mysql and run

mysql> source /tmp/InnoDBConversionToMyISAM.sql

STEP #6

Uncomment out the skip-innodb

[mysqld]
skip-innodb

STEP #7

service mysql restart

STEP #8

There is no STEP 8. That's it !!!

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.