Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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. – Aaron Bertrand May 17 '12 at 11:09

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

share|improve this answer

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 ;
share|improve this answer
    
@ypercubeᵀᴹ i edited my answer, Previous one for postgresql so i added for mysql, Here we can avoid deletion using trigger – visabhishek Feb 5 at 6:03
    
OK. Will this trigger allow the rows to be deleted on master but kept on slave? – ypercubeᵀᴹ Feb 5 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. – visabhishek Feb 5 at 10:05
    
It's working fine – visabhishek Feb 7 at 18:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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