7

I have been looking for a way to prevent MySQL delete statements from getting processed by the slave, I'm working on data warehousing project, and I would like to delete data from production server after having data replicated to slave.

what is the best way to get this done?

Thank you

Deano
  • 11,582
  • 18
  • 69
  • 119
  • you can exclude delete query please refer stac http://dba.stackexchange.com/questions/20020/how-would-i-run-delete-queries-on-a-master-without-executing-same-deletes-on-a – shola Mar 13 '13 at 15:14

2 Answers2

9

There are several ways to do this.

  1. 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
  2. Implement a BEFORE DELETE trigger on the slave to ignore the deletes.

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

I have not tried #2, but it should be possible.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • I had similar requirement.Now i am using sql_log_bin=0 in a stored procedure defined by root user to run a archival script.This helps me archive on master and not archive on the slave. – ns15 Apr 11 '18 at 05:43
  • I tried your 2nd approach, its not working. I write a trigger before delete, But data's still removed – Bala Murugan Jan 20 '21 at 10:46
5

You'll only be able to achieve this with a hack, and it will likely cause problems. MySQL replication isn't designed for this.

Imagine you insert a record in your master, it replicates to the slave. You then delete from the master, but it doesn't delete from the slave. If someone adds a record with the same unique key, there will be a conflict on the slave.

Some alternatives:

  1. If you are looking to make a backup, I would do this by another means. You could do a periodic backup with a cronjob that runs mysqldump, but this assumes you don't want to save EVERY record, only create periodic restore points.
  2. Triggers to update a second, mirror database. This can't cross servers though, you'd have to recreate each table with a different name. Also, the computational cost would be high and restoring from this backup would be difficult.
  3. Don't actually delete anything, simply create a Status field which is Active or Disabled, then hide Disabled from the users. This has issues as well, for example, ON DELETE CASCADE couldn't be used, it would have to be all manually done in code.

Perhaps if you provide the reason you want this mirror database without deletes, I could give you a more targeted solution.

tjbourke
  • 221
  • 1
  • 2
  • 8
  • I have number of busy databases, with 1.6 mil records / day / each, I'm trying to get all records stored in single database, then run clean up job on production to maintain small / fast database, and have slave / reporting server setup for historical data. I need deletes to run on master, to maintain small database. – Deano Mar 13 '13 at 15:23
  • 1
    @Deano, I would be interested in knowing which approach actually worked for you. Thanks in advance! – el.atomo Mar 04 '15 at 10:14
  • @el.atomo, I created a Perl script, which will runs every 10 min, and scan the master db for recent records, then connect to warehouse db, and write new records, Perl mysql module, supports transactions, therefore it will role back the transaction if it encounters and error. Maybe it's not the best approach, but it has been working well without issues for quite sometime now. – Deano Mar 04 '15 at 23:06