Cancel DELETE on MySQL REPLICATE

Question

I'm looking for a way to prevent DELETE statement on a MySQL replication Master/Slave.

In my case, the Master is a live database, with fresh entries (not older than a week), and the Slave is an archive database which must contains all entries.

I have several problems with my test:

• If I raise an exception in a Slave BEFORE DELETE trigger, like SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DELETE canceled';

The SQL exception raise an error and stop my Slave.

• If I raise a warning, the Slave keep running but the delete is not canceled
• I can't modify the my.cnf of MySQL.
• I can't use a boolean attribute for hide on master, show on slave (Master database must be as little as possible).

I rack my brain on this since a few days, and I 'm running out of ideas ...

Answer 1

You would be better off writing the deletes to an audit table.

Problem with preventing deletes in slaves is: if you try to insert a row with a pk which is already deleted in the master and if you have somehow prevented the delete in slave, the insert will fail in the slave.

You can track deleted rows in a different table with same structure.

http://www.techonthenet.com/mysql/triggers/before_delete.php

CREATE TRIGGER audit_before_delete
BEFORE DELETE
   ON yourtable FOR EACH ROW

BEGIN

   -- Find the deleted row and insert record into audit table
   -- insert into yourtable_audit values (old.id, old.name, old.description);

END;