Not getting into the whole which engine is the best debate, since obviously, like most important questions in life, the answer is: “It depends.”
In addition, most of what is written here can be used to convert from any engine to any engine, and thus answering any specific needs your might have.
I needed to convert MyISAM to InnoDB so here it is.
Without further ado…
As far as I know, there are two basic ways to accomplish this:
-
ALTER TABLE 'tablename' ENGINE = InnoDB
For each table in the database.
- Dump the table, edit the dump file where it says “CREATE TABLE” and fix to the preferred engine, and then reload the data back into the DB
Obviously, these methods are ok when you are dealing with a few tables, however a while ago we had a client with a huge database with over 50 tables, so of course, a script was in place.
As this was quite quick and dirty, don’t be alarmed by some wtfs and a little bit of hardcoding…
However there is a little something I am proud of there, and for those who are interested, read below the code.
BTW: PLEASE BACKUP YOUR DBS IF THEY ARE IMPORTANT TO YOU BEFORE RUNNING ANYTHING. EVEN THOUGH THIS CODE HAS BEEN TESTED ON MORE THAN ONE OCCASION, THINGS MAY GO NUTS ON YOU.
#
# FILENAME = myisam_to_innodb.sh
#
#
###########################################################################
# #
# mysql engine change for tables in specified database (myisam -> innodb) #
# #
###########################################################################
dbname=$1
# check user input
if [[ $dbname == "" ]]; then
echo "USAGE: ./myisam_to_innodb.sh DBNAME"
exit
fi
# get list of tables for the DB
table_names=$(mysql -e "show table status from $dbname" --skip-column-names | awk '{print $1}')
table_engs=$(mysql -e "show table status from $dbname" --skip-column-names | awk '{print $2}')
# populate arrays
arr_index=1
for table_name in $table_names; do
tables_names_array[$arr_index]=$table_name
# echo ${tables_names_array[$arr_index]} # for testing
let "arr_index+=1"
done
arr_index=1
for table_eng in $table_engs; do
tables_engs_array[$arr_index]=$table_eng
# echo ${tables_engs_array[$arr_index]} # for testing
let "arr_index+=1"
done
no_of_elements="${#tables_engs_array[@]}" # special syntax to get number of elements in array
# loop on tables and display - check if anything to do
for (( i=1; i<=$no_of_elements; i++ )); do
echo ${tables_names_array[i]}
if [[ ${tables_engs_array[i]} == "MyISAM" ]]; then
echo "${tables_names_array[i]} is a MyISAM table! Adding to todo list."
todo="$todo \n $(echo ${tables_names_array[i]})"
else
echo "${tables_names_array[i]} is not a MyISAM table - doing nothing."
fi
done
# verify that there is anything to do
if [[ $todo != "" ]]; then
while [ 1 ]; do
# make sure user approves
echo "Are you sure you want to convert the following tables to InnoDB? [Y/N]"
echo -e $todo
read ANSWER
if [[ $ANSWER == "N" || $ANSWER == "n" ]]; then
echo "you said no, quitting..."
exit
elif [[ $ANSWER == "Y" || $ANSWER == "y" ]]; then
echo "you said yes, let us continue..."
break
else
echo "i don't understand, please try again"
continue
fi
done
# HERE IS WHAT HAPPENS IF USER SAYS YES
for table in $(echo -e $todo) ; do
# echo "simulating conversion of $table" # for testing
mysql $dbname -ve "ALTER TABLE $table ENGINE = InnoDB"
done
else # This is what happens if "todo" is empty
echo "Nothing to do"
exit
fi
I left in all of those commented out testing lines, because they are very useful when debugging, and also they show the not-so-trivial method of printing out array items in bash.
I will now do a little break down of the script:
# check user input
if [[ $dbname == "" ]]; then
echo "USAGE: ./myisam_to_innodb.sh DBNAME"
exit
fi
# get list of tables for the DB
table_names=$(mysql -e "show table status from $dbname" --skip-column-names | awk '{print $1}')
table_engs=$(mysql -e "show table status from $dbname" --skip-column-names | awk '{print $2}')
First of all we the get name of the DB we want to convert tables in from the command line.
In case someone forgets to enter the DB name, or doesn’t know how to use the script, we do a little USAGE printout for convenience. I like finding scripts I did a million years ago and then just running them, and finding out there is a little usage thing as a reminder, before I actually dive in to figure them out again.
Afterward, we store in the two variables $table_names and $table_engs lists of the table names and engines respectively.
Of course running the same query twice for retrieving different parts of its data is not the most elegant solution, however it was the best I could come up with on that particular occasion, and given that bash sometimes stores output from mysql queries run from the command line in very nasty ways.
Not to mention all kinds of horrors regarding string manipulation in bash when dealing with data stored in variables.
At this point, I am assuming that the reader has some basic notion of mysql queries, and basic awk functions like ‘print’ so I won’t go into too much detail regarding these matters (at least not in this post).
Anyways, moving on.
arr_index=1
for table_name in $table_names; do
tables_names_array[$arr_index]=$table_name
# echo ${tables_names_array[$arr_index]} # for testing
let "arr_index+=1"
done
arr_index=1
for table_eng in $table_engs; do
tables_engs_array[$arr_index]=$table_eng
# echo ${tables_engs_array[$arr_index]} # for testing
let "arr_index+=1"
done
This is a nice trick that I had to invent out of necessity, since, there is no such thing as an associative array in bash (a quick Google search on the subject, produces the result “How To Fake Associative Arrays In Bash” and that wasn’t exactly helpful in this case).
What we have here, are two regular indexed arrays, tables_names_array and tables_engs_array, but the special thing is that they were populated from the same list, in the same order, so basically each item correlates to its respective item in the other array, according to its index, thus simulating an associative array.
In the next part, we make use of this ugly/beautiful hack…
# loop on tables and display - check if anything to do
for (( i=1; i<=$no_of_elements; i++ )); do
echo ${tables_names_array[i]}
if [[ ${tables_engs_array[i]} == "MyISAM" ]]; then
echo "${tables_names_array[i]} is a MyISAM table! Adding to todo list."
todo="$todo \n $(echo ${tables_names_array[i]})"
else
echo "${tables_names_array[i]} is not a MyISAM table - doing nothing."
fi
done
The $no_of_elements variable is frightening, I know, however bash stores many surprises for the unknowing…
Anyways, this part loops over the elements in both arrays (with the correlating indexes as stated earlier), and prints out the relevant tables (those whose engine is still MyISAM) for the user to confirm.
if [[ $todo != "" ]]; then
while [ 1 ]; do
# make sure user approves
echo "Are you sure you want to convert the following tables to InnoDB? [Y/N]"
echo -e $todo
read ANSWER
if [[ $ANSWER == "N" || $ANSWER == "n" ]]; then
echo "you said no, quitting..."
exit
elif [[ $ANSWER == "Y" || $ANSWER == "y" ]]; then
echo "you said yes, let us continue..."
break
else
echo "i don't understand, please try again"
continue
fi
done
# HERE IS WHAT HAPPENS IF USER SAYS YES
for table in $(echo -e $todo) ; do
# echo "simulating conversion of $table" # for testing
mysql $dbname -ve "ALTER TABLE $table ENGINE = InnoDB"
done
else # This is what happens if "todo" is empty
echo "Nothing to do"
exit
fi
Here we simply handle the user’s approval (notice the user-friendly case-insensitivity) and finally do the mysql query for each relevant table.
I know it is a bit long a tedious for a relatively simple task, however I know I learned quite a bit creating this, and also it eventually saved me and my team an unthinkable amount of time, considering the manual option.
I hope you also found something useful in this entry!
Hello,how can i run the above script.it seems a really good job and exactly what i want to do but i can’t understand how to use it.if i paste it in an .sh file doesn’t work
thanks in advance
Link | August 7th, 2009 at 14:52
First of all, sorry for the delay in the reply.
After pasting to the .sh file, did you assign the proper executable bit (i.e.
chmod 755 script.sh)?In addition, check the the “USAGE” section for the exact syntax – which is simply the name of the script and then the name of the database which you wish to convert tables in.
Hopefully you get it working! I would be happy to hear if you managed to work it out.
Tom.
Link | August 10th, 2009 at 19:41