Activities

October 2014
M T W T F S S
« Sep   Nov »
 12345
6789101112
13141516171819
20212223242526
2728293031  

How do I convert all MyISAM tables to InnoDB storage engine

One of my client needs to improve the sql query performance as well as to gain better performance on his platform. So I’m planning to convert all the 543 tables to convert InnodDB storage engines to use the 15GB RAM added to the server. It’s very hard to execute the “ALTER TABLE <table _name> ENGINE = InnoDB;” each time for these 543 tables.

So I’m planing to use an approach (shell script) as I’m a lazy guy to do the repeated jobs.
Identify available storage engine,

[root@OR-Web01 onrevenue]# mysql -e 'show engines;' | awk ' { print $1 }'
Engine
MRG_MYISAM
CSV
MEMORY
BLACKHOLE
MyISAM
InnoDB
ARCHIVE
PERFORMANCE_SCHEMA
FEDERATED

Create a mysql client configuration file for NOT asking password each time, ~/.my.cnf which having the lines lines shown below,

cat ~/.my.cnf
[client]
host="db_hostname"
user="dbusername"
password="dbpass"

Here is the script,don’t forget to mention your database in it.

#!/bin/bash
DB="Mydatabase"
TABLES=$(mysql --skip-column-names -B -D $DB -e 'show tables')
for T in $TABLES
do
echo "Converting the table $T....... "
mysql -D $DB -e "ALTER TABLE $T ENGINE=InnoDB"
clear
done

How do I verify the storage engine of a database.

You may use this sql query SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = ‘database_name’;

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'tstdatabase';
+-------------------------------------------------------+--------+
| TABLE_NAME                                            | ENGINE |
+-------------------------------------------------------+--------+
| jos_account_opportunity_reminder                      | MyISAM |
| jos_account_types                                     | MyISAM |
| jos_all_facebook_access_token                         | MyISAM |
| jos_all_facebook_apps                                 | MyISAM |
| jos_all_message_center_notes                          | MyISAM |
| jos_appointment_details                               | MyISAM |
| jos_appointment_display_option                        | MyISAM |

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>