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;

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;
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s