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

 

  • 114 Users Found This Useful

Was this answer helpful?

Related Articles

FSCK Options and Maintenance

Provided below are some useful commands pertaining to FSCK. Update commands to your environment /...

Killing cPanel Backups via Command Line / Shell

From time to time you may find the cPanel backup system taking longer and affecting production...

yum-utils not installed or found on Centos5 64-bit

When yum-utils is not installed or found on Centos5 64-bit the following commands may be used:...

FTP Failed: Service check failed to complete Unable to connect to port 21

The service “ftpd” appears to be down. The system failed to connect to this service’s TCP/IP...

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