Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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