I have a mysql master and 3 slave. In the master im running Archive commands to delete old records everyday.

But I don't want to Purge the records on the Slave 3. Im using this as a Archive DB.

So, How can I achieve this?

Im using a seperate user for archive.

  1. Is there any options to prevent this operation from the Particular slave?

  2. Or any way to achieve this?

1 Answer 1


I think you'll be able to achieve this goal but there isn't really anything native that will give you this functionality out of the box.

You won't be able to use replication filters because they're not granular enough to filter by DML type. So that's off the list early.

You might approach it (optionally using partitions based on your archive routine) dropping the data (partition) outside the bounds of binary logging (SET SESSION LOG_BIN=0) and therefore not sending the DELETE/DROP messages through the replication topology. This could be managed through MySQL's built-in EVENTS scheduler or could be something that runs outside of MySQL in a script controlled through crond.

A quick scan of the documentation of Percona Toolkit's pt-archiver suggests that you could run archival jobs that connect to >1 mysqld instance and can do so in a manner that the session evades the binary logging (see DSN option 'b').

A word of warning, topologies of this design can be pretty delicate especially if you forget that the downstream replicas have more data in certain tables. I have completely forgotten about this when running tools such as pt-online-schema-change where the table is rebuilt on the MASTER and the REPLICA's version of the table ends up the same as the MASTER's.

Good luck!

Your Answer

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

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