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

 

  • 116 Users Found This Useful

Was this answer helpful?

Related Articles

VPS Time not Correct with Time Server

If you have a problem with your VPS time not staying sync in a Xen VPS you will want to enable...

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

Clear cPanel Eximstats DB and Repairing the Eximstats DB

Over the time or due to spammers the eximstats database file may grow to a large size. To clear...

Create CXS Quarantine Directory

The following command may be used to create a quarantine directory for Configserver's CXS: cxs...

error: db3 error(-) from dbenv->failchk: DB_RUNRECOVERY: Fatal error, run database recovery

error: db3 error(-) from dbenv->failchk: DB_RUNRECOVERY: Fatal error, run database recovery...