How do we exclude SQL from replicating in MySQL

October 15, 2014 · by · in MySQL
If you’re using MySQL Replication, in rare cases you may require to skip a particular SQL statement from replicating – which is to be executed on Master and should not be on the Slave.
Though, its not a good practice!
However, here’s how to do it.
MySQL has a variable sql_log_bin, by default its value will be ‘ON’ or ‘1’, when we enable replication.
That means, it replicates all the SQL statements from Master to Slave.So, if we want to disable this for a particular SQL, do as below:

SET SESSION sql_log_bin=0; sql statement;SET SESSION sql_log_bin=1;

Example:
SET SESSION sql_log_bin=0;

DELETE FROM tab1 WHERE col1=’NULL’;

SET SESSION sql_log_bin=1;

However, after MySQL 5.5.5 version, no need to use SESSION. By default its a SESSION variable and it won’t affect other user’s transactions.

Note: Executing this on Master would cause data inconsistency. Don’t try this unless you are sure about what you are doing!

  • Ask Question