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 '14 at 22:01

score 4 · Answer 1 · edited Apr 13 '17 at 12:42

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 Answers1