Prevent Slave from replicating deletions

Question

My question is regarding Master-Slave replication on MySQL using linux server.

I am just testing something on replication in MySQL, I am wondering is it possible to prevent the slave from replicating delete statements?

I know the slave will replicate all inserts/deletes from the master. But I only want it to replicate the inserts.

Is this possible?

Before trying to answer - let me ask you what do think 'update' does ? — Up_One, Mar 13 '14 at 12:21
sorry, this will not update. only inserts and deletes at the moment. updated the question — jeffry, Mar 13 '14 at 12:23

score 2 · Answer 1 · edited May 23 '17 at 11:44

2

Refer this:

There are several ways to do this.

Run SET SQL_LOG_BIN=0; for the relevant session on the master before executing your delete. That way it is not written to the binary log
Implement a BEFORE DELETE trigger on the slave to ignore the deletes.

use approach #1 for statements that I don't want to replicate. It requires SUPER privilege.

edited May 23 '17 at 11:44

Community

answered Mar 13 '14 at 12:23

Sathish D

ok i want this to be done automatically, how do you propose i go about implementing approach 2? – jeffry Mar 13 '14 at 12:25
2

#2 is not possible. You can't stop an action within a query via a trigger without throwing an exception, which should in turn stop replication... and triggers on the slave do not fire anyway, for row-based binary log events, because the row changes that were caused by the trigger firing on the master are sent to the slave as row changes and the trigger action on the slave would be redundant. – Michael - sqlbot Mar 13 '14 at 18:15

score 0 · Accepted Answer · answered Mar 13 '14 at 12:24

0

Well it can be done:

using the SET SQL_LOG_BIN=0; for the delete session on the master before executing your delete. So will prevent from written to the binary log
Implement BEFORE DELETE/UPDATE/INSERT triggers on the slave tables to ignore the deletes.
Same rule applies to any of the statement !

answered Mar 13 '14 at 12:24

Up_One

ok i want this to be done automatically, how do you propose i go about implementing approach 2? – jeffry Mar 13 '14 at 12:25
Choose your objects and alter them accordingly to your needs! (create those triggers). – Up_One Mar 13 '14 at 12:26

2 Answers2