2

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.

  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?

  2. Do I have to stop the slave before the conversion?

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

  4. 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.

1

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:

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;" > ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" >> ${CONVERT_SCRIPT}
mysql ${MYSQL_CONN} < ${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

  • Thanks Rolando! If I will leave the slave on MyISAM, do you suggest to change the ROW_FORMAT=Fixed? What do you mean in your note "change log files before the conversion"? – jjadams Mar 10 '15 at 22:37
  • Changing all MyISAM tables to use ROW_FORMAT=Fixed on the Slave is good under three conditions: 1) if you have a lot of VARCHAR columns, 2) If you do not use FULLTEXT Indexes, 3) If you have enough diskspace to potentially double the size of all MyISAM tables with breathing room afterewards. – RolandoMySQLDBA Mar 11 '15 at 1:41
  • I disagree. ROW_FORMAT=FIXED is virtually useless. You gain virtually no performance due to Fixed vs. Dynamic, while loosing a lot of performance due to extra I/O. And, if you have lots of large VARCHARs with mostly short text, it can cost more than 2x. – Rick James Mar 11 '15 at 4:18
  • Oh... that's hard :) What about the final suggestion "changes to the InnoDB settings and log files before doing this conversion"? I'm not sure I understood it. – jjadams Mar 11 '15 at 4:49
  • "changing settings": mysql.rjweb.org/doc.php/memory – Rick James Apr 30 '16 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.