Convert Database ENGINE from InnoDB to MyISAM
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;