Doubts about Converting MyISAM to InnoDB (master/slave and more) I want to convert my MySQL DB (46 Tables, 2.1 GB) from MyISAM to InnoDB. I have a couple of questions about this process.


Since I have a Master/Slave structure, do I need to manage this and change the Slave also? Or any structural changes will directly affect the Slave?
Do I have to stop the slave before the conversion?
I'm using MySQL 5.5.32 (on ubuntu 12.04). Should I update MySQL (to 5.6.*) before the conversion?
I use only stored procedure to run queries. Is there something I need to change for the new engine?


If you have any other suggestions, feel free to share them with me.
ANSWERS TO YOUR QUESTIONS


  1) Since I have a Master/Slave structure, do I need to manage this and change the Slave also? Or any structural changes will directly affect the Slave?


I would recommend you change the Master to all InnoDB and leave the Slave all MyISAM

You will definitely need to make configuration changes since InnoDB has more a lot more moving parts in memory and disk.


  2) Related to n.1, do I have to stop the slave before the conversion?


You can disable binary logging in your session and convert all tables on the Master without replicating any of it to the Slave. Therefore, you do not need to run STOP SLAVE;


  3) I'm using MySQL 5.5.32 (on ubuntu 12.04). Should I update MySQL (to 5.6.*) before the conversion?


You need to upgrade either way. Why? I wrote a post describing how Oracle had a security path installed in MySQL 5.5.40 and 5.6.21 (See my post Find MySQL Patch Level)


If you want to remain with MySQL 5.5, upgrade to MySQL 5.5.40 or greater
If you want to upgrade to MySQL 5.6, upgrade to MySQL 5.6.21 or greater



  4) I use only stored procedure to run queries. Is there something I need to change for the new engine?


No changes for stored procedures are necessary

I have discussed this before. Here a two of my posts where I describe having a Master and Slave with different engines:


Jan 15, 2013 : Using MyISAM for reading and InnoDB for writing data


Accepted Answer from Shlomi Noach
My Answer

Aug 14, 2012 Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?


CONVERSION SCRIPT

Create a script that has these instructions. It will convert everything to InnoDB without replicating:

CONVERT_SCRIPT=/tmp/MassConvertRowFormat.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Query to Create SQL Script
# that Converts All Your MyISAM tables to InnoDB
#
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY data_length"
#
# 1. Disable the Binary Logging of Your Session
# 2. Append storage engine conversion statements
# 3. Execute the Script
#
echo "SET SQL_LOG_BIN = 0;" &gt; ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" &gt;&gt; ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} &lt; ${CONVERT_SCRIPT}


NOTE: Please do your homework and make changes to the InnoDB settings and log files before doing this conversion. Without do so, some have experienced slow performance because of the default settings of InnoDB

MY RECOMMENDED OLDER POSTS

Here are my other posts on how and why to tune InnoDB after (or before) converting from MyISAM


Aug 17, 2012 : When to switch from MyISAM to InnoDB?
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
Apr 11, 2014 : MySQL - Best long read query solution within a replication based system
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?