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?

Answer 1

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