MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Question

Can someone tell me how to prevent the following commands

 DROP TABLE <tbl-name>;
 TRUNCATE TABLE <tbl-name>;

from being replicated to slaves?

Out of curiosity, could you elaborate why you wish to do this? Generally you want your slave to be 1:1 with your master. — atxdba, Oct 15, 2014 at 22:01

Community · Accepted Answer · 2017-04-13 12:42:47Z

Disable binary logging in your session, drop the table, and enable binary logging.

SET sql_log_bin = 0;
DROP TABLE IF EXISTS ... ;
SET sql_log_bin = 1;

This works because if the Slaves do not see the DROP TABLE command registered in the Master's binary logs, it cannot run the DROP TABLE.

I have recommended using SET sql_log_bin = 0; before in my earlier posts

Nov 12, 2012 : Mysql replication and ignore tables
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Jul 11, 2011 : ALTER TABLE on a large table with an indexed column (This is where I use SET sql_log_bin = 0; to directly prevent data from being written to Slaves)

Give it a Try !!!

1 Answer 1