HOW DO WE EXCLUDE SQL FROM REPLICATING IN MYSQL

 

If you’re using MySQL Replication, in few 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;
ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type>;
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.

  • Ask Question