MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Can someone tell me how to prevent the following commands

DROP TABLE ;
 TRUNCATE TABLE ;

from being replicated to slaves?

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 !!!

StackExchange Database Administrators Q#62331, answer score: 4