MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

MySQLmysql-5.1replication

Can someone tell me how to prevent the following commands

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

from being replicated to slaves?

Best Answer

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

Related Solutions

Mysql – pt-table-checksum with slaves using non-standard ports

The issue is that the replicas don't report their ports to the master. By default pt-table-checksum tries to connect by filling unknowns with defaults; if the unknowns aren't using defaults, this will fail. In this case, there are two options:

1) Use the --recursion-method option to change from inspecting SHOW PROCESSLIST to inspecting SHOW SLAVE HOSTS, and configure slaves to report their host. I don't recommend this.

2) I recommend, instead, that you use --recursion-method=dsn and create a table full of DSN (data source name) instructions that tell the tool how to connect to each replica. If this table is foo.bar, then you will use --recursion-method=dsn=D=foo,t=bar.

This is only available in the 2.0 series of the toolkit, but you should be using that anyway, because pt-table-checksum is hugely improved in 2.0.

Mysql – What can cause TRUNCATE TABLE to take a really long time

Using TRUNCATE TABLE on an InnoDB table requires a full table lock because TRUNCATE TABLE is DDL (Data Definition Language) not DML (Data Manipulation).

Doing DELETE FROM user_engagements; will not help because MVCC info is written to the undo logs in ibdata1, and that can hold up the table from being emptied. If any uncommitted transactions are holding onto user_engagements, that could potentially hold up a TRUNCATE TABLE as well.

You could rename the table so that it is immediately available

SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE user_engagements_new LIKE user_engagements;
ALTER TABLE user_engagements RENAME user_engagements_zap;
ALTER TABLE user_engagements_new RENAME user_engagements;
DROP TABLE user_engagements_zap;
SET FOREIGN_KEY_CHECKS = 1;

This should replicate quickly except for the last statement.

Give it a Try !!!

If you have MySQL 5.1.16+, TRUNCATE TABLE requires DROP privilege. My answer performs what TRUNCATE TABLE now does.

If you have MySQL 5.1.15 and back, you need DELETE privilege, which my answer covers.

Best Answer

Related Solutions

Mysql – pt-table-checksum with slaves using non-standard ports

Mysql – What can cause TRUNCATE TABLE to take a really long time

Related Question