3

I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to remain on the slave?

1
  • 1
    I would suggest don't use native replication. Keep the slave up to date with triggers or with custom polling code that runs on a schedule. Commented May 17, 2012 at 11:09

2 Answers 2

3

If you're just going to delete this extra data manually on the odd occasion, you can simply turn off the binary logging for the current session. On the MySQL prompt run the following:

set sql_log_bin=0;

Then any query you enter won't be sent to the binary log, and thus won't be sent across to the slaves.

Once you're finished, re-activate binary logging with

set sql_log_bin=1;

Reference: MySQL manual

-1

Solution for PostgreSQL:

We can avoid using rules

- Stop slave daemons
- Create ON DELETE and ON UPDATE DO INSTEAD NOTHING

CREATE RULE void_delete AS ON DELETE TO reptest DO INSTEAD NOTHING

- rule Apply RULE on table

ALTER TABLE reptest ENABLE REPLICA RULE void_delete;

Now, you can notice below that DELETE has no impact on Slave Node


EDIT

For MySQL we can create Trigger like:

DELIMITER $$

CREATE TRIGGER PreventDeletion
BEFORE DELETE ON table1
FOR EACH ROW
BEGIN

  IF old.id IN (1,2) THEN -- Will only abort deletion for specified IDs
    SIGNAL SQLSTATE '45000' -- "unhandled user-defined exception"
      -- Here comes your custom error message that will be returned by MySQL
      SET MESSAGE_TEXT = 'This record is sacred! You are not allowed to remove it!!';
  END IF;

END
$$

DELIMITER ;
4
  • @ypercubeᵀᴹ i edited my answer, Previous one for postgresql so i added for mysql, Here we can avoid deletion using trigger Commented Feb 5, 2016 at 6:03
  • OK. Will this trigger allow the rows to be deleted on master but kept on slave? Commented Feb 5, 2016 at 7:44
  • @ypercubeᵀᴹ i think we have to add "slave-skip-errors = 45000" in slave my.cnf file. Then replication will be continued and records are exist on slave. But its not tested, will get-back to you asap after testing. Commented Feb 5, 2016 at 10:05
  • It's working fine Commented Feb 7, 2016 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.