cPanel Server Management
For 24×7 cPanel Server Management with Monitoring, Unlimited Tech Support, Disaster Recovery, Security updates and much more Signup today!
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
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;
cPanel Server Management
AssistanZ is a pioneer in offering 24×7 cPanel Server Management Service since 2004. We offer 24×7 cPanel Server Management
cPanel Server Management
cPanel Installation and Setup
cPanel Server Optimization
cPanel Server Migration
cPanel Server Security and Hardering
cPanel Server Monitoring
cPanel Server Hack Recovery
and Much more
