Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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 !!!

share|improve this answer
    
thanks for your professional answer –  user209827 Feb 24 at 4:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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