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

Disable cPanel Brute Force Command Line

To disable the cPanel brute force detection from the command line usee the following:...

SpamAssassin Command Line Bayesian Learn Command cPanel

Once it is enabled, it is necessary to train spamassassin to learn what is junk mail and what is...

Recursive chmod / Permission Change Across Directories for a File Type

The following command may be very useful for changing the permissions of a file type such as .php...

CSF Error: *WARNING* Binary location for [HOST] [/usr/bin/host] in /etc/csf/csf.conf is either incorrect, is not installed or is not executable

During a CSF start or restart you may encounter this error. This is a result of /usr/bin/host...

Killing cPanel Backups via Command Line / Shell

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