How to convert InnoDB to MyISAM

Although at times faster, you will find InnoDB a very large headache when it comes to table corruption and may want to convert to MyISAM. First backup your databases. Do note that some databases like WordPress may require innoDB. Be sure to backup your databases before hand. Once you have performed this, the following commands may be useful in converting InnoDB to MyISAM:


mysql -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'"

/etc/init.d/mysql stop
mkdir -p /root/innodb.bak
mv /var/lib/mysql/ib* /root/innodb.bak/
/etc/init.d/mysql start


To restore use the following:

 


cp /root/innodb.bak/* /var/lib/mysql
echo innodb_force_recovery=4 >> /etc/my.cnf
service mysql restart

 


Share this page:


Was this article useful? Have any suggestions or improvements to the information?

Also Read

Failed to lock proc mutex: Permission denied

The error : Failed to lock proc mutex: Permission denied can occur when using mod_ruid2 with...

central_filter defer (-17): error in filter file: unknown filtering command "r_subject:" near line 22 of filter file

The error "central_filter defer (-17): error in filter file: unknown filtering command...

Remount /tmp with exec permission

There are times where you may need to have exec permission available for the /tmp folder. This...

Nginx redirecting folder to another host or server

From time to time you may want to proxy out a path to another server or location. To perform a...

This webpage is not available ERR_SSL_VERSION_OR_CIPHER_MISMATCH

This webpage is not availableERR_SSL_VERSION_OR_CIPHER_MISMATCHA secure connection cannot be...