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

 

  • 118 Users Found This Useful

Was this answer helpful?

Related Articles

ModSecurity: collection_store: Failed to access DBM file /var/cpanel/secdatadir/ip: Permission denied

The error Failed to access DBM file /var/cpanel/secdatadir/ip: Permission denied can occur on...

Whitelisting Multiple IPs with Mod_Security

The following can be added to the main modsecurity.conf or a whitelist file such as one...

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

Permission denied: /home/username/ htaccess pcfg_openfile: unable to check htaccess file, ensure it is readable

When checking your error log you find the error 508 or 403 forbidden error in your browser and...

Killing cPanel Backups via Command Line / Shell

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