0

We are currently using MySQL. Ideally, we would want statement or row based replication to another completely different cluster that is read-only except for the replication stream coming in. The only statements we do not want replicated are ones of the format DELETE.......WHERE archive=true. In this way, we can keep our transactional database with 3 months of data for speed and our replica database can have 2.5 years of data.

I have not found a way to do this just yet so I would appreciate any option of doing this at this point. Naturally, there are transactional deletes and we DO want those replicated.

Is there a way to set this up?

I have been googling and chatgpting for info.

Oh, my only other option I thought about is to replicate first and then on the replica, add triggers to all tables to then copy the data to another table but in the trigger filter out any rows for when a delete happens so if delete happens, we trigger and check if archive=true|false. If false, we replicate and if true, we ignore and the data is now archived. This unfortunately results in quite a large dataset size in our replica but is one option I have in my head. I was hoping to filter before it hits the replica though.

open issue on trigger is if I actually receive the row data to the value.

1 Answer 1

1

Before that query SET VARIABLE @@log_bin=FALSE; then `TRUE afterward.

The query-rewrite feature may be able to insert those two statements whenever it sees the relevant test on archive.

4
  • very very interesting. Can this rewrite plugin be used with row based replication? Reading the docs, it implies only statement based replication :( and I think we are on row based replication right now (not sure why yet). Commented Feb 2 at 7:26
  • Several versions ago, MySQL moved to the "better" ROW based approach.
    – Rick James
    Commented Feb 3 at 5:56
  • hmm, what is difference between SET VARIABLE @@log_bin=FALSE; and SET SQL_LOG_BIN=0; from this post stackoverflow.com/questions/15388907/… Commented Feb 5 at 6:36
  • @DeanHiller - Experiment with each and report back. (I don't remember, and I don't have a setup to test.)
    – Rick James
    Commented Feb 5 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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