Mysql – Doubts about Converting MyISAM to InnoDB (master/slave and more)

innodbmyisamMySQLmysql-5.5replication

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.

Best Answer

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;" > ${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

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?

Related Solutions

Mysql – Converting 3 Master + 1 Slave to 4 Master – Any advice

Here is the topology you just described

+-------------+
|             ^
|             |
V             |
M1 --> M2 --> M3
|
|
+----> S1

You would like to slip S1 as a Master into the Replication Ring so that it looks like this:

+---------------------+
|                     ^
|                     |
V                     |
M1 --> S1 --> M2 --> M3

Essentially, you only have to prep S1 to a Master and Point M2 to receive binary log entries from S1.

OK Here we go

STEP 01) Point your application at M3

STEP 02) Prep S1 to be a Master

set the server_id as a different number from M1, M2, M3
set log-slave-updates in my.cnf like you did for M1, M2, M3
enable binary logging on S1 the same way you enabled it on M1, M2, M3
restart mysql on S1

STEP 03) run STOP SLAVE; on M1

STEP 04) run SHOW SLAVE STATUS\G on S1 and M2

Make sure Seconds_Behind_Master is 0 on S1 and M2

STEP 05) run SHOW MASTER STATUS; on S1 (Record the binary log and position)

STEP 06) run this on M2

STOP SLAVE;
CHANGE MASTER TO master_host='IP Address of S1',
master_post=3306,
master_user='repluser',
master_password='replpass',
master_log_file='XXXX'
master_log_pos=YYYY;
START SLAVE;
SHOW SLAVE STATUS\G

where XXXX is the binary log from STEP 05 and YYYY is position from STEP 05

If the SHOW SLAVE STATUS\G says Yes for Slave_IO_Running and Slave_SQL_Running then you have achieved this:

+---------------------+
                      ^
                      |
                      |
M1 --> S1 --> M2 --> M3

STEP 07) run START SLAVE\G on M1

STEP 08) run SHOW SLAVE STATUS\G on M1, S1, M2, M3

Where Seconds_Behind_Master is 0 on all the servers...

STEP 09) Point you application to other servers as desired

Any questions ???

If none

Give it a Try !!!

Your mission, should you decide to accept this, is to practice this is a Dev/Staging Environment and make sure you trust this algorithm before doing this in Production.

In the event your data is caught or killed, the DBA StackExchange and I will disavow any knowledge of your actions.

Mysql – Safely promoting master to slave

When you shifted the IP within your application, any DB Connections that were open at the moment were totally unaware of the move. A quick netstat | grep -i mysql would reveal that on Master1.

You would be better off doing the cutover of the IP as follows:

On Master1
- FLUSH HOSTS;
- service mysql stop
Change the IP in the App
On all web servers, service httpd restart

In order to safeguard the app from having to edit it for the sake of assigning a new IP, try using a DB VIP instead.

For example:

Master1 is 10.1.2.30
Master2 is 10.1.2.40
Use 10.1.2.70 as the DBVIP

Here is what you can setup

On Master1, run ip addr add 10.1.2.70/24 dev eth1
Use 10.1.2.70 in your app

Then, when it is time to cutover, do the following

On Master1
- FLUSH HOSTS;
- service mysql stop
- ip addr del 10.1.2.70/24 dev eth1
On Master2
- ip addr add 10.1.2.70/24 dev eth1
- SHOW SLAVE STATUS\G and make sure all final SQL statements from Master1 executed
On all web servers, service httpd restart

That way, a cutover would not involve editing any part of the app. Please notice that I did not mention running STOP SLAVE anywhere because this would allow any final SQL statements to flow over from Master1 to Master2 once mysql is stopped on Master1.