1. Yes ‘SQL_LOG_BIN’ will work for any number of Slaves, as this variables make sure that the event is not written to Binary Log.

2. For getting the list of Slaves, you can also run following statement in Master:

show slave hosts;

Best Regards
Gopal

1. You mentioned to disable bin logging before any DML that requires significant time. Can we apply the same if we have one
Master – 10 Slaves ? If I want to change engine from Innodb MyISAM or vice versa , Would you suggest the same
way or pt-online-schema-change will be better ?
2. Is there any way i can get to know how many slaves are in Master Slave replication. Will this work :

select distinct host from information_schema.processlist WHERE user = ‘slave_user’
If all slaves are not connected to master at the time when i fired this command.

Looking forward to your reply.

Thanks

The problem was that master was able to handle about 4k queries per second while due to single-thread nature of replication, slave was able to read and execute only around 600 queries per second (no network issue as MASTER_LOG_FILE in slave status almost remains same as that on master being written currently and there are plenty of pending relay logs).

Can anybody suggest a workaround apart from hardware upgrade?

I also have experienced that before and I have wasted my time looking for the replication issue I until I got an idea of check server clock.

SQL_LOG_BIN which Peter mentioned is a session variable. Any statement you run in that thread will not end up in the binary log, thus not be replicated. So rather than skipping a transaction on all the slaves, you simply don’t log it on the master.

In terms of promoting slaves, it’s not entirely necessary to get them to the same binlog position. If you as per your previous comment have the luxury of being able to ensure that no updates hit the DBs, you can simply do it with a series of CHANGE MASTER statements whilst you are certain that the DBs have the same data (ie. the writes doesn’t go through and all slaves are caught up).

I’ve seen you talk about promoting a slave to be the new master before, but in my eyes that has always seemed like a huge pain that is full of potential pitfalls. We run with one master and 4 slaves, the master does about 20k questions a minute and the slaves each do about 11k. How hard is it to synchronize them to the same master bin log position? FWIW, our site wasn’t down for the above index update, we have an intermediate proxy layer that acts as a write-through cache and queued the writes until the master could handle them.

You’re completely right. Even though MySQL Replication is great in its ability to replicate DDL too it is often not practical because of replication lag it causes and other reason.

It often makes sense to use SQL_LOG_BIN=0 to run query only on master (or start from the slave). Plus this often allows you to do massive DDL without taking sight down – you just run it on the slave, promote it to the master and run on ex-master.