Preventing replication of alter table command to change engine

Question

I am in the process of switching a MySQL table from MyISAM to InnoDB and I do not want the slave to perform the alter table command until I execute it manually. The intention is to have a backup for a few hours in case there is an issue that I have not addressed already. Is there a way to tell the MySQL slave to not process alter table commands from the Master for a period of time?

Answer 1

If you want to prevent any ALTER TABLE ... ENGINE=InnoDB; from executing on a Slave, just run this on the Master:

SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;

What this does is prevent the Master from recording the ALTER TABLE ... ENGINE=InnoDB; in its binary logs. Since the Slave only replicates from the binary logs of the Master, just don't record the commands that you do not want replicated.

If you have multiple tables to convert and not replicate, then you can do this:

SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
ALTER TABLE ... ENGINE=InnoDB;
.
.
.
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;