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

Failed to lock proc mutex: Permission denied

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

Whitelisting Multiple IPs with Mod_Security

Whitelisting Multiple IPs in ModSecurity   If ModSecurity is blocking legitimate requests...

Recursive chmod / Permission Change Across Directories for a File Type

Recursive Permission Changes Across Directories   To change permissions recursively across...

Repairing and Optimizing all MySQL and MariaDB Databases on the Server

From time to time you will find repairing and optimizing your databases are necessary. From a...

Error: rpmdb open failed with cPanel

Fixing RPM Database Errors in cPanel   The error "rpmdb open failed" occurs when the RPM...



Save 30% on web hosting - Use coupon code Hosting30