MySQL replication without deletions

Question

I was looking for a way to prevent the execution of MySQL removal instructions from the slave slave, I am working on a data warehouse project, and I would like to delete data from the production server after the data has been replicated to the slave.

What is the best way to do this?

thanks

Answer 1

There are several ways to do this.

• Run SET SQL_LOG_BIN=0; for the corresponding session on the host computer before performing your uninstall. This way it is not written to the binary log
• Deploy a BEFORE DELETE trigger on the slave to ignore deletes.

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

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

Answer 2

You can only achieve this with a hack, and this is likely to cause problems. MySQL replication is not designed for this.

Imagine that you insert a record into your master, it is copied to the subordinate. Then you delete from the master, but it does not delete from the slave. If someone adds an entry with the same unique key, there will be a conflict on the follower.

Some alternatives:

• If you want to make a backup, I will do it in a different way. You can do periodic backups using cronjob, which launches mysqldump, but this assumes that you do not want to save EVERY record, only create periodic recovery points.
• Triggers to update the second mirror database. However, this cannot cross servers; you will have to recreate each table with a different name. In addition, the computational cost will be high, and restoring from this backup will be difficult.
• Actually, do not delete anything, just create a “Status” field that is active or disabled, then hide “Disconnected” from users. This also has problems, for example, ON DELETE CASCADE cannot be used, all this must be done manually in the code.

Perhaps if you indicate the reason why you want this mirror database without deleting, I can give you a more focused solution.