Can I configure MySQL replication to break when I run a "drop database" or "drop table" command on the master?

Basically, what the title says. This would enhance the failover quality of our systems, becuase we have run into issues of people thinking that they were in a test environment running drop statements on the master, which then (obviously) resulted in data loss.

Instead, I was wondering if there was a db option to just have replication refuse to run the drop table command, and have replication stop running at that point. (assume statement based replication for now). MySQLs FAQs seem to indicate that the answer to this is 'no'.

The answer to your question is no -- the replication SQL thread implicitly has the SUPER privilege, so any command that was entered in the binary log can be executed by the slave.

You might be able to help your scenario by setting up a slave that is deliberately lagging behind the master. That way if someone accidentally drops or deletes something they shouldn't, you have a grace period to go to the slave, stop the replication thread, and run a backup of the data that was dropped.

In MySQL 5.6, you can configure a slave with delay:

mysql> CHANGE MASTER TO MASTER_DELAY=3600; /* 1 hour delay */

In MySQL 5.5 and earlier, you can simulate this behavior with the free tool pt-slave-delay.

But I agree with the comment from @Phil -- if you have people working on the production master database who can't be careful about which instance they are using before they run DROP TABLE, then you should revoke their privileges.

Another tip: you can set the MySQL client's prompt to indicate which host they're connected to. Edit $HOME/.my.cnf:

[mysql]
prompt = mysql[\h]>

StackExchange Database Administrators Q#58900, answer score: 4