cPanel KnowledgeBase and Troubleshooting, cPanel Server Fixes and Tutorials

We can use various methods to change database engine. In order to convert Database ENGINE from InnoDB to MyISAM use the following methods and shell scripts.

First you should take all the databases backup.

root@serv [~]#mysqldump DBname > DBBacupname.sql

Method : 1

Once you secure the Database you may create a file called DBlist.txt and add databases which you need to convert from InnoDB to MyISAM.

Create file and add your databases.

root@serv [~]#vi /root/DBlist.txt
Create file and add the following script.

root@serv [~]#vi convertdb.sh

#!/bin/bash
MYSQLCMD=mysql

for db in `cat /root/DBlist.txt`; do
for table in `echo show tables | $MYSQLCMD $db | grep -v Tables_in_`; do
TABLE_TYPE=`echo show create table $table | $MYSQLCMD $db | sed -e’s/.*ENGINE=\([[:alnum:]\]\+\)[[:space:]].*/\1/’|grep -v ‘Create Table’`
if [ $TABLE_TYPE = "InnoDB" ] ; then
mysqldump $db $table > $db.$table.sql
echo “ALTER TABLE $table ENGINE = MyISAM” | $MYSQLCMD $db
fi
done

done

Save and Execute the script after changed permission to 755.

root@serv [~]#sh convertdb.sh

 

Method : 2

Use the following php script to change database ENGINE from InnoDB to MyISAM

<?php

// connect your database here first

mysql_connect('host', 'user', 'pass');

$databases = mysql_query('SHOW databases');

while($db = mysql_fetch_array($databases)) {
echo "database => {$db[0]}\n";
mysql_select_db($db[0]);

$tables = mysql_query('SHOW tables');

while($tbl = mysql_fetch_array($tables)) {
echo "table => {$tbl[0]}\n";
mysql_query("ALTER TABLE {$tbl[0]} ENGINE=MyISAM");
}
}

 

How to change DATABASE engine to InnoDB?

 

You can use same above methods to INNODB instead of MyISAM.

Otherwise, You can change manually with below is a syntax to change storage engine to InnoDB.

ALTER TABLE `table_name` ENGINE=INNODB;
ALTER TABLE `table_name` ENGINE=INNODB;