MySQL replication without delete statments

Question

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

Answer 1

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.

Answer 2

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.